Oracle exception

待我称王封你为后i 2021-06-26 16:06 401阅读 0赞

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

Oracle中异常分为：1.预定义异常 2.非预定义异常 3.自定义异常三种。

## 一、预定义异常 ##

预定义异常是指由PL/SQL所提供的系统异常。当PL/SQL应用程序违反了Oracle规则或出现其它系统限制的情况时，将会隐含地触发一个内部异常。以下是PL/SQL为我们预定义的异常(经常更新中)：

1.`CURSOR_ALREADY_OPEN`（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; /

1.  `INCALID_CURSOR`（`incalid_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`（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`（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`（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`（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`（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`（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`（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`（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`（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`（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`（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-04021` Oracle在等待资源时出现超时错误，该资源可能被其它`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;