PL/SQL异常处理

异常处理
=========
为了提高应用程序的健壮性,开发人员必须考虑程序可能出现的各种错误,并进行相应的处理.
Oracle中异常分为预定义例外,非预定义例外和自定义例外三种.

## 处理预定义异常
预定义异常是指由PL/SQL所提供的系统异常.当PL/SQL应用程序违反了Oralce规则或系统限制时,则会隐含的触发一个内部异常.

# PL/SQL为开发人员提供了二十多个预定义异常:

1.ACCESS_INTO_NULL
该异常对应于ORA-06530错误.当开发对象类型应用时,如果没有初始化对象,直接为对象属性赋值,该异常触发.

DECLARE
emp emp_type;
BEGIN
emp.name:='SCOTT';
EXCEPTION
WHEN ACCESS_INTO_NULL THEN
DBMS_OUTPUT.PUT_LINE('首先初始化对象emp');
END;
/

2.CASE_NOT_FOUND
对应于ORA-06592错误.在CASE语句时,如果WHEN子句没有包含必须的条件分支,并且没有仓含ELSE子句,被触发.

DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=&&no;
CASE
WHEN v_sal?1000 THEN
UPDATE emp SET sal=sal+100 WHERE empno=&no;
WHEN v_sal?2000 THEN
UPDATE emp SET sal=sal+150 WHERE empno=&no;
WHEN v_sal?3000 THEN
UPDATE emp SET sal=sal+200 WHERE empno=&no;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('在CASE语句中没有与'||v_sal||'相关的条件.');
END;
/

3.COLLECTION_IS_NULL
对应于ORA-06531错误.在给集合元素(嵌套表或VARRAY类型)赋值前,必须首先寝化集合元素.否则触发该异常.
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(2) FROM emp
WHERE empno=&no;
DBMS_OUTPUT.PUT_LINE('雇员名: '||ename_table(2));
EXCEPTION
WHEN COLLECTION_IS_NULL THEN
DBMS_OUTPUT.PUT_LINE('必须初始化集合元素.');
END;
/

4.CURSOR_ALREADY_OPEN
对应于ORA-06511错误.当重新打开已经打开的游标时,会隐含地触发该异常.
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
OPEN emp_cursor;
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END LOOP;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已经打开.');
END;
/

5.DUP_VAL_ON_INDEX
对应于ORA-00001错误,当在惟一索引所对应的列上键入重复值时触发.
BEGIN
UPDATE dept SET deptno=&new_no WHERE deptno=&old_no;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('在deptno列上不能出现重复值.');
END;
/

6.INVALID_CURSOR
对应于ORA-01001错误.当试图在不合法的游标上执行操作时触发.如从未打开的游标取数据,关闭未打开的游标等.
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
FETCH emp_cursor INTO emp_record;
CLOSE emp_cursor;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('请检查游标是否已经打开.');
END;
/

7.INVALID_NUMBER
对应于ORA-01722错误.当内嵌SQL语句不能有效地将字符转变成数字时触发.如数值100被写成"1oo".
BEGIN
UPDATE emp SET sal=sal+'100';
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('输入的数字不正确.');
END;
/

8.NO_DATA_FOUND
对应于ORA-01403错误.当执行SELECT INOT未返回行,或引用了索引表未初始化元素时触发.
DECLARE
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE lower(ename)=lower('&name');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('不存在该雇员.');
END;
/


9.TOO_MANY_ROWS
对应于ORA-01422错误,当执行SELECT INTO语句时,如果返回超过一行触发该异常.
DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE sal=&sal;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回多行.');
END;
/

10.ZERO_DIVIDE
对应于ORA-01476错误.当运行PL/SQL块时,如果使用数据值除0触发该异常.
DECLARE
num1 INT:=100;
num2 INT:=0;
num3 NUMBER(6,2);
BEGIN
num2:=num1/num2;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('分母不能为0.');
END;
/

11.SUBSCRIPT_BEYOND_COUNT
对应于ORA-06533错误.当使用嵌套表或VARRAY元素时,如果下标越界触发该异常.
DECLARE
TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(10);
emp_array emp_array_type;
BEGIN
emp_array:=emp_array_type('SCOTT','MARY');
DBMS_OUTPUT.PUT_LINE(emp_array(3));
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
DBMS_OUTPUT.PUT_LINE('下标越界.');
END;
/

12.SUBSCRIPT_OUTSIDE_LIMIT
对应于ORA-06532错误.当使用嵌套表或VARRAY元素时,如果下标为负值触发该异常.
DECLARE
TYPE emp_array_type IS varray(20) OF VARCHAR2(10);
emp_array emp_array_type;
BEGIN
emp_array:=emp_array_type('SCOTT','MARY');
DBMS_OUTPUT.PUT_LINE(emp_array(-1));
EXCEPTION
WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
DBMS_OUTPUT.PUT_LINE('下标不能是负数.');
END;
/

13. VALUE_ERROR
对应于ORA-06502错误.当在PL/SQL块中执行赋值操作时,如果变量长度不足则触发该异常.
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=&no;
DBMS_OUTPUT.PUT_LINE(v_ename);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('变量长度不够.');
END;
/


# 其它预定义异常.

1.LONIN_DENIED
对应于ORA-01017错误.当PL/SQL应用程序要连接Oracle数据库时,如果密码错误则触发该异常.

