主题：[推荐]Oracle数据库PL-SQL学习资料之五——异常处理

7 异常处理
PL/SQL异常用于检测运行时的错误，与Java异常类似，但是PL/SQL的异常不是对象，也没有定义自己的方法
异常在语句块的声明部分声明，在执行部分产生，在异常处理部分被处理

7、1 异常种类
异常有用户自定义异常和预定义异常

7、1、1 用户自定义异常
定义方法为：
declare e_newException EXCEPTION;

一般需要显式抛出和必要异常捕获，如：
set serveroutput on size 1000000;

DECLARE
  -- Exception to indicate an error condition
  e_TooManyStudents EXCEPTION;

  v_CountStudents NUMBER(3);
BEGIN
  SELECT count(*)
    INTO v_CountStudents
    FROM stu;

  /* Check */
  IF v_CountStudents >=3 THEN
    /* raise exception */
    RAISE e_TooManyStudents;
  END IF;

EXCEPTION
  when e_TooManyStudents then
    DBMS_OUTPUT.PUT_LINE('Too Many');
END;
/

7、1、2 预定义异常
对应各种常见的Oracle错误，这些异常标识符都在STANDARD包中被定义，程序可以直接使用，如：
DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  v_TempVar := 'ABCD';
EXCEPTION
  when VALUE_ERROR then
    DBMS_OUTPUT.PUT_LINE('Value Error');
END;
/

说明：
如果没有处理异常，会将异常传播到外层语句块，一旦执行控制权转移到最外层的调用环境，就无法再回到原来的语句块，如：
DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  v_TempVar := 'ABCD';
END;
/

7、1、3 EXCEPTION_INIT编译指示
该编译指示可以将用户自定义异常与预定义异常结合一齐，在预定义异常发生时将触发用户自定义异常，如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(1);
  e_ValueError EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_ValueError, -06502);
BEGIN
  select name into v_TempVar from stu where sid=2;
EXCEPTION
  WHEN e_ValueError then
    DBMS_OUTPUT.PUT_LINE('Value Error');
END;
/

注意：
编译指示必须在声明部分声明

7、2 异常处理
7、2、1 典型的异常处理方法
如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  --select name into v_TempVar from stu;
  --select name into v_TempVar from stu where sid=2;
  select name into v_TempVar from stu where sid=4;
EXCEPTION
  when VALUE_ERROR then
    DBMS_OUTPUT.PUT_LINE('Value Error');
  when NO_DATA_FOUND then
    DBMS_OUTPUT.PUT_LINE('No Data Found');
  when TOO_MANY_ROWS then
    DBMS_OUTPUT.PUT_LINE('Too Many Rows');
END;
/

也可以将多个异常放入一个处理单元，如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  select name into v_TempVar from stu where sid=4;
EXCEPTION
  when VALUE_ERROR or NO_DATA_FOUND then
    DBMS_OUTPUT.PUT_LINE('Value Error or No Data Found');
  when TOO_MANY_ROWS then
    DBMS_OUTPUT.PUT_LINE('Too Many Rows');
END;
/

但是不能使得某个异常具有多个处理单元，如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  select name into v_TempVar from stu;
EXCEPTION
  when VALUE_ERROR or NO_DATA_FOUND then
    DBMS_OUTPUT.PUT_LINE('Value Error or No Data Found');
  when VALUE_ERROR then
    DBMS_OUTPUT.PUT_LINE('Value Error');
END;
/

7、2、2 others异常处理单元
可以使用others异常处理单元捕获任何异常（包括用户自定义异常和预定义异常），通常放入最外层语句块或者异常处理的最后语句，如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  select name into v_TempVar from stu;
EXCEPTION
  when VALUE_ERROR then
    DBMS_OUTPUT.PUT_LINE('Value Error');
  when others then
    DBMS_OUTPUT.PUT_LINE('Other Error');
END;
/

为了在others异常处理单元有效的区分是什么异常，可以使用内置函数来观察，如：
set serveroutput on size 1000000;

DECLARE
  v_TempVar VARCHAR2(3);
BEGIN
  --select name into v_TempVar from stu;
  --select name into v_TempVar from stu where sid=2;
  select name into v_TempVar from stu where sid=4;
EXCEPTION
  when VALUE_ERROR then
    DBMS_OUTPUT.PUT_LINE('Value Error');
  when others then    
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

