PL/SQL编程学习总结

2022-02-03 17:22:19

最近学习了PL/SQL的相关知识，写篇文章记录一下，也当作这两天学习成果的复习回顾啦，如果有问题，可以一起交流学习，互相进步！！！这是第一篇博客，但绝对不会是最后一篇，立个flag以表决心哈哈哈！！！

PL/SQL编程学习总结


PL/SQL（Procedural Language SQL）过程化SQL语言是oracle数据对于sql的扩展，在普通sql语句的使用上增加了编程语言的特点，语法仅适用于oracle数据库。

PL/SQL的基本结构
PL/SQL编程学习总结

变量

变量可以分为：普通型变量，引用型变量，记录型变量以及常量，还有一种支持写入功能的变量：替代变量。

普通型变量

声明变量时可以直接赋初始值

DECLARE
         v_name varchar2(32):=’aaa’;
         v_age number(3):=10;
BEGIN
         v_name:='zhangsan';
         v_age:=25;
         dbms_output.put_line('姓名：'||v_name||'  年龄：'||v_age);
END;

也可以在begin种进行赋值

DECLARE
         v_name varchar2(32);
         v_age number(3);
BEGIN
         v_name:='zhangsan';
         v_age:=25;
         dbms_output.put_line('姓名：'||v_name||'  年龄：'||v_age);
END;

引用型变量

直接把变量类型定义为表中已有字段的类型

DECLARE
     ename emp.ename%type;
     sal emp.sal%type;
     hiredate emp.hiredate%type; 
 BEGIN
     select ename,sal,hiredate into ename,sal,hiredate from emp where empno=7369;
     dbms_output.put_line('姓名：'||ename||'  薪资：'||sal||'  入职日期：'||hiredate); 
 END;

记录型变量

PL/SQL提供了 %ROWTYPE操作符，返回一个记录类型，表示表中的一行记录
记录型变量相当于一条完整的表记录，通过“变量名.列名”获取其值
–记录型变量

DECLARE
    emp_record   emp%ROWTYPE;
BEGIN
	--给变量赋值
    SELECT *  INTO emp_record FROM emp WHERE empno = 7788;
    --打印输出
    DBMS_OUTPUT.PUT_LINE(emp_record.ename||'的工资是：'||emp_record.sal);
END;

常量

在变量名后面加上constant即可。

替代变量

"&n1(替代变量)"运行程序时，会提示用户输入值n1

DECLARE
    v_num1  NUMBER(2) := &n1;
    v_num2  NUMBER(2) := &n2;
BEGIN    
    DBMS_OUTPUT.PUT_LINE(v_num1);
    DBMS_OUTPUT.PUT_LINE(v_num2);
END;

选择循环

分支结构

单分支

需求：输入员工号，判断员工工资, 显示工资小于3000的员工姓名及工资

DECLARE
    v_ename emp.ename%type;
    v_sal emp.sal%type;
BEGIN    
    select ename,sal into v_ename,v_sal from emp where empno=&empno;
    if v_sal<3000 then
      dbms_output.put_line(v_ename||'的薪资是：'||v_sal);
    end if;
END;

双分支

需求：输入员工号，判断员工工资,将工资小于3000的员工工资涨200，并显示涨工资的员工姓名，其他员工显示员工姓名及工资

DECLARE
    v_ename emp.ename%type;
    v_sal emp.sal%type;
    v_empno emp.empno%type:=&empno;  
BEGIN    
    select ename,sal into v_ename,v_sal from emp where empno=v_empno;
    if v_sal<3000 then
       update emp set sal = sal + 200 where empno = v_empno;
       commit;
       dbms_output.put_line(v_ename||'涨工资了');
    else
       dbms_output.put_line(v_ename||'的工资是：'||v_sal);
    end if;
END;

多分支

需求：输入员工号，判断员工工资, 工资小于2000，显示低收入，工资小于6000，显示中等收入，其它显示高收入。

DECLARE
    v_ename emp.ename%type;
    v_sal emp.sal%type;
    v_empno emp.empno%type:=&empno;  
BEGIN    
    select ename,sal into v_ename,v_sal from emp where empno=v_empno;
    if v_sal<2800 then
        dbms_output.put_line(v_ename||'的工资是：'||v_sal||'属于低收入！！');
    elsif v_sal<6000 then 
        dbms_output.put_line(v_ename||'的工资是：'||v_sal||'属于中等收入！！');    
    else
        dbms_output.put_line(v_ename||'的工资是：'||v_sal||'属于高收入！！');    
    end if;
