PL SQL异常处理

PL/SQL程序执行过程中出现的错误，称之为异常。


PL/SQL程序的错误可分为两类：
    一类是PL/SQL语法错误，它由PL/SQL编译器发现并给出错误信息，
    另一类是运行时错误，由PL/SQL运行时引擎发现并给出错误信息。


异常分类：
    PL/SQL语言的异常分为两大类
一类是ORACLE系统异常
ORACLE系统异常又分为两种，一种是预定义异常，另一种是非预定义异常。
    另一类是自定义异常


    （1)预定义异常
预定义异常是ORACLE系统异常中的一种，用于处理常见的ORACLE错误。ORACLE预定义异常的特点是，
ORACLE系统定义了它们的错误编号与异常名字。当运行PL/SQL代码产生预定义错误时，与错误对应的
预定义异常被自动抛出，通过预定义异常名字捕获该异常。
预定义异常与ORACLE错误编号之间的对应关系如下所示：

预定义异常   Oracle错误   错误代码  描述
  
ACCESS_INTO_NULL   ORA-06530   -6530  试图给一个没有初始化（为NULL）的对象赋值
 
CASE_NOT_FOUND     ORA-06592    -6592  在CASE语句中没有WHEN子句被选择，并且没
  有ELSE子句


COLLECTION_IS_NULL ORA-06531   -6531  试图将EXISTS以外的集合（collection）方法
 应用到一个没有被初始化（为NULL）的嵌套表
（nested table）和变长数组（varray）中，
 或者试图给没有初始化的嵌套表
（nested table）或变长数组（）varray中的
 元素赋值


CURSOR_ALREADY_OPEN ORA-06511   -6511  试图打开一个已经打开的游标，一个游标在它
 重新打开前必须被关闭。一个游标FOR循环会
 自动地打开所涉及的游标，所以在游标循环里
 不能打开游标


DUL_VAL_ON_INDEX   ORA-00001   -1   试图在一个有惟一性约束的数据库列中存储重复的值


  INVALID_CURSOR   ORA-01001   -1001  试图执行一个无效的游标操作  


INVALID_NUMBER   ORA-01722   -1722  试图将一个看起来不像是一个有效的数字的字
 符串转换成数字失败时，而在过程性语句中，
 将会引发VALUE_ERROR错误，代替
 INVALID_NUMBER错误


LOGIN_DENIED   ORA-01017   -1017  用一个无效的用户名或口令去登陆Oracle


NO_DATA_FOUND   ORA-01403   100  一个SELECT INTO语句没有返回数据或者程序
 引用一个嵌套表中被删除的元素或索引表中一
 个没有被初始化的元素  

NOT_LOGGED_ON   ORA-01012   -1012  发布一个数据库呼叫指令而没有连接到数据库 


PROGRAM_ERROR   ORA-06501   -6501  Oracle内在错误，通常是由PL/SQL本身造成
 的，这种情况下应该通知Oracle公司技术部门 


ROWTYPE_MISMATCH   ORA-06504   -6504  宿主游标变量和PL/SQL游标变量相关的作业有
 着不兼容的类型 


SELF_IF_NULL   ORA-30625   -30625  程序试图调用一个空的实例的成员方法


STORAGE_ERROR ORA-06500 -6500  PL/SQL运行时内存不够或者内存是有问题的 

SUBSCRIPT_BEYOND_COUNT  ORA-06533  -6533  引用一个嵌套表（nested table）或变长数
 组（varray）的元素时索引值大于集合中元素
 的数量 


SUBSCRIPT_OUTSIDE_LIMIT  ORA-06532 -6532  引用一个嵌套表（nested table）或变长数
 组（varray）的元素时使用的索引值（例如
 －1）超出了规定的范围 


SYS_INVALID_ROWID   ORA-01410   -1410  试图将一个看起来不像是一个有效的ROWID的字
 符串转换成一个ROWID时  


TIMEOUT_ON_RESOURCE  ORA-00051   -51  Oracle在等待资源时发生超时现象  


TOO_MANY_ROWS ORA-01422 -1422  SELECT INTO语句返回了多行数据


VALUE_ERROR   ORA-06502   -6502  一个算法、转换、截断或者大小约束错误发生，
 如果在SQL语句中发生错误则会引发
INVALID_ERROR错误，替代了VALUE_ERROR错误 


  ZERO_DIVIDE   ORA-01476   -1476  发生被零除 


    （2）非预定义异常
也是ORACLE系统异常中的一种，用于处理预定义以外的ORACLE系统错误。ORACLE非预定义异常的特点
是，ORACLE系统定义了它们的错误编号，但没有定义异常名字。这些ORACLE系统错误没有预定义异
常（异常名字）与其关联，需要在PL/SQL块的声明部分定义一个异常名字，然后通过伪过程
PRAGMA EXCEPTION_INIT 将该异常名字与一个ORACLE 错误编号相关联。这样，当运行PL/SQL代码
产生非预定义错误时，与错误代码对应的非预定义异常被自动抛出，通过定义的异常名字捕获该异常，
并对错误进行处理。


    （3）自定义异常