注意：
1）Oracle的错误信息最大长度为512字节，可能需要截取以限制长度
2）SQLCODE和SQLERRM函数都是过程化函数，因此不能直接在SQL语句中使用，必须先赋给变量才能使用
3）可以使用参数来单独调用SQLERRM函数，以显示数字对应的错误信息，如：
set serveroutput on size 1000000;

BEGIN
    DBMS_OUTPUT.PUT_LINE(SQLERRM());
    DBMS_OUTPUT.PUT_LINE(SQLERRM(0));
    DBMS_OUTPUT.PUT_LINE(SQLERRM(1));
    DBMS_OUTPUT.PUT_LINE(SQLERRM(100));
    DBMS_OUTPUT.PUT_LINE(SQLERRM(200));
    DBMS_OUTPUT.PUT_LINE(SQLERRM(-100));
    DBMS_OUTPUT.PUT_LINE(SQLERRM(-200));
END;
/

显示为：
ORA-0000: normal, successful completion
ORA-0000: normal, successful completion
User-Defined Exception
ORA-01403: 未找到数据
-200: non-ORACLE exception
ORA-00100: 未找到数据
ORA-00200: 无法创建控制文件

7、2、3 显式抛出错误
使用RAISE_APPLICATION_ERROR函数创建自己的错误消息，如：
set serveroutput on size 1000000;

BEGIN
    RAISE_APPLICATION_ERROR(-20000,'This ''s a error and ' || 'other info');
END;
/

说明：
1）输出的错误信息除了自定义错误信息外，还有一个固定的信息，为：ORA-06512，表示出错的所在行

2）比用户自定义异常更有描述性，一般的用户自定义异常只能返回相似的信息，如：
set serveroutput on size 1000000;

DECLARE
  e_ValueError EXCEPTION;
BEGIN
  RAISE e_ValueError;
EXCEPTION
  WHEN e_ValueError then
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
END;
/

3）RAISE_APPLICATION_ERROR函数的错误号位于-20000到-20999之间，错误信息必须少于512字节，第三个参数默认为假，表示新的错误将替换旧的错误，如为真，则表示新的错误添加到旧的错误上。如：
set serveroutput on size 1000000;

BEGIN
    RAISE_APPLICATION_ERROR(-20000,'This ''s a error and ' || 'other info');
EXCEPTION
  when others then    
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
    RAISE_APPLICATION_ERROR(-20000,'This ''s another error and ' || 'other info',true);
END;
/

4）抛出错误可以使用others异常捕获来处理（具体区分何种错误可以通过SQLCODE函数来进行），如：
set serveroutput on size 1000000;

BEGIN
    RAISE_APPLICATION_ERROR(-20000,'This ''s a error and ' || 'other info');
EXCEPTION
  when others then    
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
END;
/

7、3 异常传播
7、3、1 执行部分产生的异常传播
1）
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
BEGIN
  BEGIN
    RAISE e_A;
  EXCEPTION
    WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A inner');
  END;
EXCEPTION
  WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A outer');
END;
/

输出：e_A inner

2）
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
  e_B EXCEPTION;
BEGIN
  BEGIN
    RAISE e_B;
  EXCEPTION
    WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A inner');
  END;
EXCEPTION
  WHEN e_B then
    DBMS_OUTPUT.PUT_LINE('e_B outer');
END;
/

输出：e_B outer

3）
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
  e_B EXCEPTION;
  e_C EXCEPTION;
BEGIN
  BEGIN
    RAISE e_C;
  EXCEPTION
    WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A inner');
  END;
EXCEPTION
  WHEN e_B then
    DBMS_OUTPUT.PUT_LINE('e_B outer');
END;
/

报错

7、3、2 声明部分产生的异常传播
1）声明部分的异常通常会被立即传播到外层语句块，所以最外层的语句块中如果存在声明部分的异常，就会立刻终止代码运行
set serveroutput on size 1000000;

DECLARE
  v_number number(3):='abc';
BEGIN
  null;
EXCEPTION
  WHEN others then
    DBMS_OUTPUT.PUT_LINE('outer');
END;
/

报错

2）语句块自身的异常处理不能捕获自身声明部分的异常，只能捕获内部语句块的声明部分异常
set serveroutput on size 1000000;

DECLARE
  
BEGIN
  DECLARE
    v_number number(3):='abc';
  BEGIN
    null;
  EXCEPTION
    WHEN others then
      DBMS_OUTPUT.PUT_LINE('inner');
  END;
