《Oracle PL/SQL开发指南》学习笔记30——源码调试——错误管理（第二部分，用户定义的异常（User-Defined Exceptions））

 

用户定义的异常（User-Defined Exceptions）

You can declare user-defined exceptions in the following three ways. This section explains all  three and shows you how to implement and throw them.  

 

1. Declare an EXCEPTION variable in the declaration block, which you can throw by using  the RAISE statement. 
2. Declare an EXCEPTION variable and map it to a standard Oracle exception with a  PRAGMA (or precompiler) instruction in the declaration block. This type of error occurs  when the code generates the standard Oracle exception.
3. Use the raise_application_error function to create a dynamic exception. This  technique doesn’t require you to declare an EXCEPTION variable. The function lets you  map a user-defined error code to a message. You can call the raise_application_  error function in the execution or exception blocks. Calling the function throws a  dynamic exception. It’s important to note that you must use an integer in the range of  –20000 to –20999 as the error number.

1. 声明用户定义的异常

SQL> DECLARE
  2    e EXCEPTION;
  3  BEGIN
  4    RAISE e;
  5    dbms_output.put_line('Can''t get here.');
  6  EXCEPTION
  7    WHEN OTHERS THEN  /* Catch all exceptions. */
  8      /* Check user-defined exception. */
  9      IF SQLCODE = 1 THEN
 10        dbms_output.put_line('This is a ['||SQLERRM||'].');
 11      END IF;
 12  END;
 13  /
This is a [User-Defined Exception].

PL/SQL 过程已成功完成。

 2. 使用PRAGMA预编译指令映射错误代码

原型：

PRAGMA EXCEPTION_INIT(locally_declared_exception, error_code);

示例：

SQL> DECLARE
  2    lv_a VARCHAR2(20);
  3    invalid_userenv_parameter EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(invalid_userenv_parameter,-2003);
  5  BEGIN
  6    lv_a := SYS_CONTEXT('USERENV','PROXY_PUSHER');
  7  EXCEPTION
  8    WHEN invalid_userenv_parameter THEN
  9      dbms_output.put_line(SQLERRM);
 10  END;
 11  /
ORA-02003: 无效的 USERENV 参数

PL/SQL 过程已成功完成。

SQL> ED
已写入 file afiedt.buf

  1  DECLARE
  2    lv_a VARCHAR2(20);
  3    invalid_userenv_parameter EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(invalid_userenv_parameter,-2003);
  5  BEGIN
  6    lv_a := SYS_CONTEXT('USERENV','CON_NAME');
  7    dbms_output.put_line('用户环境变量CON_NAME（容器名）为：'||lv_a );
  8  EXCEPTION
  9    WHEN invalid_userenv_parameter THEN
 10      dbms_output.put_line(SQLERRM);
 11* END;
SQL> /
用户环境变量CON_NAME（容器名）为：CDB$ROOT

PL/SQL 过程已成功完成。

3. 将局部异常映射至用户自定义错误代码

SQL> DECLARE
  2    e  EXCEPTION;
  3    PRAGMA EXCEPTION_INIT(e,-20001);
  4  BEGIN
  5    RAISE e;
  6  EXCEPTION
  7    WHEN e THEN
  8    dbms_output.put_line(SQLERRM);
  9  END;
 10  /
ORA-20001:

PL/SQL 过程已成功完成。

4. 动态的用户定义的异常（RAISE_APPLICATION_ERROR）

原型：

RAISE_APPLICATION_ERROR(error_number, error_message [, keep_errors])

示例：

SQL> BEGIN
  2    RAISE_APPLICATION_ERROR(-20001,'A not too original message.');
  3  EXCEPTION
  4    WHEN others THEN
  5      dbms_output.put_line(SQLERRM);
  6  END;
  7  /
ORA-20001: A not too original message.

PL/SQL 过程已成功完成。

SQL> ed
已写入 file afiedt.buf

  1  BEGIN
  2    RAISE_APPLICATION_ERROR(-20001,'A not too original message.');
  3  --EXCEPTION
  4   -- WHEN others THEN
  5      --dbms_output.put_line(SQLERRM);
  6* END;
SQL>
SQL> /
BEGIN
*
第 1 行出现错误:
ORA-20001: A not too original message.
ORA-06512: 在 line 2

将PRAGMA与RAISE_APPLICATIOIN_ERROR结合使用：

SQL> DECLARE
  2     e EXCEPTION;
  3    PRAGMA EXCEPTION_INIT(e,-20001);
  4  BEGIN
  5    RAISE_APPLICATION_ERROR(-20001,'A less original message.');
  6  EXCEPTION
  7    WHEN e THEN
  8      dbms_output.put_line(SQLERRM);
  9  END;
 10  /
ORA-20001: A less original message.

PL/SQL 过程已成功完成。

SQL> DECLARE
  2    lv_a         VARCHAR2(1);
  3    lv_b VARCHAR2(2) := 'AB';
  4  BEGIN
  5    lv_a := lv_b;
  6    dbms_output.put_line('Never reaches this line.');
  7  EXCEPTION
  8    WHEN value_error THEN
  9      RAISE_APPLICATION_ERROR(-20001,'A specific message.');
 10    WHEN others THEN
 11      dbms_output.put_line(SQLERRM);
 12  END;
 13  /
DECLARE
*
第 1 行出现错误:
ORA-20001: A specific message.
ORA-06512: 在 line 9


SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    lv_a         VARCHAR2(1);
  3    lv_b VARCHAR2(2) := 'AB';
  4  BEGIN
  5    lv_a := lv_b;
  6    dbms_output.put_line('Never reaches this line.');
  7  EXCEPTION
  8    WHEN value_error THEN
  9      RAISE_APPLICATION_ERROR(-20001,'A specific message.',true);
 10    WHEN others THEN
 11      dbms_output.put_line(SQLERRM);
 12* END;
SQL> /
DECLARE
*
第 1 行出现错误:
ORA-20001: A specific message.
ORA-06512: 在 line 9
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小