一、概述 异常分成三大类：预定义异常、非预定义异常、自定义异常 处理方法分为：直接抛出异常、内部块处理异常、游标处理异常 预定义异常：由PL/SQL定义的异常。由于它们已在standard包中预定义了，因此，这些预定义异常可以直接在程序中使用，而不必再定义部分声明。 非预定义异常：用于处理预定义异常所不能处理的Oracle错误。 自定义异常：用户自定义的异常，需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是Oracle错误，例如它可能是一个数据错误。 三种异常中，预定义与非预定义异常都与Oracle错误有关，并且由Oracle隐含自动抛出， 而自定义异常与Oracle错误没有任何关联，由开发人员为特定情况所定义的异常，需要显式抛出(raise)。 二、预定义异常 1、预定义异常的种类 1.1、ACCESS_INTO_NULL 说明：对应于ORA-06530，当创建对象类型应用是，在引用对象属性之前，如果没有初始化对象， 直接为对象属性赋值，则出发此异常。 例子：create type emp_type as object(name varchar2(10),sal number(6,2)); declare emp emp_type; begin emp.name:='scott'; exception when ACCESS_INTO_NULL dbms_output.put_line('未初始化'); end; 1.2、CASE_NOT_FOUND 说明：对应于ORA-06592，编写CASE语句时，如果在when子句中没有包含必须的条件分支并且没有包含else子句，就会出发此异常。 1.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(name_table(2)); exception when COLLECTION_IS_NULL then dbms_output.put_ling('未初始化'); end; 1.4、CURSOR_ALREADY_OPEN 说明：ORA-06511，当打开已经打开的游标时，会触发此异常。 1.5、DUP_VAL_ON_INDEX 说明：对应于ORA-00001，当在唯一索引所对应的列上键入重复值时，会触发此异常。 1.6、INVALID_CURSOR 说明：对应于ORA-01001，当试图在不合法的游标上执行操作时，会触发此异常。例如从未打开的游标提取数据或关闭未打开的游标。 1.7、INVALID_NUMBER 说明：对应于ORA-01722，当内嵌sql语句不能有效地将字符转变成数字时，会隐含地触发此异常。 1.8、NO_DATA_FOUND 说明：对应于ORA-01403，当执行selct into未返回行，或者引用了索引表未初始化元素时，会触发此异常。 1.9、TOO_MANY_ROWS 说明：对应于ORA-01422，当执行select into返回超过一行，则触发此异常。 1.10、ZERO_DIVIDE 说明：对应于ORA-01476，当除数为0时，触发此异常。 1.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; 1.12、SUBSCRIPT_OUTSIDE_LIMT 说明：对应于ORA-06532，当使用嵌套表或VARRAY元素时，如果下标为取负数，会触发此异常。 1.13、VALUE_ERROR 说明：对应于ORA-06502，当执行赋值操作时，如果变量长度不足以容纳实际数据，会触发此异常。 1.14、LOGIN_DENIED 说明：对应于ORA-01017，当连接Oracle数据库时，如果用户名或密码不正确，会触发此异常。 1.15、NOT_LOGGED_ON 说明：对应于ORA-01012，如果在没有连接到数据库的情况下，执行PL/SQL块，会触发此异常。 1.16、PROGRAM_ERROR 说明：对应于ORA-06501，如果出现此错误，则表示存在PL/SQL内部问题，用户此时可能需要重新安装数据字典和PL/SQL系统包。 1.17、ROWTYPE_MISMATCH 说明：对应于ORA-06504，当执行赋值操作时，如果宿主游标变量和PL/SQL游标变量的返回类型不兼容，会触发此异常。 1.18、SELF_IS_NULL 说明：对应于ORA-30625，当使用对象类型时，如果在null实例上调用成员方法，会触发此异常。 1.19、STORAGE_ERROR 说明：对应于ORA-06500，PL/SQL块运行时，如果超出内存空间或内存被损坏，会触发此异常。 1.20、SYS_INVALID_ROWID 说明：对应于ORA-01410，当将字符转变为ROWID时，如果使用了无效的字符串，会触发此异常。 1.21、TIMEOUT_ON_RESOURCE 说明：对应于ORA-00051，如果Oracle在等待资源时出现超时错误，会触发此异常。 2、预定义异常的处理 说明：这里说两个常见的异常no_data_found和too_many_rows，这两个异常多由select into语句触发。 举例： declare v_cnt number :=800; v_name emp.ename%type; begin select ename into v_name from emp where sal=v_cnt; dbms_output.put_line('姓名：' || v_name); exception when no_data_found then --直接抛出异常 dbms_output.put_line('不存在该工资值的雇员'); when too_many_rows then dbms_output.put_line('存在多个雇员具有该工资'); when others then rollback; dbms_output.put_line('异常回滚退出'); end; 2.1、no_data_found异常 起因：给一个变量赋值时，查询的结果为空。 说明：如上面例子可以看到，一旦直接抛出异常，就会让过程中断。no_data_found这种异常，没有严重到要让程序中断的地步，可以完全交给由程序进行处理。 2.1.1、使用内部独立块处理 说明：这是一种比较好的处理方式了，不会因为这个异常而引起程序中断。 例如： declare v_cnt:=800; v_name emp.ename%type; begin begin select ename into v_name from emp where sal=v_cnt; exception when no_data_found then v_name:=''; end; dbms_output.put_line('姓名：' || v_name); exception when too_many_rows then dbms_output.put_line('存在多个雇员具有该工资'); when others then rollback; dbms_output.put_line('异常回滚退出'); end; 2.1.2、使用游标处理 说明：游标操作可以完全避免no_data_found异常。 例如： declare v_cnt:=800; v_name emp.ename%type; cursor c_cursor is select ename from emp where sal=v_cnt; begin open c_cursor; fetch c_cursor into v_name; close c_cursor; dbms_output.put_line('姓名：' || v_name); exception when too_many_rows then dbms_output.put_line('存在多个雇员具有该工资'); when others then rollback; dbms_output.put_line('异常回滚退出'); end; 2.2、too_many_rows异常 起因：给一个变量赋值时，查询的结果有多条记录或select into 语句中变量名与表名相同。 说明：返回多条记录如果是可接受的，必须采用游标处理；如果是不可接受的，必须采用内部快处理。 （1）、返回多条记录如果是可接受的，则随便取一条，用游标处理。 （2）、返回多条记录如果是不可接受的，则必须捕获异常，用内部块处理。 举例： declare v_cnt:=800; v_name emp.ename%type; begin begin select ename into v_name from emp where sal=v_cnt; exception when no_data_found then v_name:=''; when too_many_rows then v_name:=''; dbms_output.put_line('存在多个雇员具有该工资'); end; dbms_output.put_line('姓名：' || v_name); exception when others then rollback; dbms_output.put_line('异常回滚退出'); end; 三、非预定义异常 说明：非预定义异常用于处理与21个预定义异常无关的Oracle错误。 由于预定义异常只是与一部分Oracle错误相连的异常，所以如果要处理没有与预定义异常对应的Oracle错误时， 则需要为这些Oracle错误声明相应的非预定义异常。声明这样的异常需要使用exception_init编译指令。 exception_init编译指令的定义如下： pragma exception_init(exception_name,Oracle_error_number); exception_name是预先被声明的异常名，Oracle_error_number是错误号，这条命令必须写在定义部分。 例子： declare e_inte exception; --定义 pragma exception_intt(e_inte,-2291); --关联Oracle错误ORA-2291 begin update emp set deptno=&dno where empno=&eno; exception when e_inte then dbms_output.put_line('部门不存在'); end; 注意，通过exception_init，一个自定义异常只能和一个Oracle错误相连，在异常处理语句中， sqlcode和sqlerrm将返回这个Oracle错误的代码和消息文本，而不是返回用户自定义消息 四、自定义异常 说明：自定义异常与Oracle错误没有任何关联，由开发人员为特定情况所定义的异常，需要显式抛出(raise)。 1、自定义异常的简述 尽管自定义异常的声明与变量的声明类似，但异常是一个错误状态，而不是一个数据项， 所以异常不能出现在赋值语句和sql语句中，但异常的作用域与定义部分其它变量的作用域相同。 如果一个自定义异常被传递到作用域外，则不能再通过原来的名字引用它。为了解决这个问题， 我们可以在包中声明异常，这个异常就可以在任何块中使用，使用时在异常前加包名前缀即可。 自定义异常由raise语句产生(由exception_inti编译指令声明的用户自定义异常也可通过对应的Oracle错误的出现而产生)， 当然如果需要，预定义异常也可以使用raise语句来产生。 当一个异常产生是，控制权立即转交给块的异常处理部分。如果该块没有异常处理部分， 则向该块的外一层块传递。一旦控制权交给了异常处理部分，则再没有办法回到块可执行部分。 一条异常处理语句可以处理多个异常，只要在when子句中由or分割多个异常即可。 如果块中的异常没有被处理，则该块会带着未处理的异常返回调用它的程序，这会导致调用它的程序出错。 如果在存储过程中出现异常，则存储过程的out参数将得不到返回值。为了避免未处理异常带来的弊病， 我们最好在块的最外层使用others子句处理块中所有未处理的异常。这样就可以确保所有的错误都能被发现和处理。 如果是定义部分的一个赋值语句产生了异常。即使在当前块的异常处理部分中有处理该异常的处理语句，也不去执行， 而是立刻被传递到外部块中。当异常传递到外部块中以后，按照处理可执行部分中产生的异常一样去处理该异常。 在异常处理语句中也可以产生异常，这个异常可以通过raise语句产生，或是由于出现一个运行错误而产生。 这两种情况下产生的异常都被立刻传递到块外，这与定义部分产生的异常一样。为什么这样处理呢？ 因为异常部分每一次只能有一个异常被处理，当一个异常被处理是，产生了另外一个异常， 而一次不能同时处理多个异常，所以将异常处理部分产生的异常传递到块外。 2、自定义异常的简单应用 说明：用户定义异常类型，使用raise显示抛出异常 declare v_name:='mary'; v_dno:=80; e_integrity exception; --定义自定义异常 e_no_rows exception; pragma exception_init(e_integrity,-2291); name emp.ename%type:=v_name; dno emp.deptno%type:=v_dno; begin update emp set deptno=v_dno where ename=v_name; if sql%notfound then raise e_no_rows; --显示抛出异常 end if; exception when e_integrity then dbms_output.put_line('该部门不存在'); when e_no_rows then dbms_output.put_line('该雇员不存在'); end; 3、raise抛出异常的三种方法 1）、Raise exception:用于抛出当前程序中定义的异常或在 standard 中的系统异常。 2）、Raise package.exception:用于抛出有一些异常是定义在非标准包中的，如UTL_FILE，DBMS_SQL以及程序员创建的包中异常 3）、Raise:不带任何参数，这种情况只出现在希望将当前的异常传到外部程序时。 五、异常的函数 说明：当出现异常，通过使用异常函数可以取得错误号及相关错误信息，另外通过使用raise_application_error也可自定义错误号与错误信息。 1、sqlcode和sqlerrm函数 sqlcode返回异常对象的错误代码号，sqlerrm返回的是对应的错误信息，为了在plsql中处理其他未预料的Oracle错误， 可以在异常处理部分的when others子句后引用这两个函数来确定错误号和信息。 异常种类 SQLCODE SQLERRM Oracle错误对应的异常 负数 Oracle错误 NO_DATA_FOUND +100 No data found 自定义异常 -1 User-Defined Exception 没有产生异常 0 Oracle-0000 注意：如果使用exception_init预编译指令声明与Oracle错误相连的自定义异常，则SQLCODE和SQLERRM返回 对应的Oracle错误代码和相应的错误信息，而不是返回+1和User-Defined。 如果SQLERRM是可以带一个数字参数，返回值是与这个数字参数相关的文本。 如果要在sql语句中使用sqlcode和sqlerrm，则一定要先把它们的值赋给局部变量，然后再将这些局部变量用在sql语句中。 因为这些函数是过程性的，不能直接用在sql语句中。 EXCEPTION WHEN OTHERS THEN ROLLBACK; v_message := '错误行号:' || DBMS_UTILITY.format_error_backtrace () || '错误代码:'|| SQLCODE|| '错误提示'|| SQLERRM; DBMS_OUTPUT.put_line (v_message); END; 2、raise_application_error 说明：该过程用于自定义错误信息，仅限数据库端子程序使用（过程、函数、包、触发器），不能在匿名块或客户端子程序中。。 语法：raise_application_error(error_number,mesage[,[true|false]]); 其中error_number定义错误号，必输是-20000到-20999之间的负整数；message指定错误信息，不长于2048字节； 第三个为可选参数，true则错误会被放在先前错误的堆栈中，fale则替换先前所有错误，默认为false。 例子： create or replace procedure raise_comn(eno number,commission number) is v_comm emp.comm%type; begin select comm into v_comm from emp where empno=eno; if v_comm is null then raise_application_error(-20001,'该雇员无补助'); end if; exception when no_date_found then dbms_output.put_line('雇员不存在'); end; 六、PL/SQL编译警告 说明：10g后新增功能，为了提高plsql子程序的健壮性并避免运行错误，可以激活警告检查功能。 1、PL/SQL警告的分类 severs：检查可能出现的不可预料的结果或错误结果，例如参数别名。 performance：检查可能引起的性能问题，例如执行insert时为number列提供varchar2数据。 informational：检查子程序中的死代码。 all：检查所有警告（上面3种都检查）。 2、PL/SQL警告消息的控制 说明：为了激活警告功能，需要设置初始化参数PLSQL_WARNINGS。 可以通过系统级、会话级、DBMS_VARNINGS系统包、ALTER PROCEDURE命令设置。 可以激活或禁止所有警告或某种警告。 alter system set plsql_warnings='enable:all'; --系统级 alter session set plsql_warnings='enableerformance'; --会话级 call dbms_warning.set_warning_setting_string('enable:severs','session'); --DBMS_VARNINGS系统包 alter procedure hello compile plsql_warnings='enableerformance'; --ALTER PROCEDURE命令 alter session set plsql_warnings='disable:all'; 3、PL/SQL警告的使用 1）、检测死代码 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; alter session set plsql_warnings='enable:informational';--激活 alter procedure dead_code compile;--编译 show errors;--显示 2）、检测引起性能问题的代码 create or replace precedure update_sql(name varchar2,salary varchar2) is begin update emp set sql=salary where ename=name; end; alter session set plsql_warnings='enable:informational';--激活 alter procedure dead_code compile;--编译 show errors;--显示
