Oracle:pl/sql 异常处理[转]

 

pl/sql 提供了强大而灵活的手段来捕捉和处理程序产生的异常，从而使 oracle 的用户远离一些令人烦恼的 bug 。

 

pl/sql 异常处理的概念和术语

 

在 oracle 中所有的错误都被认为是不应该发生的异常。一个异常可能是以下 3 种情况的一种：

u       由系统产生的错误（“ out of memory ”或“ duplicate value in index ”）

u       用户行为导致的错误

u       应用程序给用户的一个警告

pl/sql 用一种异常句柄的结构来捕捉和响应错误。正是有了异常句柄的存在，我们能很方便的分离异常处理代码与可执行代码。与线性的代码相比，为了处理异常，异常句柄提供了一种类似事件驱动的模式；换句话说，就是不管一种特定的错误在何时何地发生，它都将被同一个代码处理。

当一个错误出现后，无论它是系统还是程序产生的，都将导致一个异常。之后，可执行程序被中断，控制权转移给异常处理代码。处理完异常后，程序将不会回到先前被中断的位置，相反的，控制权被交给了当前程序的外围模块（可能是程序，也可能是系统）。

       procedure jimmy

       is

              new_value varchar(35)

       begin

|--------new_value:=old_value || ‘-new’;

|          if new_value like ‘like%’

|          then

|                 …..

|          end if;

|   exception

|----- à        when value_error

              then

                     …..

       end;

因为 old_value 是一个未被定义的变量，所以将产生一个错误，并将给异常处理模块处理。

 

从异常的可应用范围出发，可将异常分为两类：

系统异常：

由 oracle 定义并由 pl/sql runtime 引擎在检测到错误时产生的异常。一些系统异常有名字，比如 NO_DATA_FOUND ，然而大多数的异常仅仅只有数字编号和描述。这些异常无论在哪个 pl/sql 中程序都能被应用。

共有 21 个命名的系统异常：

命名的系统异常

产生原因

ACCESS_INTO_NULL

未定义对象

CASE_NOT_FOUND

CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时

COLLECTION_IS_NULL

集合元素未初始化

CURSER_ALREADY_OPEN

游标已经打开

DUP_VAL_ON_INDEX

唯一索引对应的列上有重复的值

INVALID_CURSOR

在不合法的游标上进行操作

INVALID_NUMBER

内嵌的 sql 语句不能将字符转换为数字

NO_DATA_FOUND

使用 select into 未返回行，或应用索引表未初始化的元素时

TOO_MANY_ROWS

执行 select iotn 时，返回超过一行

ZERO_DIVIDE

除数为 0

SUBSCRIPT_BEYOND_COUNT

元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT

使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR

赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED

Pl/sql 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ON

Pl/sql 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERROR

Pl/sql 内部问题，可能需要重装数据字典＆ pl./sql 系统包

ROWTYPE_MISMATCH

宿主游标变量与 pl/sql 游标变量的返回类型不兼容

SELF_IS_NULL

使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR

运行 pl/sql 时，超出内存空间

SYS_INVALID_ID

无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE

Oracle 在等待资源时超时

 

由程序员定义的异常：

程序员在程序中定义的异常，它只是在特定的程序种有效。可以使用 EXCEPTION_INT 这个 pragma 将一个无名字的系统异常与一个程序员定义的名字相关联。或者用 RAISE_APPLICATION_ERROE 来自己定义一个异常的数字编号和描述。

 

按异常生成方式可分为：

预定义异常：

       就是上面表中的 21 种有名字的系统异常。

非预定义异常：

       没名字的系统异常，可以用 pragma exception_int 给它关联一个名字。

自定义异常：

       需要用 RAISE 或 RAISE_APPLICATION_ERROR 生成的异常。

 

下面是一些要用到的属于 ;

Exception section （异常处理模块）

它是 pl/sql 语句块种包含一个或多个异常句柄的部分。 Exception section 的结构基本上与 case 相似。

