Oracle 学习笔记12 —— 异常处理

 　　Oracle使用异常来处理，PL/SQL执行时发生的错误，这些异常都可以包括在PL/SQL程序的exception块中。Oracle提供了许多的内置的异常，用户也可以根据自己的需要定义异常。Oracle的异常可以分为3类：

　　1> 预定义异常 Oracle中内置了大量异常。在PL/SQL中使用预定义的异常，以便检查用户代码的执行失败的原因。这些异常定义在Oracle的核心PL/SQL库中，用户可以在自己的PL/SQL异常处理部分使用名称对其进行标识。对这种异常情况的处理，用户无需在程序中定义，Oracle自己引发。

　　2> 非预定义异常 非预定义异常数据库本身不知道，不能控制的错误。例如，操作类型崩溃；Oracle服务器错误；网络或者机器I/O错误等。对这种异常情况的处理，需要用户在程序中定义，然后由Oracle自动引发。

　　３> 用户定义异常 违反业务逻辑时，开发人员明确定义并引发的异常。

1、预定义异常

当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐式地引发一个预定义异常错误。下面是常见的预定义异常。

系统预定义异常

系统定义异常	说明
ACCESS_INFO_NULL	试图为某个未初始化对象的属性赋值。
CASE_NOT_FOUND	在case语句中未包含相应相应的when字句，并且没有设置else语句。
COLLECTION_IS_NULL	集合元素未初始化
CURSOR_ALREADY_OPEN	试图打开一个已经打开的游标。游标在重新打开之前，必须关闭。
DUP_VAL_ON_INDEX	试图在一个唯一性索引的列中存储冗余值
INVALID_CURSOR	执行一个非法的游标操作，例如关闭一个未打开的游标。
INVALID_NUMBER	试图将一个字符串转换为一个无效的数字。
LOGIN_DENIED	试图使用无效的用户名和密码连接数据库
NO_DATA_FOUND	select into语句没有返回数据，或者试图访问嵌套表中语句被删除的元素或未初始化的元素。
NOT_LOGINED_ERROR	试图在没有连接数据的情况下访问数据的内容。
PROGRAM_ERROR	pl/sql内部问题，可能需要重装数据字典和pl/sql系统包。
ROW_TYPE_MISMATCH	主游标变量与PL/SQL变量的返回类型不同。
SELF_IS_NULL	使用对象类型时，在NULL对象上调用对象方法。
STORAGE_ERROR	PL/SQL程序使用完了内存或内存遭到破坏。
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或VARRAY的最大值。
SUBSCRIPT_OUTSIDE_LIMIT	试图使用非法索引号引用嵌套表或VARRAY中的元素。
SYS_INVALID_ROWID	字符串向ROWID转换时的错误。
TIMEOUT_ON_RESOURCE	Oracle在等待资源是超时。
TOO_MANY_ROWS	执行SELECT INTO语句时，结果集大于一行。
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据。
ZERO_DIVIDE	除数为0.

SQL> set serveroutput on
SQL> begin
  2    insert into emp(empno, ename,job,sal,deptno)
  3    values(7369, 'ATG', 'Tim', 1500, 20);
  4  exception
  5    when DUP_VAL_ON_INDEX then
  6       dbms_output.put_line('捕获DUP_VAL_ON_INDEX 异常');
  7       dbms_output.put_line('该主键值已经存在');
  8  end;
  9  /
捕获DUP_VAL_ON_INDEX 异常
该主键值已经存在

PL/SQL 过程已成功完成。

上边的例子试图使用已经存在的主键值向emp表添加新记录，这会因为违法主键约束而发生错误。

SQL> set serveroutput on
SQL> declare
  2    emp_row emp%rowtype;
  3  begin
  4    select *
  5    into emp_row
  6    from emp
  7    where deptno = 10;
  8  exception
  9    when others then
 10    dbms_output.put_line('异常错误(' || SQLCODE || ')');
 11    dbms_output.put_line(SQLERRM);
 12  end;
 13  /
异常错误(-1422)
ORA-01422: 实际返回的行数超出请求的行数

PL/SQL 过程已成功完成。

2、非预定义异常

在一个异常产生、被捕获并处理之前，它必须被定义。Oracle定义了几千个异常，绝大多数只有错误编号和相关描述，仅仅命名少量最常用的异常，即系统定义异常。


除此之外的绝大多数异常都未命名，这些异常就是非预定义异常，它们需要程序员对其命名。当然，只使用错误码也可以完成异常的处理，但是这种异常处理会使代码可读性非常差。为非预定义异常命名时，需要使用 pragma exception_init 语句为错误号关联一个名称，随后就可以向系统预定义异常一样进行处理。exception_init是编译时运行的一个函数，它只能出现在代码的声明部分，而异常名必须在此之前被定义。下边为-2292 关联了一个名称：

SQL> set serveroutput on
SQL> declare
  2    invalid_company_id exception;
  3    fk_delete_exception exception;
  4    pragma exception_init(fk_delete_exception, -2292);
  5  begin
  6    delete from dept
  7    where dname='SALES';
  8  exception
  9    when fk_delete_exception then
 10    dbms_output.put_line('该项目存在于另一个列表中。');
 11  end;
 12  /
该项目存在于另一个列表中。

PL/SQL 过程已成功完成。

在上面的例子中，由于要删除的部门仍在在emp表中引用，所以提示外键引用。

3、用户定义异常
系统预定义和非预定义异常都是有oracle判断的错误，在实际的应用中，开发人员可以根据具体的业务规则自定义异常。

SQL> set serveroutput on
SQL> declare
  2    salary_error exception; -- 定义薪金异常
  3    var_sal emp.sal%type;
  4  begin
  5
  6    select sal into var_sal from emp
  7    where empno = 7369;
  8
  9    if var_sal <= 800 then
 10      raise salary_error;  -- 抛出异常
 11    end if;
 12  exception
 13    when salary_error then
 14      dbms_output.put_line('穷人');
 15  end;
 16  /
穷人

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> declare
  2    var_comm number;
  3  begin
  4    select comm
  5    into var_comm
  6    from emp
  7    where ename='TURNER';
  8
  9    if var_comm = 0 then
 10      raise zero_divide; -- 抛出系统异常
 11    end if;
 12  exception
 13
 14    when zero_divide then
 15      dbms_output.put_line('补贴为0！');
 16  end;
 17  /
补贴为0！

PL/SQL 过程已成功完成。