END;

CASE分支处理等值

需求：输入成级等级，判断属于哪个层次，并打印输出(录入的字符串需要加双引号)

DECLARE 
       v_grade char(1) := &no;
BEGIN
       CASE v_grade
            WHEN  'A'  THEN
                  DBMS_OUTPUT.PUT_LINE('优秀');
            WHEN  'B'  THEN
                  DBMS_OUTPUT.PUT_LINE('中等');     
            WHEN  'C'  THEN
                  DBMS_OUTPUT.PUT_LINE('一般'); 
            ELSE
                  DBMS_OUTPUT.PUT_LINE('输入有误');    
       END CASE;              
END;

CASE分支处理非等值

需求：输入员工号，获取员工工资，判断工资，如果工资小于1500，补助加100，如果工资小于2500，补助加80，如果工资小于5000，补助加50.

DECLARE       
       v_sal     emp.sal%TYPE;
       v_empno   emp.empno%TYPE := &no;
BEGIN
       SELECT sal
       INTO   v_sal
       FROM   emp
       WHERE  empno = v_empno;
       CASE 
              WHEN v_sal<1500 THEN
                   UPDATE emp set comm = nvl(comm,0)+100 WHERE empno = v_empno;
              WHEN v_sal<2500 THEN
                   UPDATE emp set comm = nvl(comm,0)+80 WHERE empno = v_empno;     
              WHEN v_sal<5000 THEN
                   UPDATE emp set comm = nvl(comm,0)+50 WHERE empno = v_empno;                   
       END CASE;
       COMMIT;
END;

循环结构

需求：打印1-10的数字

LOOP循环

DECLARE
       V_COUNT NUMBER(2) := 1;
BEGIN
     LOOP
         DBMS_OUTPUT.put_line(V_COUNT);
         EXIT WHEN V_COUNT=10;
         V_COUNT := V_COUNT+1;
     END LOOP;
END;

While循环

DECLARE
       V_COUNT NUMBER(2) := 1;
BEGIN
     WHILE V_COUNT<=10 LOOP
           DBMS_OUTPUT.put_line(V_COUNT);
           V_COUNT := V_COUNT+1;
     END LOOP;
END;

For循环

DECLARE
       
BEGIN
     FOR i IN 1..10 LOOP
         DBMS_OUTPUT.put_line(i);
     END LOOP;
END;

对于for循环不需要提前定义变量 in后的1代表起始值 10代码终止值 每次递增1 不需要写变量的改变

For循环倒叙

DECLARE
       
BEGIN
     FOR i IN REVERSE 1..10 LOOP
         DBMS_OUTPUT.put_line(i);
     END LOOP;
END;

双重for循环

嵌套循环需要使用标号 <<label_name>>

DECLARE
    
BEGIN
    <<outter>>
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('外'||i);
        <<inter>>
        FOR j IN 1..2 LOOP        
          DBMS_OUTPUT.PUT_LINE('---内'||j);
          EXIT outter WHEN i=4;
        END LOOP inter;
     END LOOP outter;
END;

退出循环

EXIT :直接退出循环
EXIT WHEN :满足一定条件退出循环

游标

游标是用来在PL/SQL中操作查询的结果集
语法：cursor 游标名（参数） is 查询的结果集

使用步骤：

  1. 声明游标
  2. 打开游标 open 游标名
  3. 从游标中取数据 fetch 游标名 into 变量
    fetch一次就从游标中取一条记录
    游标名%found:从游标中找到了数据
    游标名%notfound：从游标中没有找到数据
  4. 关闭游标 close 游标名

注意：定义游标参数时不能设置长度

存储过程

存储过程（Stored Procedure）是，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字来执行它。

语法形式如下:
CREATE [OR REPLACE] PROCEDURE 存储过程名
[( param1 [mode1] type1, param2 [mode2] type2, …)]
IS | AS 变量声明

BEGIN 业务处理

END;

param1、param2：形参名称
mode1、mode2：参数模式，包括IN（默认）、OUT
type1、type2：参数数据类型

需求：根据员工编号输出员工的姓名、工资和入职时间

create procedure P_GETEMP(eno in emp.empno%type) 