Raise （产生）

通过通知 pl/sql runtime 引擎有错误来中止当前程序的运行。也可通过显式的请求，如： RAISE 或 RAISE_APPLICATION_ERROR 来 RAISE 一个异常。

Handle （句柄，某一个异常处理的代码）

在 exception section 中捕捉错误。可以在 handle 中编写程序来处理异常，比如将错误记入 log 中，显示一个错误信息，将异常传出当前程序快。

Scope （作用范围 ）

       一个异常从产生、被捕捉到处理整个过程所处的程序部分。

Propagation （传递）

如果一个异常没有被处理，那么它将被传递到但前块的上一级，它有可能是另一个代码快，也可能是系统。

Unhandled exception （未被处理的异常）

如果一个异常没有被处理，并一直被传递道理系统中，那么它被称为 unhandled exception 。

Un-named or anonymous excepttion （匿名异常）

       （在异常类型中有介绍）

Named exception （命名异常）

       包括系统异常中有名字的那部分和用户定义的名字。

 

异常定义

 

在一个异常产生、被捕获并处理之前，它必须被定义。 Oracle 定义了几千个异常，绝大多数只有错误编号和相关描述，仅仅命名了若干个最常被用到的异常。这些名字被储存在 STANDARD ， UTL_FILE ， DBMS_SQL 这几个系统包中，详情请见 oracle:pl/sql 异常处理（ 1 ）。

出自之外的绝大多数异常需要程序员命名。有 2 种命名异常的方法：

 

1 ：声明一个自定义异常

在 STANDARD 中的命名了的异常基本山是与系统的错误相关的（当然那些只有 errorcode 的异常也是这样），但在实际的应用中我们经常需要与特定的应用程序相关的异常，由程序员声明的异常就是用于处理这种情况的。

Oracle 异常处理模块的方便的地方在于，它并没有区别对待自定义的与预定义的异常。这使得我们可以像对待预定义异常一样，捕捉和处理自定义异常，只是在此之前需要声明它；同时对于一个自定义的异常，我们需要用 RAISE 来手动产生。

下面是一个声明的例子：

       procedure calc_ammul_sales

              (company_id_in in company.company_id%tye)

       is

              invalid_company_id exception;

              negative_balance excrption;

              duplicate_company Boolean;

       begin

              /*body of executable statement*/

       exception

              when invalid_company_id

              then /*handle exception*/

              when no_data_found

              then /*handle exception*/

              /*…..*/

       end;

需要注意的是处理定义的时候，只有两个地方会出现自定义的异常：

ü         raise exception ；

ü         when exception then

 

2 ：为非预定义异常关联一个名字

仅仅 21 个预定义异常对我们来说实在是太少了，还有几千个异常只有 errorcode 和描述。另外，程序员也可以用 RAISE_APPLICATION_ERROR 定义一个含 errorcode 和描述的异常。

当然，只用 errorcode 也可以很好地完成工作，只要你不担心会忘了那串数字代表的意思就行。比方说 ;

       exception

              when others

              then

                     if sqlcode=-1843 then  /*sqlcode 是内建的用于返回最近一次错误编号的函数 */

                     …..

这的确是一段让人感到晦涩的代码，还是给它关联个名字吧。

我们要用到的是 pragma exception_init(exception,integer) ，然后就可以像对待预定义异常一样对待它了，我是说没必要像上面的那种一样用 raise 。 Exception_init 是一个编译时运行的函数，它只能出现在代码的声明部分，而异常名字必须在此之前被定义。下面用一个匿名过程举个例子：

       declare

              invalid_company_id exception;

              pragma exception_init(invalid_company_id, -1834);

要注意的时：

ü         不可以用 -1403 （ no_data_found ），用 100 ，事实上 exception_init 中的 integer 对应的是 sqlcode 返回的值。

