PL/SQL-->异常处理(Exception)

PL/SQL-->异常处理(Exception)
      Exception是一种PL/SQL标识符，当运行的PL/SQL块出现错误或警告，则会触发异常处理。为了提高程序的健壮性，可以在PL/SQL块中引入异常处理部分，进行捕捉异常，并根据异常出现的情况进行相应的处理。

一、异常的类型  

      ORACLE异常分为两种类型：系统异常、自定义异常。
      其中系统异常又分为：预定义异常和非预定义异常。

      1.预定义异常

              ORACLE定义了他们的错误编号和异常名字，预定义异常用来处理Oracle常见的错误

              NO_DATA_FOUND                  SELECT ... INTO ... 时，没有找到数据
              DUL_VAL_ON_INDEX            试图在一个有惟一性约束的列上存储重复值
              CURSOR_ALREADY_OPEN      试图打开一个已经打开的游标
              TOO_MANY_ROWS                  SELECT ... INTO ... 时，查询的结果是多行
              ZERO_DIVIDE                      零被整除

      2.非预定义异常

              ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。

      3.自定义异常
              程序员从我们业务角度出发，制定的一些规则和限制。

二、异常处理

      PL/SQL中，异常处理按3个步骤进行：
              定义异常
              抛出异常
              捕获及处理异常
           
      a.定义异常
              exception_name EXCEPTION;

      b.抛出异常
              RAISE exception_name

      c.捕获及处理异常

              EXCEPTION
                      WHEN e_name1 [OR e_name2 ... ] THEN
                              statements;

                      WHEN e_name3 [OR e_name4 ... ] THEN
                              statements;
                              ......

                      WHEN OTHERS THEN
                              statements;
              END;
              /