自定义异常用于处理用户定义错误，即处理与ORACLE系统错误无关的其错误。自定义异常是指有些操
作不会产生ORACLE系统错误，但是程序员从业务规则角度考虑，认为是一种错误。例如，执行UPDATE
操作没有更新任何记录行时，不会引发ORACLE系统错误，也不会发生异常。但是，有时需要开发人员
为此操作产生一个异常，以便进行处理，这就是用户定义异常。


异常处理
PL/SQL程序中，异常处理按以下3个步骤进行：
定义异常
抛出异常
捕获及处理异常


（1）定义异常
如前所述，ORACLE中的异常分为预定义异常、非预定义异常和自定义异常，其中预定义异常
由系统隐式定义，而后两种异常则需要用户定义。定义异常的方法是在PL/SQL程序的声明
部分定义一个EXCEPTION类型的变量，其格式为：


exception_name EXCEPTION； 


其中exception_name 为异常名，如果 是非预定义异常，还需要使用伪过程，在编译阶段将
异常名与一个ORACLE错误代码相关联，其语句格式为：

PRAGMA EXCEPTION_INIT(exception_name,error_number);


其中，exception_name为异常名，error_number为ORACLE系统内部错误号，用一个负位数
表示，-20999~-20000为用户定义错误的保留号。


（2）抛出异常
由于系统不能自动识别用户定义错误，因此当产生自定义错误时，需要程序员使用特定的PL/SQL
代码抛出相应的自定义异常。自定义异常的抛出语句格式为：


RAISE exception_name;

（3）捕获及处理异常
异常处理的基本格式为：

EXCEPTION
WHEN e_name1 [OR e_name2...] THEN
sequence of statements1;
WHEN e_name3 [OR e_name4...] THEN
sequence of statements2;
......
[WHEN OTHERS THEN
sequence of statementsn;]
END;


异常处理实例
处理预定义异常


   例1：
DECLARE
v_dividend NUMBER:=50;
v_divisor NUMBER:=0;
v_quotient NUMBER;
BEGIN
v_quotient:=v_dividend/v_divisor;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('divisor is 0');
END;
/


   例2：输入员工编号，如果员工不存在，则提示错误
SQL> SET SERVEROUTPUT ON


DECLARE
v_id emp.empno%TYPE;
v_name emp.ename%TYPE;
BEGIN
v_id:=&inputid;
select ename INTO v_name FROM emp WHERE empno=v_id;
DBMS_OUTPUT.PUT_LINE('EMPNO = '||v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');
END;
/


   例3:使用两种异常处理，避免找不到记录和找到多条记录而赋给一个变量的错误


DECLARE
v_job emp.job%TYPE;
v_name emp.ename%TYPE;
BEGIN
v_job:='&inputjob';
select ename INTO v_name FROM emp WHERE job=v_job;
DBMS_OUTPUT.PUT_LINE('EMPNO = '||v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('FOUND RECORD TOO MANY!');
END;
/


非预定义异常
也是ORACLE系统异常中的一种，用于处理预定义以外的ORACLE系统错误。ORACLE非预定义异常的特点
是，ORACLE系统定义了它们的错误编号，但没有定义异常名字。这些ORACLE系统错误没有预定义异
常（异常名字）与其关联，需要在PL/SQL块的声明部分定义一个异常名字，然后通过伪过程
PRAGMA EXCEPTION_INIT 将该异常名字与一个ORACLE 错误编号相关联。这样，当运行PL/SQL代码
产生非预定义错误时，与错误代码对应的非预定义异常被自动抛出，通过定义的异常名字捕获该异常，
并对错误进行处理。
非预定义异常需要用户定义异常名，而且还要使用伪过程，在编译阶段将异常名与一个ORACLE
错误代码相关联。


   例1：在dept删除10号部门，试验引用完整性约束异常
SQL>    DECLARE
  2                     e_deptid EXCEPTION;
  3                     PRAGMA EXCEPTION_INIT(e_deptid,-2292);
  4             BEGIN
  5                     DELETE FROM DEPT WHERE DEPTNO=&e_deptid ;
  6             EXCEPTION
  7                     WHEN e_deptid THEN
  8                             DBMS_OUTPUT.PUT_LINE('at sub table have record!');
  9             END;
 10             /
输入 e_deptid 的值:  20
原值    5:                      DELETE FROM DEPT WHERE DEPTNO=&e_deptid ;
新值    5:                      DELETE FROM DEPT WHERE DEPTNO=20 ;
at sub table have records !


PL/SQL 过程已成功完成。


SQL>    DECLARE
  2                     e_deptid EXCEPTION;
  3                     PRAGMA EXCEPTION_INIT(e_deptid,-2292);
  4             BEGIN
  5                     DELETE FROM DEPT WHERE DEPTNO=&e_deptid ;
  6             EXCEPTION
  7                     WHEN e_deptid THEN
  8                             DBMS_OUTPUT.PUT_LINE('at sub table have record!');
  9             END;
 10             /
输入 e_deptid 的值:  40
原值    5:                      DELETE FROM DEPT WHERE DEPTNO=&e_deptid ;
新值    5:                      DELETE FROM DEPT WHERE DEPTNO=40 ;


PL/SQL 过程已成功完成。


   例2：在emp表中插入记录，部门号为80，试验参照完整性约束异常


DECLARE
e_deptid EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptid,-2291);
BEGIN
INSERT INTO EMP (EMPNO,DEPTNO) VALUES (8001,80); 
EXCEPTION
WHEN e_deptid THEN
DBMS_OUTPUT.PUT_LINE('deptno 80 at table dept no exist !');
END;
/


deptno 80 at table dept no exist !


PL/SQL 过程已成功完成。


   例3：在emp表中插入一条EMPNO存在的记录


DECLARE
e_deptid EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptid,-0001);
BEGIN
INSERT INTO EMP (EMPNO,DEPTNO) VALUES (7788,10); 
EXCEPTION
WHEN e_deptid THEN
DBMS_OUTPUT.PUT_LINE('record is exist !');
END;
/