ü         不能为 0 ，不能大于 100 ，不能小于 -1000000

一个例子：

       procedure delete_company(company_id_in in number)

       is

              still_have_emplyee exception;

              pragma exception(still_have_employee, -2293);

       begin

              delete from compamy

              where company_id=company_id_in;

       exception

              when still_have_employee

              then dbms_output.put_line(‘delete employees for company first’);

       end;

在一下两种情况下，我们有必要使用 exception_init ：

ü         一个非预定义异常是经常要被用到的。

ü         我们将用 raise_applocation_error 产生了一个自定义的 errorcode 时。

一种简便的方法是将以上两种情况中的异常定义在一个包中，这样我们就没有必要每次都重复定义了。

       Create or replace package dynsql

       Is

              Invalid_table_name exception;

              Pragma exception_init(invalid_table_name, -903);

              Invalid_column_name exception;

              Pragma exception_init(invalid_column_name, -904);

              En_too_young const number:=-200001;

              Exc_too_young exception;

              Pragma exception_init(exc_too_young, -20001);

       End;

有了上面这个包，就可以方便的处理异常了 ;

       procedure validate_emp(birthdate in date)

       is

              min_tear const pls_integer:=18;

       begin

              if add_month(sysdate,min_year*12*-1)<birthdate_in

              then

raise_application_error(dynsql.en_too_young, ‘employee must be’ || min_year ||‘old’);

              end if;

       end;

除了 standard 包中的 21 个预定义异常外，还有一些包也定义了一些异常。但与 standard 包中异常不同的是，在使用这些异常时，需要带上包的名字。如：

       when dbms_lob.invalid_argval then ……

 

非常有用的一点是，可以在最外层的 pl/sql 块的异常处理模块中加入 others ，这样就可以把从内部传递出来的未被处理的剩余异常全部处理掉了。

       Exception

       When others

              Then ….

 

处理 oracle 系统自动生成系统异常外，可以使用 raise 来手动生成错误。

l         Raise exception;

l         Raise package.exception;

l         Raise;

以上是 raise 的三种使用方法。第一种用于生成当前程序中定义的异常或在 standard 中的系统异常。

       Declare

              Invalid_id exception;

              Id_values varchar(2);

       Begin

              Id_value:=id_for(‘smith’);

              If substr(id_value,1,1)!=’x’

              Then

                     Raise invalid_id;

              End if;

       Exception

              When invalid_id

              Then

                     Dbms_output.put_line(‘this is an invalid id!’);

       End;

这是一个生成自定义异常的例子，当然也可以生成系统异常：

       declare

              employee_id_in number;

       Begin

Select employee_id into employee_id_in from employ_list where employee_name=&n;

If employee_id_in=0

Then

       Raise zero_devided;

End if;

       Exception

              When zero_devided

              Then

                     Dbms_output.put_line(‘wrong!’);

       End;

有一些异常是定义在非标准包中的，如 UTL_FILE ， DBMS_SQL 以及程序员创建的包中异常。可以使用 raise 的第二种用法来生成异常。

       If day_overdue(isbn_in, browser_in) > 365

       Then

              Raise overdue_pkg.book_is_lost

       End if;

在最后一种 raise 的形式中，不带任何参数。这种情况只出现在希望将当前的异常传到外部程序时。

       Exception

              When no_data_found

              Then

                     Raise;

       End;

 

Pl.sql 使用 raise_application_error 过程来生成一个有具体描述的异常。当使用这个过程时，当前程序被中止，输入输出参数被置为原先的值，但任何 DML 对数据库所做的改动将被保留，可以在之后用 rollback 命令回滚。下面是该过程的原型：

       Procedure raise_application_error(

       Num binary_integer;

       Msg varchar2;

       Keeperrorstack Boolean default false

)