三、异常处理示例     

      1.预定义异常的例子

              --演示一个整除的异常

                      DECLARE
                              v_n1 NUMBER:=50;
                              v_n2 NUMBER:=0;
                              v_n3 NUMBER;
                      BEGIN
                              v_n3:=v_n1/v_n2;
                              DBMS_OUTPUT.PUT_LINE('v_n3='||v_n3);
                      EXCEPTION
                              WHEN ZERO_DIVIDE  THEN
                                      DBMS_OUTPUT.PUT_LINE('v_n2 don't is 0');             
                      END;
                      /

                 
              --演示VALUE_ERROR(错误号ORA-06502)

                      scott@ORCL> declare
                          2  v_ename varchar2(3);
                          3  begin
                          4          select ename into v_ename from emp where empno=&eno;
                          5          dbms_output.put_line(v_ename);
                          6  exception
                          7          when value_error then
                          8          dbms_output.put_line('variable datatype length is small');
                          9  end;
                        10  /

                      Enter value for eno: 7788
                      old    4:        select ename into v_ename from emp where empno=&eno;
                      new    4:        select ename into v_ename from emp where empno=7788;
                      variable datatype length is small

                      PL/SQL procedure successfully completed.             

                 
              --演示TOO_MANY_ROWS(对应Oracle错误号ORA-01422)

                      scott@ORCL> declare
                          2  v_ename emp.ename%type;
                          3  begin
                          4          select ename into v_ename from emp where deptno=&dno;
                          5  exception
                          6          when too_many_rows then
                          7          dbms_output.put_line('Too many rows are returned');
                          8  end;
                          9  /

                      Enter value for dno: 10
                      old    4:        select ename into v_ename from emp where deptno=&dno;
                      new    4:        select ename into v_ename from emp where deptno=10;

                      Too many rows are returned

                      PL/SQL procedure successfully completed.                     

      2.非预定义异常

              非预定义异常使用的基本过程

                      a.定义一个异常名
                      b.将异常名与异常编号相关联
                      c.在异常处理部分捕捉并处理异常

              DECLARE
                      e_deptid EXCEPTION;
                      PRAGMA EXCEPTION_INIT(e_deptid,-2292);
              BEGIN
                      DELETE FROM DEPT WHERE DEPTNO=10;
              EXCEPTION
                      WHEN e_deptid THEN
                              DBMS_OUTPUT.PUT_LINE('at sub table have record!');
              END;
              /
           

              DECLARE
                      e_emp EXCEPTION;
                      PRAGMA EXCEPTION_INIT(e_emp,-2291);
              BEGIN
                      INSERT INTO EMP(empno,deptno) VALUES (7935,60);
              EXCEPTION
                      WHEN e_emp THEN
                              DBMS_OUTPUT.PUT_LINE('60 at table dept not exist');
              END;
              /   
 

      3.自定义异常

              自定义异常与Oracle错误没有任何关系，有开发人员为特定情况所定义的例外

              下面的例子中当输入empno为1111时，尽管该雇员编号不存在，但PL/SQL代码并未给出适当的提示

                      scott@ORCL> declare
                          2  e_integrity exception;
                          3  pragma exception_init(e_integrity,-2291);
                          4  begin
                          5          update emp set deptno=&dno where empno=&eno;
                          6  exception
                          7          when e_integrity then
                          8          dbms_output.put_line('The dept is not exists');
                          9  end;
                        10  /

                      Enter value for dno: 20
                      Enter value for eno: 1111
                      old    5:        update emp set deptno=&dno where empno=&eno;
                      new    5:        update emp set deptno=20 where empno=1111;

                      PL/SQL procedure successfully completed.

              针对上述情况，可以使用自定义异常处理。

              对于自定义的异常处理需要显式的触发，其步骤如下

                      a.定义异常(在declare部分进行定义)
                      b.显式触发异常(在执行BEGIN部分触发异常，使用RAISE语句)
                      c.引用异常(在EXCEPTION部分捕捉并处理异常)

              --对上面的例子，使用自定义异常来处理，代码如下：

                      scott@ORCL> declare
                          2  e_integrity exception;
                          3  pragma exception_init(e_integrity,-2291);
                          4  e_no_employee exception;
                          5  begin
                          6          update emp set deptno=&dno where empno=&eno;
                          7          if sql%notfound then
                          8                  raise e_no_employee;
                          9          end if;
                        10  exception
                        11          when e_integrity then
                        12                  dbms_output.put_line('The dept is not exists');
                        13          when e_no_employee then
                        14                  dbms_output.put_line('The employee is not exists');
                        15  end;
                        16  /

                      Enter value for dno: 20
                      Enter value for eno: 1234
                      old    6:        update emp set deptno=&dno where empno=&eno;
                      new    6:        update emp set deptno=20 where empno=1234;

                      The employee is not exists

                      PL/SQL procedure successfully completed.             

                 
              --下面的例子中，如果插入的工资少于700，就抛出异常

                      DECLARE
                              v_sal emp.sal%TYPE;
                              v_id emp.empno%TYPE;
                              e_sal EXCEPTION;          --定义异常
                      BEGIN
                              v_id:=&inputid;
                              v_sal:=&inputsal;
                              INSERT INTO emp (empno,sal) VALUES (v_id,v_sal);
                              IF v_sal<700 THEN
                                      RAISE e_sal;          --捕捉异常
                              END IF;
                      EXCEPTION                                --处理异常
                              WHEN e_sal THEN
                                      ROLLBACK;
                                      DBMS_OUTPUT.PUT_LINE('Sal must be more than 700');  
                      END;
                      /

                 
四、使用异常函数处理异常                     

      1.SQLCODE与SQLERRM函数

              函数SQLCODE用于取得Oracle错误号
              函数SQLERRM用于取得与错误号对应的相关错误消息

                      scott@ORCL> get /u01/bk/scripts/sqlcode_errm.sql

                          1  undef v_sal
                          2  declare
                          3          v_ename emp.ename%type;
                          4          begin
                          5                  select ename into v_ename from emp
                          6                  where sal=&&v_sal;
                          7                  dbms_output.put_line('Employee Name:'||v_ename);
                          8  exception
                          9          when no_data_found then
                        10                  dbms_output.put_line('The employee is not exists for salary '||&v_sal);
                        11          when others then
                        12                  dbms_output.put_line('Error No:'||SQLCODE);
                        13                  dbms_output.put_line(SQLERRM);
                        14* end;

                      scott@ORCL> start /u01/bk/scripts/sqlcode_errm.sql

                      Enter value for v_sal: 1600
                      old    5:                where sal=&&v_sal;
                      new    5:                where sal=1600;
                      old    9:                dbms_output.put_line('The employee is not exists for salary '||&v_sal);
                      new    9:                dbms_output.put_line('The employee is not exists for salary '||1600);

                      Error No:-1422
                      ORA-01422: exact fetch returns more than requested number of rows

                      PL/SQL procedure successfully completed.     

      2.RAISE_APPLICATION_ERROR

              是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)。
              该函数用于在PL/SQL中定义错误消息，且只能在数据库端的子程序中使用(存储过程、函数、包、触发器)，不能在匿名块和客户端的子程序中使用

              使用方法

                      RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
                      该函数内的错误代码和内容，都是用用户自定义

                      error_number：用于定义错误号，且错误号从-20000 到-20999 之间，以避免与ORACLE 的任何错误代码发生冲突。
                      message：用于指定错误消息，且消息长度不能超过2k，超出部分将被截取
                      可选参数true,false:默认值为false，会替换先前的所有错误。当设置为true，则该错误会被放在先前错误堆栈中。

                      scott@ORCL> get /u01/bk/scripts/sp_raise_comm.sql

                          1  create or replace procedure raise_comm
                          2  (v_no emp.empno%type,v_comm out emp.comm%type)
                          3  as
                          4  begin
                          5            select comm into v_comm from emp where empno=v_no;
                          6            if v_comm is null then
                          7                    raise_application_error(-20001,'It is no comm for this employee');
                          8            end if;
                          9  exception
                        10            when no_data_found then
                        11            dbms_output.put_line('The employee is not exist');
                        12* end;

                      scott@ORCL> start /u01/bk/scripts/sp_raise_comm.sql

                      Procedure created.

                      scott@ORCL> variable g_sal number;
                      scott@ORCL> call raise_comm(7788,:g_sal);
                      call raise_comm(7788,:g_sal)
                                *
                      ERROR at line 1:
                      ORA-20001: It is no comm for this employee
                      ORA-06512: at "SCOTT.RAISE_COMM", line 7

                      scott@ORCL> call raise_comm(7499,:g_sal);
                      Call completed.

                      scott@ORCL> print g_sal

                                G_SAL
                      ----------
                                    300         

                           
