Oracle exception

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

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

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

1.CURSOR_ALREADY_OPEN（cursor_already_open）该异常触发ORA-06511错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. CURSOR test_cursor IS  
3. SELECT SYSDATE FROM dual;  
4. BEGIN  
5. OPEN test_cursor;  
6. FOR test_cursor2 IN test_cursor LOOP  
7. dbms_output.put_line(test_cursor2.SYSDATE);  
8. END LOOP;  
9. EXCEPTION  
10. WHEN cursor_already_open THEN  
11. dbms_output.put_line('游标已经打开，不能再次对游标执行打开操作。');  
12. END;  
13. /  

2. INCALID_CURSOR（incalid_cursor）该异常触发ORA-01001错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. CURSOR test_cursor IS  
3. SELECT SYSDATE FROM dual;  
4. test_time DATE;  
5. BEGIN  
6. --OPEN test_cursor;  
7. FETCH test_cursor  
8. INTO test_time;  
9. dbms_output.put_line('当前时间为: ' || test_time);  
10. CLOSE test_cursor;  
11. EXCEPTION  
12. WHEN invalid_cursor THEN  
13. dbms_output.put_line('请检查游标是否已经打开.');  
14. END;  
15. /  

3.NO_DATA_FOUND（no_data_found）该异常触发ORA-01403错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. test_owner DATE;  
3. BEGIN  
4. SELECT t.owner INTO test_owner FROM all_tables t WHERE t.table_name = 'test';  
5. dbms_output.put_line(test_owner);  
6. EXCEPTION  
7. WHEN NO_DATA_FOUND THEN  
8. dbms_output.put_line('No value.');  
9.   
10. END;  
11. /  

4. TOO_MANY_ROWS（too_many_rows）该异常触发ORA-01422错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. test_records dba_tables%ROWTYPE;  
3. BEGIN  
4. select * INTO test_records from dba_tables where owner='APPS' AND ROWNUM < 3;  
5. dbms_output.put_line(test_records.table_name);  
6. EXCEPTION  
7. WHEN too_many_rows THEN  
8. dbms_output.put_line('Too many values.');  
9. END;  
10. /  

5. VALUE_ERROR（value_error）该异常触发ORA-06502错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. v_test VARCHAR2(1);  
3. BEGIN  
4. SELECT 'WHAT' INTO v_test FROM dual;  
5. dbms_output.put_line(v_test);  
6. EXCEPTION  
7. WHEN value_error THEN  
8. dbms_output.put_line('变量的长度不够.');  
9. END;  
10. /  

6.DUP_VAL_ON_INDEX（dup_val_on_index）该异常触发ORA-00001错误。

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

示例：

[sql] view plain copy

 print?

1. CREATE TABLE test_table  
2. (  
3. v_id NUMBER PRIMARY KEY,  
4. v_name VARCHAR2(20)  
5. )  
6. INSERT INTO test_table VALUES(1,'test1');  
7. INSERT INTO test_table VALUES(2,'test2');  
8. SELECT * FROM test_table;  
9. BEGIN  
10. UPDATE test_table SET v_name = new_name WHERE v_id = &input_id;  
11. EXCEPTION  
12. WHEN dup_val_on_index THEN  
13. dbms_output.put_line('在deptno列上不能出现重复值.');  
14. END;  
15. /  

7. CASE_NOT_FOUND（case_not_found）该异常触发ORA-06592错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. v_test NUMBER;  
3. BEGIN  
4. SELECT val INTO v_test FROM dual;  
5. CASE  
6. WHEN v_test = 1 THEN  
7. dbms_output.put_line(v_test);  
8. WHEN v_test = 2 THEN  
9. dbms_output.put_line(v_test);  
10. WHEN v_test = 3 THEN  
11. dbms_output.put_line(v_test);  
12. --ELSE  
13. -- dbms_output.put_line(v_test);  
14. END CASE;  
15. EXCEPTION  
16. WHEN case_not_found THEN  
17. dbms_output.put_line('在CASE语句中没有与' || v_test || '相关的条件.');  
18. END;  
19. /  

8.ZERO_DIVIDE（zero_divide）该异常触发ORA-01476错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. v_test1 NUMBER := 100;  
3. v_test2 NUMBER := 0;  
4. v_test3 NUMBER := 0;  
5. BEGIN  
6. v_test3 := v_test1 / v_test2;  
7. dbms_output.put_line(v_test3);  
8. EXCEPTION  
9. WHEN zero_divide THEN  
10. dbms_output.put_line('0不能作为除数.');  
11. END;  
12. /  

 

9. INVALID_NUMBER（invalid_number）该异常触发ORA-01722错误。

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

示例：

[sql] view plain copy

 print?

1. BEGIN  
2. UPDATE emp SET sal = sal + '1oo'; -- 1oo  
3. EXCEPTION  
4. WHEN invalid_number THEN  
5. dbms_output.put_line('输入的数字不正确.');  
6. END;  
7. /  
8. DECLARE  
9. v_test NUMBER;  
10. v_test2 NUMBER;  
11. BEGIN  
12. v_test := '100';  
13. v_test2 := '1a';  
14. dbms_output.put_line(v_test);  
15. EXCEPTION  
16. WHEN INVALID_NUMBER THEN  
17. DBMS_OUTPUT.PUT_LINE('输入的数字不正确.');  
18. END;  
19. /  