其中 num 是在 -20999 到 -20000 之间的任何数字（但事实上， DBMS_OUPUT 和 DBMS_DESCRIBLE 包使用了 -20005 到 -20000 的数字）； msg 是小于 2K 个字符的描述语，任何大于 2K 的字符都将被自动丢弃； keeperrorstack 默认为 false ，是指清空异常栈，再将当前异常入栈，如果指定 true 的话就直接将当前异常压入栈中。

    CREATE OR REPLACE PROCEDURE raise_by_language (code_in IN PLS_INTEGER)

    IS

       l_message error_table.error_string%TYPE;

    BEGIN

       SELECT error_string

         INTO l_message

         FROM error_table, v$nls_parameters v

        WHERE error_number = code_in

          AND string_language  = v.VALUE

          AND v.parameter = 'NLS_LANGUAGE';

 

       RAISE_APPLICATION_ERROR (code_in, l_message);

    END;

 

异常处理

 

当异常生成之后，程序被中止，控制权交给异常处理模块，异常处理模块捕获当前异常句柄，并交由相应的程序处理；如果，异常促里模块没有捕捉到异常句柄，那么它将被传输到当前程序的外围。

 

除非由一些特殊的要求，一般情况下异常将再当前程序的异常处理模块中被处理。异常处理模块以 EXCEPTION 开始 END; 结尾。

       Declare

              /*…………*/

       begin

              /*…………*/

       exception

              when /* 异常名称 */

              then /* 异常处理 */

              when other

              then /* 异常处理 */

       end;

异常处理模块的语法基本上以 CASE 一致，凡是在 when 中有定义的异常都将被处理，而没有的则被传输。一个特殊的异常处理语句是 WHEN OTHERS 。就想在（ 3 ）中所说的，它会处理所有为被处理的异常，因此必须小心使用它，最好是在最外层的程序中。当然如果喜欢偷懒的，大可以在异常处理模块中只放一个 OTHERS 。注意，无论哪种情况， OTHERS 只能这只在异常处理的最后一位。

 

有趣的是，可以在一个 when 中处理多个异常句柄。

       Exception

              When no_data_found or invalid_employee_id or dbms_ldap.invalid session

              Then /*………..*/

       End;

       /

在这个例子里，有标准包的异常、自定义异常和非标准包中的异常。这些异常只能用 or 连接，不可以用 and ，因为只有一个异常能够生成。

 

非 raise_application_error 生成的异常，如果没有被处理而一直传递到系统环境中，那么环境将视情况作出相应的反映。在 sqlplus 中， oracle 将回滚所有 DML 对数据所做的修改。在 sqlplus 环境中，因为有自动回滚的存在，我们可以保留出现未被处理的异常的可能性；而在另外的一些环境中，则需要仔细设计最外层程序。

ü         捕捉任何有可能传出的异常。

ü         记录错误以便于分析。

ü         给外部环境一个信息，以便于其作出相应的处理。

 

对于自定义异常，因为 sqlcode 值永远是 1 ，所以当它被传出时，如果外围程序中没有定义相同名称的异常，我们将不知道是什么异常产生了。因此，不要将自定义异常传递出去。

 

在程序中处理几个互相独立的操作时，为了避免出现因为一个操作产生异常而使整个程序被中断的情况，有必要将这些独立的操作放在各自的虚拟块中。

       Procedure change_data is

       Begin

              Begin

                     Delete from employee where …..

              Exception

                     When others then null;

              End;

 

              Begin

                     Update company set …….

              Exception

When others then null;

              End;

 

              Begin

                     Insert into company_history select * from company where ….

              Exception

                     When others then null;

              End;

       End;

       /

 

 

Pl/sql 提供了一些内建的函数来帮助我们确定、分析异常。

 

SQLCODE