五、PL/SQL编译警告

      1.PL/SQL警告的分类
              SEVERE: 用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题.
              PERFORMANCE: 用于检查可能引起性能问题,如在INSERT操作时为NUMBER列提供了VARCHAR2类型数据.
              INFORMATIONAL: 用于检查程序中的死代码.
              ALL: 用于检查所有警告.

      2.控制PL/SQL警告消息

              通过设置初始化参数PLSQL_WARNINGS来启用在编译PL/SQL子程序时发出警告消息，缺省为DISABLE:ALL

              警告消息设置的级别
                      系统级别
                      会话级别
                      ALTER PROCEDURE
                      既可以激活或禁止所有警告类型,也可以激活或禁止特定消息号

                      scott@ORCL> show parameter plsql%ings;

                      NAME                                                                TYPE              VALUE
                      ------------------------------------ ----------- ------------------------------
                      plsql_warnings                                            string          DISABLE:ALL             

                      scott@ORCL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'

                      scott@ORCL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'

                      scott@ORCL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';

                      scott@ORCL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';

      3.演示PL/SQL编译告警

              a.检测死代码

                      在下面的代码中，ELSE子句永远不会执行,应该避免出现类似的死代码.

                      从Oracle 10g开始,在编写PL/SQL子程序之前开发人员可以激活警告检查.

                              scott@ORCL> get /u01/bk/scripts/dead_code.sql

                                  1  CREATE OR REPLACE PROCEDURE dead_code AS
                                  2        x number := 10;
                                  3  BEGIN
                                  4        IF x>0 THEN
                                  5                        x:=1;
                                  6        ELSE                         
                                  7                        x:=2;      --死代码
                                  8        END IF;
                                  9* END dead_code;

                              scott@ORCL> start /u01/bk/scripts/dead_code.sql

                              scott@ORCL> alter session set plsql_warnings='enable:informational';

                              scott@ORCL> show parameter plsql%ings

                              NAME                                                                TYPE              VALUE
                              ------------------------------------ ----------- ------------------------------
                              plsql_warnings                                            string          ENABLE:INFORMATIONAL, DISABLE:
                                                                                                                                PERFORMANCE, DISABLE:SEVERE

                              scott@ORCL> alter procedure dead_code compile;
                              SP2-0805: Procedure altered with compilation warnings                   

                              scott@ORCL> show errors;
                              Errors for PROCEDURE DEAD_CODE:

                              LINE/COL ERROR
                              -------- -----------------------------------------------------------------
                              4/6          PLW-06002: Unreachable code
                              7/3          PLW-06002: Unreachable code

              b.检测引起性能问题的代码

                      scott@ORCL> create or replace procedure update_sal
                          2  (no number,salary varchar2)
                          3  as
                          4  begin
                          5          update emp set sal=salary where empno=no;
                          6  end;
                          7  /

                      Procedure created.

                      scott@ORCL> alter session set plsql_warnings='enable:performance';

                      scott@ORCL> alter procedure update_sal compile;

                      SP2-0805: Procedure altered with compilation warnings

                      scott@ORCL> show errors

                      Errors for PROCEDURE UPDATE_SAL:

                      LINE/COL ERROR
                      -------- -----------------------------------------------------------------
                      5/24        PLW-07202: bind type would result in conversion away from column           

转自：http://blog.csdn.net/robinson_0612/article/details/6080119