SQL> select empno,deptno from emp where empno=7788;


     EMPNO     DEPTNO
---------- ----------
      7788         10


自定义异常处理
自定义异常不仅需要用户定义异常名字，还要程序员安排何时抛出异常及对异常的处理。


   例1：在emp中插入一条工资为负数的记录。


DECLARE
e_sal EXCEPTION;
v_sal emp.sal%TYPE;
BEGIN
v_sal:=&inputsal;
IF v_sal<0 THEN
RAISE e_sal;
END IF;
INSERT INTO emp(empno,ename,sal) values (8002,'TEST',v_sal);
EXCEPTION
WHEN e_sal THEN
DBMS_OUTPUT.PUT_LINE('sal not < 0 !');
END;
/


输入 inputsal 的值:  -1000
原值    5:                      v_sal:=&inputsal;
新值    5:                      v_sal:=-1000;
sal not < 0 !


PL/SQL 过程已成功完成。


   例2：带有WHEN OTHERS THEN的异常处理


DECLARE
e_sal EXCEPTION;
v_sal emp.sal%TYPE;
BEGIN
v_sal:=&inputsal;
INSERT INTO emp(empno,ename,sal) values (8002,'TEST',v_sal);
IF v_sal<0 THEN
RAISE e_sal;
END IF;
EXCEPTION
WHEN e_sal THEN
DBMS_OUTPUT.PUT_LINE('sal not < 0 !');
rollback;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other error !');
rollback;
END;
/


使用异常函数
在PL/SQL代码运行出现错误时，通过使用异常函数可以获得错误代码及相关的错误描述，
其中函数SQLCODE用于获得ORACLE错误代码，而SQLERRM则用于获得与之相应 的错误描述。


DECLARE
e_sal EXCEPTION;
v_sal emp.sal%TYPE;
v_empno emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
v_sal:=&inputsal;
v_empno:=&inputempno;
v_deptno:=&inputdeptno;
INSERT INTO emp(empno,deptno,sal) values (v_empno,v_deptno,v_sal);
IF v_sal<0 THEN
RAISE e_sal;
END IF;
EXCEPTION
WHEN e_sal THEN
DBMS_OUTPUT.PUT_LINE('sal not < 0 !');
rollback;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error code:'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('error desc:'||SQLERRM);
END;
/


第一种情况：
输入 inputsal 的值:     100
原值    7:              v_sal:=&inputsal;
新值    7:              v_sal:= 100;
输入 inputempno 的值:  100
原值    8:              v_empno:=&inputempno;
新值    8:              v_empno:=100;
输入 inputdeptno 的值:  10
原值    9:              v_deptno:=&inputdeptno;
新值    9:              v_deptno:=10;


PL/SQL 过程已成功完成。


第二种情况：
输入 inputsal 的值:     -1000
原值    7:              v_sal:=&inputsal;
新值    7:              v_sal:= -1000;
输入 inputempno 的值:  1000
原值    8:              v_empno:=&inputempno;
新值    8:              v_empno:=1000;
输入 inputdeptno 的值:  20
原值    9:              v_deptno:=&inputdeptno;
新值    9:              v_deptno:=20;
sal not < 0 !


PL/SQL 过程已成功完成。