这个函数在前面有提到过，它是一个用于返回当前模块中最近一次异常值的函数，或者说是非入栈程序的异常值。打个比方：如果在当前程序的异常模块中调用了另一个程序， oracle 将当前程序及相应的环境变量（包括异常值）压入系统栈；在被调用程序中生成了一个值为 1 的异常，那么 sqlcode 将返回 1 ；之后刚才的程序出栈， sqlcode 返回当前异常值。需要注意的是，不要在异常模块之外使用它，这样不会有任何意义。当没有异常或在异常模块之外使用时， SQLCODE 返回 0 ；返回值 1 是指自定义异常。

 

SQLERRM

接收异常值，返回相应的长度不超过 512 字节的描述语。如果没有传入异常值，则返回当前异常描述。

       Begin

              Dbms_output.put_line( sqlerrm(-1403);

       End;

Sql>/

Ora-1403: no data found

在需要体构长度超过 512 字节的描述时， oracle 建议使用 dbms_utility.format_error_stack 。显然，用这个函数来判断一个异常是否为系统异常是很有用的，如果不是的话，将返回以下两种情况的一种。

如果是一个负数：

       ora-nnnnn: message not found,; product=rdbms; facility=ora

如果是一个正数：

       -nnnnn: non-oracle exception

 

DBMS_UTILITY.FORMAT_ERROR_STACK

返回当前异常相应的描述，没有字符长度限制。与 SQLCODE 相同的是，必须在异常处理模块中使用。虽然名称中有一个 stack 在，但通过它并不能知道异常的最初生成处，需要的话就必须使用 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 。

 

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

系统为最近一次生成的异常设置了一个栈，并跟踪它的传递过程，而这个函数使用这个栈，然后返回该异常的整个传递过程。这个函数对错误的定位和实施下一步处理起着至关重要的作用。

       Create or replace procedure procl is

       Begin

              Dbms_output.put_line(‘running proc1’);

              Raise no_data_found;

       End;

       /

       create or replace procedure proc2 is

       begin

              dbms_output.put_line(‘calling proc1’);

              proc1;

       end;

       /

       create or replace procedure proc3 is

       begin

              dbms_output.put_line(‘calling proc2’);

              proc2;

       exception

              when no_data_found

              then

                     dbms_output.put_line(‘error stack at top level’);

                     dbms_output.put_line(dbms_utility.format_error_backtrace);

       end;

       /

现在可以运行 proc3 来看看结果。

Sql>set serveroutput on;

Sql>begin

2                         dbms_output.put_line(‘proc3->proc2->proc1 backtrace’);

3                         proc3;

4     end;

5     /

    Proc3 -> Proc2 -> Proc1 backtrace

    calling proc2

    calling proc1

    running proc1

    Error stack at top level:

    ORA-06512: at "SCOTT.PROC1", line 4

    ORA-06512: at "SCOTT.PROC2", line 5

ORA-06512: at "SCOTT.PROC3", line 4

事实上，每次异常的产生都将重置这个异常栈，只是最后一次从系统栈出栈的是最外层的程序块，所以可以清楚地看到异常生成的整个过程。上面这个程序的执行过程是这样的：首先用 put_line 打印 Proc3 -> Proc2 -> Proc1 backtrace ， 调用 proc3 ，当前程序入栈 => 打印 calling proc2 ，调用 proc2 ， proc3 入栈 => 打印 calling proc1 ，调用 proc1 ， proc2 入栈 => 打印 running proc1 ，生成 no_data_found 异常，该异常被压入异常栈中 =>  proc2 出栈，并检测到来自第 5 行调用传递过来的异常，将它在此压入异常栈 => proc3 出栈，并检测到来自第 4 行调用传递过来的异常，将它在此压入异常栈， dbms_utility.format_error_backtrace 将异常栈中信息反相打印出来 =>  最外层程序出栈， end 。

以下是正确使用这个函数的一些注意事项：

ü         在当前程序的异常处理模块中调用这个函数。

ü         避免在中间程序中使用异常处理模块。

这样异常就能被正确地传输到最外层程序中，并打印出这个过程了。

 

 

本文出处：http://www.cppblog.com/ivenher/articles/14133.html