is
  v_name emp.ename%type;
  v_sal emp.sal%type;
  v_hiredate emp.hiredate%type;
begin
     select ename,sal,hiredate into v_name,v_sal,v_hiredate 
     from emp where empno=eno;
     dbms_output.put_line(v_name);  
     dbms_output.put_line(v_sal); 
     dbms_output.put_line(v_hiredate); 
end ;

存储过程调用
PL/SQL调用存储过程,如果没有变量声明，DECLARE可以省略

DECLARE

BEGIN 
  P_GETEMP(7936);    
END;

函数

函数和存储过程存在以下区别但本质上没什么区别。
PL/SQL编程学习总结

语法形式如下
CREATE [OR REPLACE] FUNCTION 函数名
[( param1 [mode1] type1, param2 [mode2] type2, …)]
return type
IS | AS 变量声明
BEGIN 业务处理 END;

param1、param2：形参名称
mode1、mode2：参数模式，包括IN（默认）、OUT
type1、type2：参数数据类型

需求：查询指定员工的年薪，参数员工的编号，返回年薪。

create or replace function f_gettotalsal(vempno in emp.empno%type) 
return number
is
  vtotalsal emp.sal%type;
begin
    select sal*12+nvl(comm,0) into vtotalsal from emp where empno=vempno;
    return vtotalsal;
end;

函数调用

declare
vtotalsal number;
begin 
  vtotalsal := f_gettotalsal(7369);
  dbms_output.put_line(vtotalsal);    
end;

注意：自定义的函数和oracle提供的函数一样的使用方式

触发器

触发器是一个数据库对象，当特定的事件发生时隐式的执行，比如在一个表上发生插入、更新或删除的事件(进行DML操作时)，数据库触发器会隐式执行。
PL/SQL编程学习总结

语法形式如下
CREATE [OR REPLACE] Trigger 触发器名字
	timing
	    event1 [OR event2 OR event3]
	    ON table
   [for each row] [when 条件  [old.pid>7900]] old在plsql语句中前面要加上： 即:old  :new
	trigger_body

timing表示触发时间
event1、event2和event3表示触发事件
table表示针对的表，
for each row 每行数据就会触发一次该触发器
trigger_body表示触发器体

需求：不允许在周日修改emp表

create or replace trigger auth_secure 
before 
insert or update or DELETE
on emp
begin
  IF(to_char(sysdate,'DY')='星期日') THEN
    RAISE_APPLICATION_ERROR(-20600,'不允许在周日修改emp表');
  END IF;
END;

注意：
RAISE_APPLICATION_ERROR()是 oracle内置的一个过程 第一个参数是自定义的错误码

异常处理

程序运行过程中发生的意外

ZERO_DIVIDE --ORA-01476-- 程序尝试除以 0
VALUE_ERROR --ORA-06502-- 类型转换错误
NO_DATA_FOUND --ORA-01403-- 未找到行
TOO_MANY_ROWS --ORA-01422–SELECT INTO 语句返回多行数据
OTHERS 其他的异常

DECLARE
   --变量的声明
BEGIN  
    --业务处理
EXCEPTION 
          WHEN 异常 then
          ...
          WHEN 异常2 then
          ...
          WHEN others then 
          ....
END;

需求：编写一个函数 求2个数的商

create or replace function f_mod(v1 in number,v2 in number) return number
is
 v_ret number;
begin
     v_ret := v1 / v2;
     return v_ret;
exception
      when ZERO_DIVIDE then
      RAISE_APPLICATION_ERROR(-20600,'除数不能为0');   
end;

select f_mod(5,0) from dual

SQL优化

不要让Oracle做的太多

1.避免复杂的多表关联
2.避免使用“ * ”
3.避免使用耗费资源的操作
4.用exists替换distinct
5.用UNION—ALL替换UNION

给优化器更明确的命令
1.自动选择索引
2.避免在索引列上使用函数或者列
3.避免在索引列上使用not，前置通配符，is null等
4.避免出现索引列自动转换
因为上面的操作，都会使索引失去作用，然后对表进行逐一遍历

减少访问次数
当执行每条SQL语句时, ORACLE都会在内部执行了许多工作,例如:解析SQL语句、估算索引的利用率、绑定变量、读数据块等等。由此可见,减少访问数据库的次数,就能减少ORACLE的实际工作量。

上一篇：PL/SQL 设置


下一篇：Oracle讲课笔记第一课