2.NOT_LOGGED_ON
对应于ORA-01012错误.如果程序没连接Oracle数据库,那么执行PL/SQL访问数据库时触发该异常.

3.PROGRAM_ERROR
对应于ORA-06501.如果出现该错误,则表示PL/SQL内部问题,用户可能需要重新安装数据字典和PL/SQL系统包.

4.ROWTYPE MISMATCH
对应于ORA-06504错误.赋值时,宿主游标变量和PL/SQL游标变量的返回类型不兼容时触发该异常.

5.SELF_IF_NULL
对应于ORA-30625.当使用对象类型时,如果在NULL实例上调用成员方法则触发该异常.

6.STORAGE_ERROR
对应于ORA-06500错误.PL/SQL块运行时,如果走出内在空间或内在被损坏则触发该异常.

7.SYS_INVALID_ROWID
对应于ORA-01410错误.当将字符串转变为ROWID时,如果使用了无效的字符串则触发该异常.

8.TIMEOUT_ON_RESOURCE
对应于ORA-00051错误.Oracle在等待资源超出现超时错误时触发该异常.


## 非预定义异常
使用预定义异常,只能处理21个Oracle错误.而当使用PL/SQL开发应用程序时,可能还遇到其他的上结错误.
使用非预定义异常的步骤如下:
    定义异常 -> 关联异常和错误 -> 引用例外

当定义Oracle错误和例外之间的关联关系时,要使用伪过程EXCEPTION_INTI.下面以处理ORA-02291错误为例说明:

DECLARE
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
UPDATE emp SET deptno=&dno WHERE empno=&eno;
EXCEPTION
WHEN e_integrity THEN
DBMS_OUTPUT.PUT_LINE('该部门不存在.');
END;
/




# 处理自定义异常
预定义异常和非预定义异常都与Oracle错误有关,并且出现Oracle错误时会自动触发.
而自定义异常与Oracle错误没有任何关联,它是开发人员为特定情况所定义的异常.
自定义异常必须显式触发.使用步骤如下:
    定义异常 -> 显式触发异常 -> 引用异常
首先要在定义部分(DECLARE)定义异常,然后在执行部分(BEGIN)触发异常(RAISE语句),
最后在异常处理部分(EXCEPTION)捕捉处理.如下:
DECLARE
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
e_no_employee EXCEPTION;
BEGIN
UPDATE emp SET deptno=&dno WHERE empno=&eno;
IF SQL%NOTFOUND THEN
RAISE e_no_employee;
END IF;
EXCEPTION
WHEN e_integrity THEN
DBMS_OUTPUT.PUT_LINE('该部门不存在.');
WHEN e_no_employee THEN
DBMS_OUTPUT.PUT_LINE('该雇员不存在.');
END;
/


# 使用异常函数
在PL/SQL块中出现Oracle错误时,通过使用例外函数可以取得错误号以及相关的错误消息,函数SQLCODE用于取得Oracle错误号.
SQLERRM则用于取得与之相关的错误消息.另外,在存储过程,函数和包中使用RAISE_APPLICATION_ERROR可以自定义错误号和消息.

DECLARE
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE sal=&&v_sal;
DBMS_OUTPUT.PUT_LINE('雇员名:'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('不存在工资为'||&v_sal||'雇员');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误号'||SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

# RAISE_APPLICATION_ERROR
该过程用于自定义错误消息.只能在数据库端的子程序(过程,函数,包,触发器)中使用,不能在匿名块和客户端程序是使用.
语法如下:
    raise_application_error(error_number,message[,{TRUE | FALSE}]);
error_number : 错误号,范围是: -20000 ~ -20999之间的负整数;
message :      错误消息,长度不能超过2048字节;
第三个可靠选参数,如果TRUE,该错误会被放在先前错误堆栈中;如果FALSE(默认),则替换先前所有错误.

例:
CREATE OR REPLACE PROCEDURE raise_comm(eno NUMBER,commission NUMBER)
IS
v_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE emp=eno;
IF v_comm IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'该员工无补助.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该雇员不存在.');
END;
/

## PL/SQL编译警告
在Oracle 10g之前,编写PL/SQL子程序时,只要子程序符合SQL和PL/SQL的语法及主义规则,Oracle就会成功地编译子程序.
如下 (DEAD CODE):
CREATE OR REPLACE PROCEDURE dead_code AS
x number := 10;
BEGIN
IF x=10 THEN
x=20;
ELSE
x:=100; --死代码
END IF;
END dead_code;
/

如例,ELSE子句永远不会执行,应该避免出现类似的死代码.Oracle 10g开始,
在编写PL/SQL子程序之前开发人员可以激活警告检查.

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

2.控制PL/SQL警告消息
为了使得数据库可以在编译PL/SQL子程序时发出警告消息,需要设置寝化参数PLSQL_WARNINGS.
不仅可以在系统级或会话级设置,也可以在ALTER PROCEDURE命令是进行设置.
既可以激活或禁止所有警告类型,也可以激活或禁止特定消息号.如:
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> ALTER PROCEDURE hello COMPILE;
2> PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE',
2> 'DISABLE:PERFORMANCE','ERROR:06002';

激活警告检查后,编译子程序时用SHOW ERRORS命令显示警告错误

 

本文转自：http://hi.baidu.com/liumangxiaowangzi/blog/item/22ae4a067612277c03088151.html/cmtid/fcfef509fc83538bd0581b69