Oracle PL/SQL中异常高级特性

共计 5062 个字符，预计需要花费 13 分钟才能阅读完成。

在 OraclePL/SQL 语句块中 exception 的异常处理部分是非常重要的组成部分，它决定了在 PL/SQL 语句块内部可执行部分在发生异常错误时，程序是友好地提示：程序遇到某些错误而无法执行，还是抛出一堆难以理解的 Oracle 内部错误码。

本文只介绍 3 中 PL/SQL 异常的三种高级形态，用于解决 Oracle 内置异常过少，很多时候不能够满足实际的使用需求。

1，RAISE_APPLICATION_ERROR

 - 是 Oracle 提供的一种特殊的内置过程，允许程序员为特定的程序创建有意义的错误消息，适用于用户自定义定义异常。

 - 语法结构

  RAISE_APPLICATION_ERROR (error_number,error_message); 或者

  RAISE_APPLICATION_ERROR (error_number,error_message,keep_errors)

  - error_number 是与特定错误消息关联的错误编号，Oracle 预留了 -20999 — -20000 专门提供给程序员自定义错误代码。

  - error_message 是错误消息文本，最多包含 2048 个字符。

  - keep_errors 是可选的 Boolean 参数，默认为 FALSE，如果为 TRUE，新抛出的错误会被添加到已抛出的错误列表中，这个错误列表称为错误栈，如果为 FALSE，新错误会替换已抛出的错误栈。

 - 适用于未命名的用户定义异常，负责把错误编号和错误消息关联，用户定义了异常，却没有定义该错误的名称

 - 使用 RAISE_APPLICATION_ERROR 过程，程序员能够遵循与 Oracle 一致的方式返回错误消息。

– 示例代码

declare
  v_id number := &p_id;
  v_name varchar2(20);
  v_sal number;
begin
  if v_id > 0 then
      select ename,sal into v_name,v_sal from emp where empno = v_id;
      dbms_output.put_line(chr(10)||v_name||’ ‘||v_sal);
  else
      raise_application_error (-20001,’Employee id can not be negative.’);
  end if;
exception
  when NO_DATA_FOUND then
      dbms_output.put_line(chr(10)||’There is no such employee id is ‘||v_id);
end;
/
Enter value for p_id: 40
old  2:    v_id number := &p_id;
new  2:    v_id number := 40;

There is no such employee id is 40

PL/SQL procedure successfully completed.
/
Enter value for p_id: -90
old  2:    v_id number := &p_id;
new  2:    v_id number := -90;
declare
*
ERROR at line 1:
ORA-20001: Employee id can not be negative.
ORA-06512: at line 11

- 示例解析：该 PL/SQL 代码会根据用户输入的员工 Id，查询员工的姓名和工资。当我们输入存在的员工编号时，程序能够正常返回结果；如果输入不存在 ID，则 select into 语句会抛出没有返回行，进而使程序进入异常处理部分（本部分为举例），程序同样执行成功；当输入一个负数时，if 条件语句就会进入到 raise_application_error 部分，由于可执行部分运行发生错误，执行焦点会立即转移到异常处理部分，而异常处理部分没有关于该异常的处理，所以程序报错，并返回到用户界面。

- 是哟个 raise_application_error，程序员可以使程序实现像 Oracle 系统产生的错误消息。

- 事实上，单纯使用 raise_application_error，因为没有异常的名称，如果要对其进行异常处理，只能够使用 others（下文有专门的介绍）。

2，EXCEPTION_INIT

 - 使用 EXCEPTION_INIT 编译指令，可以将用户自定义的 Oracle 错误编号和用户自定义的错误名称关联起来，相当于用户自定义错误和 RAISE_APPLICATION_ERROR 的结合体。

 - EXCEPTION_INIT 出现在语句块的声明部分：

  exception_name exception;

  pragma exception_init(exception_name,error_code)

- 考虑如下代码：

declare
  v_no number := &p_no;
begin
  delete from dept where deptno = v_no;
    dbms_output.put_line(chr(10)||’The department id is ‘||v_no||’ has been deleted’);
end;
/
Enter value for p_no: 20
old  2:    v_no number := &p_no;
new  2:    v_no number := 20;
declare
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated – child record found
ORA-06512: at line 4

