《Oracle PL/SQL开发指南》学习笔记30——源码调试——错误管理（第一部分，Error Management）

 

异常类型（编译时错误、运行时错误）

1. 编译时错误

Compilation Errors  Compilation errors are generally typing errors. The parsing of your PL/SQL text file into a set of  interpreted instructions, known as p-code, finds lexical errors. Lexical errors occur when you  misuse a delimiter, identifier, literal, or comment. You can misuse lexical units by

1. Forgetting a semicolon (the statement terminator)
2. Using only one delimiter when you should use two, such as failing to enclose a string  literal
3. Misspelling an identifier (reserved words and keywords)
4.  Commenting out a lexical value required by the parsing rules 

There are three general patterns for error messages: 

1. Prior line errors Point to an error on the prior statement line, which is generally a  missing statement terminator. 
2. Current line errors Point to the column of the error or one column after the error. The  difference generally means that the parser is looking for a missing lexical unit. 

Declaration errors Point to any failure in the declaration block, and generally have the  actual error line as the last line of the error message.

2. 运行时错误

异常块包含WHEN块，用于捕获特定或一般的错误，其原型为：

WHEN [predefined_exception | user_defined_exception | OTHERS] THEN
[RETURN | EXIT ];

Oracle在sys.standard包中以如下形式预定义错误：

CURSOR_ALREADY_OPEN exception;
  pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');

Oracle异常管理内置函数：

Function	Oracle Predefined Error	User-Defined Error
SQLCODE	Returns a negative integer for  standard Oracle exceptions, except  the NO_DATA_FOUND exception,  which returns a positive 100.	Returns a positive 1 if no EXCEPTION_INIT  PRAGMA is defined. If an EXCEPTION_INIT  PRAGMA is defined, it returns a valid number  in the range of –20001 to –20999.
SQLERRM	Returns the error code and  message of a standard Oracle  exception. SQLERRM is an  overloaded function that operates  only in the exception block.	Returns a 1 and a “User-Defined Exception”  message when the exception is thrown by  the RAISE statement. Returns a valid negative  integer in the qualified range and a text  message when the exception is thrown by  the raise_application_info function.

 1）VALUE_ERROR异常

SQL> DECLARE
  2    lv_a VARCHAR2(1);
  3    lv_b VARCHAR2(2) := 'AB';
  4  BEGIN
  5    lv_a := lv_b;
  6  EXCEPTION
  7    WHEN value_error THEN
  8      dbms_output.put_line(
  9        'You can''t put ['||lv_b||'] in a one character string.');
 10  END;
 11  /
You can't put [AB] in a one character string.

PL/SQL 过程已成功完成。

2）NO_DATA_FOUND异常

SQL> DECLARE
  2    lv_a  VARCHAR2(1);
  3  BEGIN
  4    DECLARE
  5      lv_b VARCHAR2(2);
  6    BEGIN
  7      SELECT 1 INTO lv_b
  8      FROM dual
  9      WHERE 1 = 2;
 10      lv_a := lv_b;
 11    EXCEPTION
 12      WHEN value_error THEN
 13        dbms_output.put_line(
 14         'You can''t put ['||lv_b||'] in a one character string.');
 15    END;
 16  EXCEPTION
 17    WHEN others THEN
 18      dbms_output.put_line(
 19        'Caught in outer block ['||SQLERRM||'].');
 20  END;
 21  /
Caught in outer block [ORA-01403: 未找到任何数据].

PL/SQL 过程已成功完成。

3）使用RAISE子句手动触发用户定义的异常

SQL> DECLARE
  2    lv_a  VARCHAR2(1);
  3    e     EXCEPTION;
  4  BEGIN
  5    DECLARE
  6      lv_b VARCHAR2(2) := 'AB';
  7    BEGIN
  8      RAISE e;
  9    EXCEPTION
 10      WHEN others THEN
 11        lv_a := lv_b;
 12        dbms_output.put_line('Never reaches this line.');
 13    END;
 14  EXCEPTION
 15    WHEN others THEN
 16      dbms_output.put_line(
 17        'Caught in outer block->'||dbms_utility.format_error_backtrace);
 18  END;
 19  /
Caught in outer block->ORA-06512: 在 line 11



PL/SQL 过程已成功完成。

4）声明块错误

异常块无法捕捉与其属于同一层级的声明块中发生的异常：

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    lv_a CHAR := '&input';
  3  BEGIN
  4    dbms_output.put_line('['||lv_a||']');
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      dbms_output.put_line('['||SQLERRM||']');
  8* END;
SQL> /
输入 input 的值:  aB
原值    2:   lv_a CHAR := '&input';
新值    2:   lv_a CHAR := 'aB';
DECLARE
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 line 2

可使用嵌套异常块捕捉内层声明块中的异常：

SQL> BEGIN
  2    DECLARE
  3      lv_a CHAR := '&input';
  4    BEGIN
  5      dbms_output.put_line('['||lv_a||']');
  6    END;
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      dbms_output.put_line('['||SQLERRM||']');
 10  END;
 11  /
输入 input 的值:  AB
原值    3:     lv_a CHAR := '&input';
新值    3:     lv_a CHAR := 'AB';
[ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小]

PL/SQL 过程已成功完成。

5）存储函数中的动态赋值可能导致异常发生

SQL> CREATE OR REPLACE FUNCTION runtime_error
  2  (lv_input  VARCHAR2) RETURN VARCHAR2 IS
  3    a VARCHAR2(1) := lv_input;
  4  BEGIN
  5    NULL;
  6  EXCEPTION
  7    WHEN others THEN
  8      dbms_output.put_line('Function error.');
  9  END;
 10  /

函数已创建。

SQL> SELECT runtime_error ('AB') FROM dual;
SELECT runtime_error ('AB') FROM dual
       *
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "C##STUDENT.RUNTIME_ERROR", line 3