《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第九部分——异常）

本文内容：

1. 最简单的异常处理模块（使用OTHERS关键字）

2. 好的编程习惯是使用特定的异常处理程序而不是用OTHERS捕捉所有异常

3. 用户自定义异常（默认编号为1）

4. 若使用两步声明过程可以声明一个异常并将其映射至一个编号

 

 

1. 最简单的异常处理模块（使用OTHERS关键字）

SQL> edit
Wrote file afiedt.buf

  1  -- Basic error message.
  2  DECLARE
  3    lv_letter VARCHAR2(1);
  4    lv_phrase VARCHAR2(2) := 'AB';
  5  BEGIN
  6    lv_letter := lv_phrase;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      dbms_output.put_line('Error: '||CHR(10)||SQLERRM);
 10* END;
SQL> /
Error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small

2. 好的编程习惯是使用特定的异常处理程序而不是用OTHERS捕捉所有异常

SQL> edit
Wrote file afiedt.buf

  1  -- Basic error message with a named error message.
  2  DECLARE
  3    lv_letter VARCHAR2(1);
  4    lv_phrase VARCHAR2(2) := 'AB';
  5  BEGIN
  6    lv_letter := lv_phrase;
  7  EXCEPTION
  8    WHEN NO_DATA_FOUND THEN -- Specific error handler.
  9      dbms_output.put_line('Captured NO DATA FOUND ERROR: '||CHR(10)||SQLERRM);
 10    WHEN VALUE_ERROR THEN -- Specific error handler.
 11      dbms_output.put_line('Captured Value Error: '||CHR(10)||SQLERRM);
 12    WHEN OTHERS THEN -- General error handler.
 13      dbms_output.put_line('Other Error: '||CHR(10)||SQLERRM);
 14* END;
SQL> /
Captured Value Error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

PL/SQL procedure successfully completed.

3. 用户自定义异常（默认编号为1）

SQL> edit
Wrote file afiedt.buf

  1  -- ------------------------------------------------------------
  2  --   User-Defined Exceptions
  3  -- ------------------------------------------------------------
  4  -- Show a user-defined exception in an anonymous block.
  5  DECLARE
  6    lv_error EXCEPTION;
  7  BEGIN
  8    RAISE lv_error;
  9    dbms_output.put_line('Can''t get here.');
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      IF SQLCODE = 1 THEN
 13        dbms_output.put_line('This is ['||SQLERRM||']');
 14      END IF;
 15* END;
 16  /
This is [User-Defined Exception]

PL/SQL procedure successfully completed.

4. 若使用两步声明过程可以声明一个异常并将其映射至一个编号

SQL> edit
Wrote file afiedt.buf

  1  -- Show a user-defined exception with a customized exception.
  2  DECLARE
  3    lv_sys_context VARCHAR2(20);
  4    lv_error EXCEPTION;
  5    PRAGMA EXCEPTION_INIT(lv_error,-2003);
  6  BEGIN
  7    lv_sys_context := SYS_CONTEXT('USERENV','PROXY_PUSHER');
  8    RAISE lv_error;
  9    dbms_output.put_line('Can''t get here.');
 10  EXCEPTION
 11    WHEN lv_error THEN
 12      dbms_output.put_line('This is ['||SQLERRM||']');
 13* END;
SQL> /
This is [ORA-02003: invalid USERENV parameter]

PL/SQL procedure successfully completed.

5. 动态的用户自定义异常

SQL> edit
Wrote file afiedt.buf

  1  -- Show a user-defined exception with a raised application error.
  2  DECLARE
  3    lv_error EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(lv_error,-20001);
  5  BEGIN
  6    RAISE_APPLICATION_ERROR(-20001,'A less original message.');
  7  EXCEPTION
  8    WHEN lv_error THEN
  9      dbms_output.put_line('['||SQLERRM||']');
 10* END;
SQL> /
[ORA-20001: A less original message.]

PL/SQL procedure successfully completed.