oracle异常处理总结

Oracle异常处理异常处理是考验一个应用程序健壮性的最好方式,开发人员必须考虑程序中可能出现的各种错误,并进行相应的处理。

Oracle中异常分为预定义异常,非预定义异常和自定义异常三种。

一 预定义异常

预定义异常是指由PL/SQL所提供的系统异常。当PL/SQL应用程序违反了Oracle规则或出现其它系统限制的情况时,将会隐含地触发一个内部异常。

以下是PL/SQL为我们预定义的异常:

1 CURSOR_ALREADY_OPEN该异常触发ORA-06511错误。

当程序中的一个游标已经执行了打开操作,如果开发人员试图再一次打开这个已经打开的游标时,将触发该异常。

示例:

DECLARE
  CURSOR test_cursor IS
    SELECT SYSDATE FROM dual;
BEGIN
  OPEN test_cursor;
  FOR test_cursor2 IN test_cursor LOOP
    dbms_output.put_line(test_cursor2.SYSDATE);
  END LOOP;
EXCEPTION
  WHEN cursor_already_open THEN
    dbms_output.put_line('游标已经打开,不能再次对游标执行打开操作。');
END;

2 INVALID_CURSOR该异常触发ORA-01001错误。

当试图对一个尚未打开的游标执行任何操作,如打开该非法的游标执行赋值操作,或者关闭未打开的游标时,将触发该异常。

示例:

DECLARE
  CURSOR test_cursor IS
    SELECT SYSDATE FROM dual;
  test_time DATE;
BEGIN
  --OPEN test_cursor;
  FETCH test_cursor
    INTO test_time;
  dbms_output.put_line('当前时间为: ' || test_time);
  CLOSE test_cursor;
EXCEPTION
  WHEN invalid_cursor THEN
    dbms_output.put_line('请检查游标是否已经打开.');
END;

3 NO_DATA_FOUND该异常触发ORA-01403错误。

当读取一个游标进行赋值操作(SELECT INTO操作)时,如果未返回任何行,将触发该异常。

示例:

DECLARE
  test_owner DATE;
BEGIN
  SELECT t.owner
    INTO test_owner
    FROM all_tables t
   WHERE t.table_name = 'test';
  dbms_output.put_line(test_owner);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No value.');
END;

4 TOO_MANY_ROWS该异常触发ORA-01422错误。

上面是没有返回记录,这里是返回多条记录的时候会触发该异常。

示例:

DECLARE
  test_records dba_tables%ROWTYPE;
BEGIN
  SELECT *
    INTO test_records
    FROM dba_tables
   WHERE owner = 'APPS'
     AND ROWNUM < 3;
  dbms_output.put_line(test_records.table_name);
EXCEPTION
  WHEN too_many_rows THEN
    dbms_output.put_line('Too many values.');
END;

5 VALUE_ERROR该异常触发ORA-06502错误。

当在PL/SQL程序中执行赋值操作时,如果右边值的长度大于左边变量的长度,将触发该异常。

示例:

DECLARE
  v_test VARCHAR2(1);
BEGIN
  SELECT 'WHAT' INTO v_test FROM dual;
  dbms_output.put_line(v_test);
EXCEPTION
  WHEN value_error THEN
    dbms_output.put_line('变量的长度不够.');
END;

6 DUP_VAL_ON_INDEX该异常触发ORA-00001错误。

若表的某一列声明为主键,或具有惟一性,如果对该列插入重复的值时将触发该异常。

示例:

CREATE TABLE test_table
(
       v_id NUMBER PRIMARY KEY,
       v_name VARCHAR2(20)
);
INSERT INTO test_table VALUES(1,'test1');
INSERT INTO test_table VALUES(2,'test2');
SELECT * FROM test_table;
BEGIN
       UPDATE test_table SET v_name = new_name WHERE v_id = &input_id;
