ITPub博客

首页 > 数据库 > Oracle > PL SQL异常处理.

PL SQL异常处理.

原创 Oracle 作者:wzhalal 时间:2013-11-26 17:37:08 0 删除 编辑
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_NULLORA-06531   -6531 试图将EXISTS以外的集合(collection)方法
 应用到一个没有被初始化(为NULL)的嵌套表
(nested table)和变长数组(varray)中,
 或者试图给没有初始化的嵌套表
(nested table)或变长数组()varray中的
 元素赋值


CURSOR_ALREADY_OPENORA-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 过程已成功完成。


























来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28894640/viewspace-1061167/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-06

  • 博文量
    60
  • 访问量
    249787