EXCEPTION
  WHEN others then
    DBMS_OUTPUT.PUT_LINE('outer');
END;
/

输出：outer

7、3、3 异常处理部分产生的异常传播
准则：任何一个时间只有一个异常

1）语句块自身的异常处理部分不能捕获自身异常处理部分产生的异常
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
  e_B EXCEPTION;
BEGIN
  RAISE e_A;
EXCEPTION
  WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A');
    RAISE e_B;
  WHEN e_B then
    DBMS_OUTPUT.PUT_LINE('e_B');
END;
/

报错

2）语句块的异常处理部分可以捕获内部语句块异常处理部分产生的异常
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
  e_B EXCEPTION;
begin
  BEGIN
    RAISE e_A;
  EXCEPTION
    WHEN e_A then
      DBMS_OUTPUT.PUT_LINE('e_A inner');
      RAISE e_B;
    WHEN e_B then
      DBMS_OUTPUT.PUT_LINE('e_B inner');
  END;
EXCEPTION
  WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A outer');
  WHEN e_B then
    DBMS_OUTPUT.PUT_LINE('e_B outer');
  END;
/

输出：
e_A inner
e_B outer

3）不加参数的RAISE语句将继续抛出现有异常
set serveroutput on size 1000000;

DECLARE
  e_A EXCEPTION;
  e_B EXCEPTION;
begin
  BEGIN
    RAISE e_A;
  EXCEPTION
    WHEN e_A then
      DBMS_OUTPUT.PUT_LINE('e_A inner');
      RAISE;
    WHEN e_B then
      DBMS_OUTPUT.PUT_LINE('e_B inner');
  END;
EXCEPTION
  WHEN e_A then
    DBMS_OUTPUT.PUT_LINE('e_A outer');
  WHEN e_B then
    DBMS_OUTPUT.PUT_LINE('e_B outer');
  END;
/

输出：
e_A inner
e_A outer

7、4 异常准则
7、4、1 异常具有作用域，对于用户自定义异常，一旦被传播出其作用域，则不能再用名称来使用，如：
BEGIN
  DECLARE
    e_UserDefinedException EXCEPTION;
  BEGIN
    RAISE e_UserDefinedException;
  END;
EXCEPTION
  WHEN e_UserDefinedException then
    DBMS_OUTPUT.PUT_LINE('e_UserDefinedException');
END;
/

相应的解决方法为：
1）此时在外层语句块中必须使用others来捕获之，如：
BEGIN
  DECLARE
    e_UserDefinedException EXCEPTION;
  BEGIN
    RAISE e_UserDefinedException;
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('others');
END;
/

2）在包中定义异常，以保证外层语句块可见，如：
CREATE OR REPLACE PACKAGE Globals AS
  e_UserDefinedException EXCEPTION;
END Globals;
/

BEGIN
  BEGIN
    RAISE Globals.e_UserDefinedException;
  END;
EXCEPTION
  WHEN Globals.e_UserDefinedException THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
END;
/

3）使用RAISE_APPLICATION_ERROR来代替，如：
BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20100,'Some error');
  END;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE());
    DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
  END;
/

7、4、2 避免未处理的异常，可以在最外层语句块中定义others处理器

7、4、3 指定错误的位置，如使用一些变量来标明程序运行的位置，可以在同一个异常处理单元中根据变量值做出判断，或者将每个语句放入自己的子语句块，分别处理各自的异常

7、4、4 错误信息的使用
1）DBMS_UTILITY.FORMAT_CALL_STACK函数
可以有效得到当前正在执行的程序，如：
set serveroutput on size 1000000;
set linesize 250

CREATE OR REPLACE PROCEDURE C AS v_CallStack VARCHAR2(2000);
BEGIN
  v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
  DBMS_OUTPUT.PUT_LINE(v_CallStack);
END C;
/

CREATE OR REPLACE PROCEDURE B AS
BEGIN
  C;
END B;
/

CREATE OR REPLACE PROCEDURE A AS
BEGIN
  B;
END A;
/

exec A;

输出为：
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
66C960CC         3  procedure SYS.C
66C3979C         3  procedure SYS.B
66C355A0         3  procedure SYS.A
66C31A7C         1  anonymous block

2）DBMS_UTILITY.FORMAT_ERROR_STACK函数
可以得到当前错误的序号，如：
set serveroutput on size 1000000;
DECLARE
  v_TempVar number(3);
BEGIN
  BEGIN
    v_TempVar:='abc';
  END;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/