3.ORACLE异常错误处理

异常处理概念

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件，程序块的异常处理预定义错误和自定义错误，由于 PL/SQL 程序块一旦产生异常而没有指出如何处理时，程序就会自动终止整个程序运行。

异常类型

• 预定义异常
  ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 ORACLE 隐含自动抛出。类似JAVA内置的异常，如NullPointerException、ClassCastException等运行异常，由JAVA程序自动触发。

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反唯一约束条件
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-cursor	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到 ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO 没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	Program-error
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL 变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已存在的游标
ORA-6530	Access-INTO-null	试图为 null 对象的属性赋值
ORA-6531	Collection-is-null	试图将 Exists 以外的集合( collection)方法应用于一个 null pl/sql 表上或 varray 上
ORA-6532	Subscript-outside-limit	对嵌套或 varray 索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或 varray 索引得引用大于集合中元素的个数

 set serveroutput on

DECLARE
  --用来记录查询员工的薪资
  V_SALARY      EMPLOYEES.SALARY % TYPE;
  --员工ID查询条件
  V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID % TYPE := 1234;
  --员工名称查询条件
  V_NAME        EMPLOYEES.FIRST_NAME % TYPE := 'N';
BEGIN
  --查询编号为V_EMPLOYEE_ID员工薪资，如果不存在该员工则会触发NO_DATA_FOUND异常
  SELECT T.SALARY INTO V_SALARY FROM EMPLOYEES T WHERE T.EMPLOYEE_ID = V_EMPLOYEE_ID;
  
  --查询名称中包含V_NAME员工薪资，可能会返回多行数据，触发TOO_MANY_ROWS异常，也可能触发NO_DATA_FOUND异常
  SELECT T.SALARY INTO V_SALARY FROM EMPLOYEES T WHERE T.FIRST_NAME LIKE '%' || V_NAME || '%';
EXCEPTION
  --相当于JAVA的try-catch语句，针对每个不同的异常做处理
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('找不到员工编号为' || V_EMPLOYEE_ID || '数据');
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('员工名称中包含' || V_NAME || '存在多条数据');
    --OTHERS为其他为指定异常，类似JAVA的最大异常Exception
   WHEN OTHERS THEN
    dbms_output.put_line('其他异常');  
END;
/

执行结果为：

如果不用EXCEPTION块来处理异常，则自动终止程序异常，抛出错误编号为ORA-01403异常，可以通过以上表格找打对应的异常信息。

• 非预定义异常
  即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 隐含自动抛出，用于处理预定义异常所不能处理的Oracle错误。
  
  非预定义异常用于处理与ORACLE预定义异常无关的Oracle错误。
  
  由于预定义异常只是与一部分Oracle错误相连的异常，所以如果要处理没有与预定义异常对应的Oracle错误时，则需要为这些Oracle错误声明相应的非预定义异常。声明这样的异常需要使用exception_init编译指令。使用步骤如下：

1. 在 PL/SQL 块的定义部分定义异常情况，格式为：<异常情况> EXCEPTION;
2. 将非预定义与标准的 ORACLE 预定义异常联系起来，使用 PRAGMA EXCEPTION_INIT 语句，格式为：PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>)，异常情况为自定义异常说明信息，错误代码为ORACLE预定义异常错误号;
3. 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理，当ORACLE抛出预定义异常时，会转为抛出与其相关联的非预定义异常。

 set serveroutput on
DECLARE
  --非预定义异常处理违反唯一约束条件
  DUP_INDEX_EXCEPTION EXCEPTION;
  --ORACLE预定义异常错误号为-00001，将非预定义异常和预定义异常关联起来
  PRAGMA EXCEPTION_INIT(DUP_INDEX_EXCEPTION, -00001);
BEGIN
  --由于数据表中已经存在了主键为100的数据，此时在插入会违反主键的唯一约束条件
  INSERT INTO EMPLOYEES SELECT * FROM EMPLOYEES T WHERE T.DEPARTMENT_ID = 100;
EXCEPTION
  --当ORACLE抛出预定义的异常时，转为抛出与之相关联的非预定异常
  WHEN DUP_INDEX_EXCEPTION THEN
    dbms_output.put_line('删除失败，违反唯一约束条件');
END;
/

执行结果为：

• 用户定义异常
  程序执行过程中，出现编程人员认为的非正常情况（可能是数据异常）。自定义异常与Oracle错误没有任何关联，由开发人员为特定情况所定义的异常，需要显式抛出(raise)。
  
  raise抛出异常的三种方法：

1. Raise exception:用于抛出当前程序中定义的异常或在 standard 中的系统异常。
2. Raise package.exception:用于抛出有一些异常是定义在非标准包中的，如UTL_FILE，DBMS_SQL以及程序员创建的包中异常
3. Raise:不带任何参数，这种情况只出现在希望将当前的异常传到外部程序时，一般用在EXCEPTION块。

set serveroutput on
DECLARE
  --自定义异常，用于更新记录数为0时手动抛出
  NO_ROWS_EXCEPTION EXCEPTION;
BEGIN
  --数据表中不存在编号为1234的数据，此时更新返回的更新记录数为0的，但执行此语句时ORACLE是不会抛出异常的
  UPDATE EMPLOYEES T SET T.FIRST_NAME = 'TEST_NAME' WHERE T.DEPARTMENT_ID = 1234;
  
  IF SQL%NOTFOUND THEN
    --没有相应的更新记录，则手动抛出异常
    RAISE NO_ROWS_EXCEPTION;
  END IF;
EXCEPTION
  --当ORACLE抛出预定义的异常时，转为抛出与之相关联的非预定异常
  WHEN NO_ROWS_EXCEPTION THEN
    dbms_output.put_line('更新失败，不存在待更新的记录');
    --其他异常则往外抛出
  WHEN OTHERS THEN RAISE;
END;
/

执行结果为：