Oracle 存储过程异常处理

Oracle提供了三种异常处理方式：

１：预定义异常        用于处理常见的Oracle错误
２：非预定义异常     用于处理预定义异常所不能处理的Oracle错误
３：自定义异常        用于处理于Oracle错误无关的其他情况

语法：
EXCEPTION
  WHEN exception_Name THEN  --exception_Name为异常的名字
    　  statement1;
  WHEN OTHERS THEN
　　　statement1;

一、 处理预定义异常，系统预定的21种类型　　

命名的系统异常	产生原因
access_into_null	未定义对象
case_not_found	case中若未包含相应的when，并且没有设置
collection_is_null	集合元素未初始化
curser_already_open	游标已经打开
dup_val_on_index	唯一索引对应的列上有重复的值
invalid_cursor	在不合法的游标上进行操作
invalid_number	内嵌的 sql 语句不能将字符转换为数字
no_data_found	使用 select into   未返回行，或应用索引表未初始化的
too_many_rows	执行 select into   时，结果集超过一行
zero_divide	除数为 0
subscript_beyond_count	元素下标超过嵌套表或varray的最大值
subscript_outside_limit	使用嵌套表或 varray   时，将下标指定为负数
value_error	赋值时，变量长度不足以容纳实际数据
login_denied	pl/sql 应用程序连接到   oracle 数据库时，提供了不正确的用户名或密码
not_logged_on	pl/sql   应用程序在没有连接 oralce 数据库的情况下访问数据
program_error	pl/sql   内部问题，可能需要重装数据字典＆ pl./sql系统包
rowtype_mismatch	主游标变量与 pl/sql 游标变量的返回类型不兼容
self_is_null	使用对象类型时，在 null   对象上调用对象方法
storage_error	运行 pl/sql   时，超出内存空间
sys_invalid_id	无效的 rowid 字符串
timeout_on_resource	oracle 在等待资源时超时

二、处理非预定义异常

 使用非预定义异常包括三步：
1、在定义部分定义异常名，
2、在异常和Oracle错误之间建立关联，
3、在异常处理部分捕捉并处理异常。 

当定义Oracle错误和异常之间的关联关系时，需要使用伪过程EXCEPTION_INIT。
1、首先的定义部分定义异常，
2、使用progma exception_init(exception_name,exception_number) 在异常和oracle错误之间建立关联，
     这时要求用户知道可能出现的错误号（异常函数sqlcode、sqlerrm和 raise_application_error）；
3、最终在异常处理部分捕捉并处理异常。

下面以更新特定雇员的部门号，并处理ORA-02291错误为例，说明使用非预定义异常的方法。示例如下：

DECLARE
  e_integrity EXCEPTION; --1、定义部分
  PRAGMA EXCEPTION_INIT (e_integrity, -2291);  --2、建立关联关系
BEGIN
  UPDATE emp SET deptno= &dno WHERE empno = &eno;
EXCEPTION
  WHEN e_integrity THEN  --3、捕捉处理
    DBMS_OUTPUT.PUT_LINE(‘该部门不存在’);
END;

三、处理自定义异常
预定义异常和非预定义异常都与Oracle错误有关，并且当出现Oracle错误时会隐含触发相应异常；
而自定义异常与Oracle错误没有任何关联，它是由开发人员为特定情况所定义的异常。 
当使用自定义异常时，
1、需要在定义部分（DECLARE）定义异常，
2、再执行部分（BEGIN）触发异常（使用RAISE语句），
3、在异常处理部分（EXCEPTION）捕捉并处理异常。 

CREATE TABLE errlog(
  Errcode NUMBER,
  Errtext CHAR(40));

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)
RETURN NUMBER 
AS
  v_sal NUMBER;
BEGIN
  IF p_deptno IS NULL THEN
    RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’);
  ELSIF p_deptno<0 THEN
    RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
  ELSE
    SELECT SUM(employees.salary) INTO v_sal FROM employees 
    WHERE employees.department_id=p_deptno;
    RETURN v_sal;
  END IF;
END;

DECLARE 
  V_salary NUMBER(7,2);
  V_sqlcode NUMBER;
  V_sqlerr VARCHAR2(512);
  Null_deptno EXCEPTION;
  Invalid_deptno EXCEPTION;
  PRAGMA EXCEPTION_INIT(null_deptno,-20991);
  PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
BEGIN
  V_salary :=get_salary(10);
  DBMS_OUTPUT.PUT_LINE('10号部门工资：' || TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(-10);
  EXCEPTION
    WHEN invalid_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) 
      VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
  END inner1;

  V_salary :=get_salary(20);
  DBMS_OUTPUT.PUT_LINE('部门号为20的工资为：'||TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(NULL);
  END inner2;

  V_salary := get_salary(30);
  DBMS_OUTPUT.PUT_LINE('部门号为30的工资为：'||TO_CHAR(V_salary));

  EXCEPTION
    WHEN null_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END outer;