Oracle PL/SQL中异常高级特性示例解析

pl/sql(procedural language/sql，过程语言/sql)是结合了oracel过程语言和结构化查询语言(sql)的一种扩展语言。

优点：

（1）pl/sql具有编程语言的特点，它能把一组sql语句放到一个模块中，使其更具模块化种序的特点。

（2）pl/sql可以采用过程性语言控制程序的结构。

（3）pl/sql有自动处理的异常处理机制。

（4）pl/sql程序块具有更好的可移植性，可移植到另一个oracle数据库中。

（5）pl/sql程序减少了网络的交互，有助于提高程序性能。

在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以字面值赋值的方法给这两个变量。

总结

以上所述是小编给大家介绍的oracle pl/sql中异常高级特性示例解析，希望对大家有所帮助