处理 Oracle 例外

1. 例外分类

    1)  预定义例外

         Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

    2)  内部定义的非预定义例外

         Internally defined exceptions (ORA-n errors) are described in Oracle Database Error Messages.The runtime system raises them implicitly (automatically).

         An internally defined exception does not have a name unless either PL/SQL gives it one or you give it one.

    3) 自定义例外

         You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.

     An exception name declaration has this syntax: exception_name EXCEPTION;

             Table 11-2 Exception Categories

Category	Definer	Has Error Code	Has Name	Raised Implicitly	Raised Explicitly
Internally defined	Runtime system	Always	Only if you assign one	Yes	Optionally
Predefined	Runtime system	Always	Always	Yes	Optionally
User-defined	User	Only if you assign one	Always	No	Always

             表格 11-2

2. 处理预定义例外

    预定义例外是指由PL/SQL所提供的系统例外。当PL/SQL 应用程序违反了 Oracle 规则或系统限制时，则会隐含地触发一个内部例外。为了处理各种常见的 Oracle 错误，PL/SQL为开发人员提供了二十多种预定义例外，每个预定义例外都对应一个 Oracle 系统错误。如下例：当雇员表不存在指定名字的雇员时，将触发NO_DATA_FOUND预定义例外。

DECLARE
   v_sal emp.sal%TYPE;
BEGIN
   SELECT sal INTO v_sal WHERE LOWER(ename) = LOWER('&name');

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('该雇员不存在');
END;  

/

 

 

                Table 11-3 lists the names and error codes of the predefined exceptions.：

Exception Name	Error Code
ACCESS_INTO_NULL	-6530
CASE_NOT_FOUND	-6592
COLLECTION_IS_NULL	-6531
CURSOR_ALREADY_OPEN	-6511
DUP_VAL_ON_INDEX	-1
INVALID_CURSOR	-1001
INVALID_NUMBER	-1722
LOGIN_DENIED	-1017
NO_DATA_FOUND	+100
NO_DATA_NEEDED	-6548
NOT_LOGGED_ON	-1012
PROGRAM_ERROR	-6501
ROWTYPE_MISMATCH	-6504
SELF_IS_NULL	-30625
STORAGE_ERROR	-6500
SUBSCRIPT_BEYOND_COUNT	-6533
SUBSCRIPT_OUTSIDE_LIMIT	-6532
SYS_INVALID_ROWID	-1410
TIMEOUT_ON_RESOURCE	-51
TOO_MANY_ROWS	-1422
VALUE_ERROR	-6502
ZERO_DIVIDE	-1476

         表格 11-3


3. 处理非预定义例外

       非预定义例外用于处理与预定义例外无关的 Oracle 错误。使用预定义例外，只能处理21个 Oracle 错误。为了提高 PL/SQL 程序的健壮性，应该在 PL/SQL 应用程序中合理地处理这些 Oracle 错误。使用非预定义例外的步骤如下：

• 定义例外
• 关联例外和错误
• 引用例外

      ORA-02291: 违反完整约束条件 (UNA_HR.FK_T1_USERID) - 未找到父项关键字

DECLARE
   e_integrity EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_integrity, -2291);  
BEGIN
   UPDATE emp SET deptno = &deptno WHERE empno=&empno;

EXCEPTION
   WHEN  e_integrity THEN
      dbms_output.put_line('该部门不存在');
END;  
/

 

 

4. 处理自定义例外

    自定义例外是指由 PL/SQL 开发人员所定义的例外。预定义例外和非预定义例外都与 Oracle 错误有关，并且出现 Oracle 错误时会隐含触发相应例外；而自定义例外与 Oracle 错误没有任何联系，它是由开发人员为特定情况所定义的例外。

    使用自定义例外的步骤如下：

• 定义例外
• 显示触发例外
• 引用例外

DECLARE 
   e_integrity EXCEPTION;
   PRAGMA EXCEPTION_INIT(e_integrity, -2291);   
   e_no_employee EXCEPTION;
BEGIN
   UPDATE emp SET deptno = &deptno WHERE empno=&empno;

   IF SQL%NOTFOUND THEN
      RAISE e_no_employee;
   END IF;

EXCEPTION
   WHEN e_integrity THEN
      dbms_output.put_line('该部门不存在');

   WHEN e_no_employee THEN
      dbms_output.put_line('该雇员不存在');

END;   

/

 

 5. 使用例外函数

• SQLCODE 和 SQLERRM

          SQLCODE用于返回 Oracle 错误号，而 SQLERRM 则用于返回该错误号对应的错误消息。为了在 PL/SQL 应用程序中处理其他未预料到的 Oracle 错误，我们可以在例外处理部分的 WHEN OTHERS 子句后引用这两个函数，以取得相关的 Oracle 错误。

•  RAISE_APPLICATION_ERROR

           该过程用于在 PL/SQL 应用程序中自定义错误消息。注意，该过程只能在数据库端的子程序（过程、函数、包、触发器）中使用，而不能在匿名块和客户端的子程序中使用。

语法如下:

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

其中，error_number 用于定义错误号，该错误号必须是在 -20000到 -20999 之间的负整数； message 用于指定错误消息，并且长度不能超过 2048 字节；第三个参数为可选参数，如果设置为 TRUE，则该错误消息放在先前错误堆栈当中；如果设置为 FALSE (默认值)，则会替换先前所有错误。

 

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER

IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/

DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception

BEGIN
  account_status ('1-JUL-10', '9-JUL-10');   -- invoke procedure
EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));

END;
/