Oracle异常处理

为了提高应用程序的健壮性,开发人员必须考虑程序可能出现的各种错误,并进行相应的处理.

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-0 1722 错误.当内嵌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-0 1403 错误.当执行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-0 1422 错误,当执行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-0 1012 错误.如果程序没连接Oracle数据库,那么执行PL/SQL访问数据库时触发该异常.

3.PROGRAM_ERROR

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

4.ROWTYPE MISMATCH

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

5.SELF_IF_NULL

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

6.STORAGE_ERROR

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

7.SYS_INVALID_ROWID

对应于ORA-0 1410 错误.当将字符串转变为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:0 6002 ';

 

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