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

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

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; 

函数

函数和存储过程存在以下区别但本质上没什么区别。

语法形式如下
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操作时)，数据库触发器会隐式执行。

语法形式如下 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的实际工作量。