EXCEPTION
       WHEN dup_val_on_index THEN
            dbms_output.put_line('在deptno列上不能出现重复值.');
END;

7 CASE_NOT_FOUND该异常触发ORA-06592错误。

在CASE语句中,如果CASE语句的条件在WHEN子句中没有找到对应的条件分支,且该CASE语句不包含ELSE分支,将触发该异常。

示例:

DECLARE
  v_test NUMBER;
BEGIN
  SELECT val INTO v_test FROM dual;
  CASE
    WHEN v_test = 1 THEN
      dbms_output.put_line(v_test);
    WHEN v_test = 2 THEN
      dbms_output.put_line(v_test);
    WHEN v_test = 3 THEN
      dbms_output.put_line(v_test);
      --ELSE
  -- dbms_output.put_line(v_test);
  END CASE;
EXCEPTION
  WHEN case_not_found THEN
    dbms_output.put_line('在CASE语句中没有与' || v_test || '相关的条件.');
END;

8 ZERO_DIVIDE该异常触发ORA-01476错误。

当在程序中使用0作为除数进行运算时,将触发该异常。

示例:

DECLARE
  v_test1 NUMBER := 100;
  v_test2 NUMBER := 0;
  v_test3 NUMBER := 0;
BEGIN
  v_test3 := v_test1 / v_test2;
  dbms_output.put_line(v_test3);
EXCEPTION
  WHEN zero_divide THEN
    dbms_output.put_line('0不能作为除数.');
END;

9 INVALID_NUMBER该异常触发ORA-01722错误。

数字或值错误,或字符到数值的转换错误。

示例:

BEGIN
  UPDATE emp SET sal = sal + '1oo'; -- 1oo
EXCEPTION
  WHEN invalid_number THEN
    dbms_output.put_line('输入的数字不正确.');
END;

DECLARE
  v_test  NUMBER;
  v_test2 NUMBER;
BEGIN
  v_test  := '100';
  v_test2 := '1a';
  dbms_output.put_line(v_test);
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('输入的数字不正确.');
END;

10 ACCESS_INTO_NULL该异常触发ORA-06530错误。

当程序中的对象还没有先进行对象初始化的操作,就直接为对象的属性赋值,将触发该异常。

示例:

DECLARE
  v_test test_type;
BEGIN
  v_test.v_name := 'test';
EXCEPTION
  WHEN access_into_null THEN
    dbms_output.put_line('首先初始化对象v_test');
END;

11 COLLECTION IS NULL该异常触发ORA-06531错误。

在给集合元素赋值前,必须先初始化该集合元素,否则触发该异常。

示例:

DECLARE
  TYPE emp_ssn_array IS TABLE OF NUMBER /* INDEX BY BINARY_INTEGER*/
  ;
  best_employees emp_ssn_array;
BEGIN
  best_employees(0) := '123456';
  dbms_output.put_line('best_employees(0): ' || best_employees(0));
EXCEPTION
  WHEN collection_is_null THEN
    dbms_output.put_line('必须初始化集合元素.');
END;

12 SUBSCRIPT_BEYOND_COUNT该异常触发ORA-06533错误。

当使用复合数据类型时,如果下标越界触发该异常。

示例:

DECLARE
  TYPE test_array IS VARRAY(20) OF NUMBER;
  v_test test_array;
BEGIN
  v_test := test_array(123456);
  dbms_output.put_line('v_test(1): ' || v_test(2));
EXCEPTION
  WHEN subscript_beyond_count THEN
    dbms_output.put_line('下标越界.');
END;

13 SUBSCRIPT_OUTSIDE_LIMIT该异常触发ORA-06532错误,当使用复合数据类型时,如果下标为负值时触发该异常。

示例:

DECLARE
  TYPE test_array IS VARRAY(20) OF NUMBER;
  v_test test_array;
BEGIN
  v_test := test_array(123456);
  dbms_output.put_line('v_test(-1): ' || v_test(-1));