10. ACCESS_INTO_NULL（access_into_null）该异常触发ORA-06530错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. v_test test_type;  
3. BEGIN  
4. v_test.v_name := 'test';  
5. EXCEPTION  
6. WHEN access_into_null THEN  
7. dbms_output.put_line('首先初始化对象v_test');  
8. END;  
9. /  

11. COLLECTION IS NULL（collection is null）该异常触发ORA-06531错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. TYPE emp_ssn_array IS TABLE OF NUMBER/* INDEX BY BINARY_INTEGER*/;  
3. best_employees emp_ssn_array;  
4. BEGIN  
5. best_employees(0) := '123456';  
6. dbms_output.put_line('best_employees(0): ' || best_employees(0));  
7. EXCEPTION  
8. WHEN collection_is_null THEN  
9. dbms_output.put_line('必须初始化集合元素.');  
10. END;  
11. /  

12. SUBSCRIPT_BEYOND_COUNT（subscript_beyond_count）该异常触发ORA-06533错误。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. TYPE test_array IS VARRAY(20) OF NUMBER;  
3. v_test test_array;  
4. BEGIN  
5. v_test := test_array(123456);  
6. dbms_output.put_line('v_test(1): ' || v_test(2));  
7. EXCEPTION  
8. WHEN subscript_beyond_count THEN  
9. dbms_output.put_line('下标越界.');  
10. END;  
11. /  

13.SUBSCRIPT_OUTSIDE_LIMIT（subscript_outside_limit）该异常触发ORA-06532错误

当使用复合数据类型时，如果下标为负值时触发该异常。

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. TYPE test_array IS VARRAY(20) OF NUMBER;  
3. v_test test_array;  
4. BEGIN  
5. v_test := test_array(123456);  
6. dbms_output.put_line('v_test(-1): ' || v_test(-1));  
7. EXCEPTION  
8. WHEN subscript_outside_limit THEN  
9. dbms_output.put_line('下标不能是负数.');  
10. END;  
11. /  

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,

[sql] view plain copy

 print?

1. barcode,  
2. depotid,  
3. goodsname  
4. FROM sa_sale  
5. WHERE depotid = '11'  
6. ORDER BY selldate  

 

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

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

[sql] view plain copy

 print?

1. SELECT goodsid,  
2. barcode,  
3. depotid,  
4. goodsname  
5. FROM (SELECT DISTINCT goodsid,  
6. barcode,  
7. depotid,  
8. goodsname,  
9. selldate  
10. FROM sa_sale  
11. WHERE depotid = '11')  
12. ORDER BY selldate  

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

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

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

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

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

下面以处理ORA-02291错误为例说明：

[sql] view plain copy

 print?

1. DECLARE  
2. e_integrity EXCEPTION;  
3. PRAGMA EXCEPTION_INIT(e_integrity, -2291);  
4. BEGIN  
5. UPDATE emp SET deptno = dno WHERE empno = &eno;  
6. EXCEPTION  
7. WHEN e_integrity THEN  
8. dbms_output.put_line('该部门不存在.');  
9. END;  
10. /  

三 自定义异常使用预定义异常和非预定义异常，程序可以捕获Oracle错误，并且在出现Oracle错误时自动触发对应的异常。但是在实际应用中，可能还会遇到其它错误，这时，可以为特定的情况自定义异常，不过需要显示触发该异常：通过定义异常，然后关联异常和错误，显示触发异常，最后在EXCEPTION中处理该异常。

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. l_error_message VARCHAR2(200);  
3. e_user_exception EXCEPTION;  
4. BEGIN  
5. ** ** ** ** **;  
6. IF '出现错误' THEN  
7. l_error_message := '定义错误信息';  
8. RAISE e_user_exception;  
9. END IF;  
10. EXCEPTION  
11. WHEN e_user_exception THEN  
12. raise_application_error(-20001, l_error_message);  
13. END;  
14. /  

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

SQLCODE 返回Oracle错误号。

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

示例：

[sql] view plain copy

 print?

1. DECLARE  
2. v_ename emp.ename%TYPE;  
3. BEGIN  
4. SELECT ename INTO v_ename FROM emp WHERE sal = &v_sal;  
5. dbms_output.put_line('雇员名:' || v_ename);  
6. EXCEPTION  
7. WHEN NO_DATA_FOUND THEN  
8. dbms_output.put_line('不存在工资为' || v_sal || '雇员');  
9. WHEN OTHERS THEN  
10. dbms_output.put_line('错误号' || SQLCODE);  
11. dbms_output.put_line(SQLERRM);  
12. END;  
13. /  

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(默认),则替换先前所有错误.

示例：

[sql] view plain copy

 print?

1. CREATE OR REPLACE PROCEDURE raise_comm  
2. (  
3. eno NUMBER,  
4. commission NUMBER  
5. ) IS  
6. v_comm emp.comm%TYPE;  
7. BEGIN  
8. SELECT comm INTO v_comm FROM emp WHERE emp = eno;  
9. IF v_comm IS NULL THEN  
10. raise_application_error(-20001, '该员工无补助.');  
11. END IF;  
12. EXCEPTION  
13. WHEN NO_DATA_FOUND THEN  
14. dbms_output.put_line('该雇员不存在.');  
15. END;  
16. /