文章标题

Oracle 异常处理

1. 异常处理概念

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

在Oracle中有三种异常错误：

1. 预定义 ( Predefined )错误： ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

2. 非预定义 ( Predefined )错误： 即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。

3. 用户定义(User_define) 错误： 程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

预定义的异常处理

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

exception
   when first_exception then  
   when second_exception then
    ... 
   when others then  
end;

异常处理可以按任意次序排列,但 others 必须放在最后.

来个例子：

--例1：更新指定员工工资，如工资小于1500，则加100；
declare
  eno emp.empno%type := &empno;
  esa emp.sal%type;
begin
  select sal into esa from emp where empno = eno;

  if esa <= 1500 then
    update emp set sal = sal + 100 where empno = eno;
    dbms_output.put_line('编码为' || eno || '员工工资已更新!');
  else
    dbms_output.put_line('编码为' || eno || '员工工资已经超过规定值!');
  end if;

exception
  when no_data_found then
    raise_application_error(-20006, '数据库中没有编码为' || eno || '的员工!');
  when too_many_rows then
    raise_application_error(-20007, '程序运行错误!请使用游标!');
  when others then
    raise_application_error(-20008, SQLCODE || '---' || SQLERRM);
end;

非预定义的异常处理

对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：

1. 在PL/SQL 块的定义部分定义异常情况：
    <异常情况>  exception;

2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用exception_init语句：
    pragma exception_init(<异常情况>, <错误代码>);

3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

举个例子：

--例2：删除指定部门的记录信息，以确保该部门没有员工。
declare
  dno emp.empno%type := &deptno;
  deptno_remaining exception;
  pragma exception_init(deptno_remaining, -20009);
begin
  delete from emp where empno = dno;
exception
  when deptno_remaining then
    raise_application_error(-20009, '违反数据完整性约束!');
  when others then
    raise_application_error(-20008, SQLCODE || '---' || SQLERRM);
end;

用户自定义的异常处理

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：

1. 在PL/SQL 块的定义部分定义异常情况：
    <异常情况>  exception;

2. raise <异常情况>;


3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

再来个例子：

--例3：更新指定员工工资，增加100；
declare
  eno emp.empno%type := &empno;
  no_result exception;
begin
  update emp set sal = sal + 100 where empno = eno;
  if sql%notfound then
    raise no_result;
  end if;

exception
  when no_result  then
    raise_application_error(-20010, '数据更新失败！！');
  when others then
    raise_application_error(-20008, SQLCODE || '---' || SQLERRM);
end;

2.拓展

raise_application_error()与dbms_output.put_line()一样是系统提供的包，可以重新定义异常错误信息，它为应用程序提供了一种与Oracle交互的方法。

语法：
    raise_application_error(error_number, error_message, [keep_errors]);

注解：
    1. error_number :  为 –20,000 到 –20,999 之间的参数；

    2. error_message: 是相应的提示信息(< 2048 字节)；

    3. keep_errors : 可选，
    如果keep_errors = true，则新错误将被添加到已经引发的错误列表中；
    如果keep_errors=false(缺省),则新错误将替换当前的错误列表；

举个例子：

-- 例4：创建一个函数get_salary, 该函数检索指定部门的工资总和，
--其中定义了-20991和-20992号错误，分别处理参数为空和非法部门代码两种错误：

create table errlog(
       errcode number,
       errtest varchar2(40)
);

create or replace function get_salary(dno number)
  return number as
  v_sal number;
begin
  if dno is null then
    raise_application_error(-20011, '输入的部门号为空！');
  elsif dno < 0 then
    raise_application_error(-20012, '无效的部门代码！');
  else
    select sum(sal) into v_sal from emp where deptno = dno;
    return v_sal;
  end if;
end;

declare
  v_deptno  emp.deptno%type := &deptno;
  v_salary  number;
  v_sqlcode errlog.errcode%type;
  v_sqltext errlog.errtest%type;

  Null_deptno exception;
  Invalid_deptno exception;

  pragma exception_init(Null_deptno, -20011);
  pragma exception_init(Invalid_deptno, -20012);
begin
  v_salary := get_salary(v_deptno);
  dbms_output.put_line(v_deptno || '号部门的总工资为：' || v_salary);

exception
  when Invalid_deptno then
    v_sqlcode := sqlcode;
    v_sqltext := sqlerrm;
    insert into errlog values (v_sqlcode, v_sqltext);
    commit;

  when Null_deptno then
    v_sqlcode := sqlcode;
    v_sqltext := sqlerrm;
    insert into errlog values (v_sqlcode, v_sqltext);
    commit;
end;

测试异常如图： 输入一个-10进去， 触发异常

再查询 errlog 表

将传入函数的参数设置为null， 触发异常

再次查询 errlog 表