EXCEPTION
  WHEN subscript_outside_limit THEN
    dbms_output.put_line('下标不能是负数.');
END;

14 LONIN_DENIED该异常触发ORA_01017错误。

当PL/SQL连接数据库时,如果密码错误,将触发该异常。

15 NOT_LOGGED_ON该异常触发ORA-01012错误。

如果PL/SQL没有连接数据库,程序运行将触发该异常。

16 PROGRAM_ERROR该异常触发ORA-06501错误。

如果出现该异常,则表示PL/SQL的内部问题。用户可能需要重新安装数据字典和PL/SQL系统包。

17 ROWTYPE MISMATCH该异常触发ORA-06504错误。

在赋值时,如果宿主游标变量和PL/SQL游标变量的返回类型不兼容,将触发该异常。

18 SELF_IF_NULL该异常触发ORA-30625错误。

在使用对象类型时,如果在NULL示例上调用成员方法将触发该异常。

19 STORAGE_ERROR该异常触发ORA-06500错误。

当PL/SQL块运行时,如果走出内在空间或内在被损坏则触发该异常。

20 SYS_INVALID_ROWID该异常触发ORA-01410错误。

当将字符串转变为ROWID时,如果使用了无效的字符串则触发该异常。

21 TIMEOUT_ON_RESOURCE该异常触发ORA-00051错误。

Oracle在等待资源时出现超时错误时将触发该异常。

22 ORA-04021Oracle在等待资源时出现超时错误,该资源可能被其它session锁住,此时将触发该异常。

23 ORA-01791 不是Selected表达式

SELECT DISTINCT goodsid, barcode, depotid, goodsname
  FROM sa_sale
 WHERE depotid = '11'
 ORDER BY selldate;

这句话执行的时候就有错误, 但把排序换成 order by  Goodsid 或其它 Barcode,DepotId,GoodsName的时候均没有错误,这是因为 selldate不在查询结果字段中,而且这个语句是distinct语句。所以会出现这个错误

下边给出正确的解决办法:

SELECT goodsid, barcode, depotid, goodsname
  FROM (SELECT DISTINCT goodsid, barcode, depotid, goodsname, selldate
          FROM sa_sale
         WHERE depotid = '11')
 ORDER BY selldate;

24 ORA-01002: fetch out of sequence当游标中数据集已经取完,然后再一次进行FETCH操作时将触发该异常。

25 PLS-00382: expression is of wrong type表达式类型错误。即在代码中赋予操作的左右两边的类型不等。

二 非预定义异常

使用非预定义异常的步骤如下:

定义异常 -> 关联异常和错误 -> 引用例外

当定义Oracle错误和例外之间的关联关系时,使用伪过程EXCEPTION_INIT。

下面以处理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错误时自动触发对应的异常。但是在实际应用中,可能还会遇到其它错误,这时,可以为特定的情况自定义异常,不过需要显示触发该异常:通过定义异常,然后关联异常和错误,显示触发异常,最后在EXCEPTION中处理该异常。

示例:

DECLARE
  l_error_message VARCHAR2(200);
  e_user_exception EXCEPTION;
BEGIN
  IF '出现错误' THEN
    l_error_message := '定义错误信息';
    RAISE e_user_exception;
  END IF;
EXCEPTION
  WHEN e_user_exception THEN
    raise_application_error(-20001, l_error_message);
END;

四 例外函数

1 SQLCODE,SQLERRM在PL/SQL块中出现Oracle错误时,通过使用例外函数可以取得错误号以及相关的错误消息。

SQLCODE 返回Oracle错误号。

SQLERRM 返回错误号对应的错误消息。

示例:

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;

2 RAISE_APPLICATION_ERROR在存储过程,函数和包中使用RAISE_APPLICATION_ERROR可以自定义错误号和消息。

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;

本文标题:oracle异常处理总结

本文链接:http://yedward.net/?id=43

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章