- 由于违反外键约束，删除部门失败了。但是抛出的错误不是很好理解

- 我们可以使用 EXCEPTION_INIT 来对这个错误进行处理，首先我们得知道违反外键约束的这个 Oracle 错误代码“ORA-02292”

- 使用 EXCEPTION_INIT

declare
  v_no number := &p_no;
  e_dept_exist exception;
  pragma exception_init(e_dept_exist,-02292);
begin
  delete from dept where deptno = v_no;
    dbms_output.put_line(chr(10)||’The department id is ‘||v_no||’ has been deleted’);
exception
    when e_dept_exist then
        dbms_output.put_line(chr(10)||’There are some employees in this deptartment, if you want delete this deptartment ,please delete these employees in the department first.’);
end;
/
Enter value for p_no: 20
old  2:    v_no number := &p_no;
new  2:    v_no number := 20;

There are some employees in this deptartment, if you want delete this deptartment ,please delete these employees in the department first.

PL/SQL procedure successfully completed.

- 这下抛出的错误就容易理解多了。首先我们定义了一个名为 e_dept_exist 的异常，然后将这个异常与 Oracle 错误代码 -02292 进行关联。当程序执行报错时进入异常处理部分，在这里我们重新给这个错误定义了错误消息。

3，SQLCODE 和 SQLERRM

 - 在异常处理中，当异常的名称未知时（比如上面 1 中 RAISE_APPLICATION_ERROR），都可以使用 others 来进行异常的捕获处理；

 - 由于 others 所捕获的异常是未知的（也可以是已知的，但是在程序中没有将其枚举出来），因此需要使用 Oracle 提供的两个内置函数 SQLCODE、SQLERRM 来针对 others 的异常进行处理：

 - SQLCODE 会返回 Oracle 的错误编号

 - SQLERRM，返回错误的消息

- 示例 1，处理 Oracle 系统返回的错误：

declare
  v_no number := &p_no;
    error_code number;
    error_msg varchar2(500);
begin
  delete from dept where deptno = v_no;
    dbms_output.put_line(chr(10)||’The department id is ‘||v_no||’ has been deleted’);
exception
    when others then
        error_code := sqlcode;
        error_msg := sqlerrm;
        dbms_output.put_line(chr(10)||’Error code is: ‘||error_code);
        dbms_output.put_line(chr(10)||’Error message is: ‘||error_msg);
end;
Enter value for p_no: 10
old  2:    v_no number := &p_no;
new  2:    v_no number := 10;

Error code is: -2292

Error message is: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated – child record found

PL/SQL procedure successfully completed.

- 请注意 exception 异常处理部分，在该部分里面我们用到了声明部分定义的两个变量，error_code 用来存储 SQLCODE，error_msg 用来存储 SQLERRM。然后将两个变量值打印出来。

- 示例 2，处理用户自定义的异常：

declare
  v_id number := &p_id;
  v_name varchar2(20);
  v_sal number;
begin
  if v_id > 0 then
      select ename,sal into v_name,v_sal from emp where empno = v_id;
      dbms_output.put_line(chr(10)||v_name||’ ‘||v_sal);
  else
      raise_application_error (-20001,’Employee id can not be negative.’);
  end if;
exception
  when NO_DATA_FOUND then
      dbms_output.put_line(chr(10)||’There is no such employee id is ‘||v_id);
    when others then
        declare
            error_code number;
            error_msg varchar2(500);
        begin
            error_code := sqlcode;
            error_msg := sqlerrm;
            dbms_output.put_line(chr(10)||’Error code is: ‘||error_code);
            dbms_output.put_line(chr(10)||’Error message is: ‘||error_msg);
        end;
end;
/
Enter value for p_id: -90
old  2:    v_id number := &p_id;
new  2:    v_id number := -90;

Error code is: -20001

Error message is: ORA-20001: Employee id can not be negative.

PL/SQL procedure successfully completed.

– 在本代码中使用了 raise_application_error，由于单纯的使用 raise_application_error，只能使用 others 进行捕获。在异常处理部分，我们使用了一个 PL/SQL 语句块来处理这个错误，声明两个变量，并将 SQLCODE 和 SQLERRM 以字面值赋值的方法给这两个变量。

：