PL/SQL错误处理(原创)

Exception概述

Exception是一种PL/SQL标识符，当运行的PL/SQL块出现错误或警告，则会触发异常处理。为了提高程序的健壮性，可以在PL/SQL块中引入异常处理部分，进行捕捉异常，并根据异常出现的情况进行相应的处理。
ORACLE异常分为两种类型：系统异常、自定义异常。其中系统异常又分为：预定义异常和非预定义异常。
预定义异常
ORACLE定义了他们的错误编号和异常名字，常见的预定义异常处理如下

非预定义异常
ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。
自定义异常
程序员从业务角度出发，制定的一些规则和限制。
异常处理
PL/SQL中，异常处理按个步骤进行：
定义异常

exception_name EXCEPTION;
抛出异常

RAISE exception_name
捕获及处理异常
EXCEPTION
??? WHEN e_name1 [OR e_name2 ... ] THEN
??????? statements;
??? WHEN e_name3 [OR e_name4 ... ] THEN
??????? statements;
??????? ......
??? WHEN OTHERS THEN
??????? statements;
END;

预定义异常处理示例??
一个整除的异常
SQL>? declare
? 2??? v_n1 number := 50;
? 3??? v_n2 number := 0 ;
? 4??? v_n3 number;
? 5? begin
? 6??? v_n3 := v_n1/v_n2;
? 7??? dbms_output.put_line('v_n3=' || v_n3);
? 8? exception
? 9??? when zero_divide then
?10????? dbms_output.put_line('v_n2 can' || '''' || 't be 0');
?11* end;

v_n2 can't be 0
PL/SQL procedure successfully completed.

VALUE_ERROR(错误号ORA-06502)

SQL> declare
? 2??? v_ename varchar2(3);
? 3? begin
? 4??? select ename into v_ename from emp where empno = &eno;
? 5??? dbms_output.put_line(v_ename);
? 6? exception
? 7??? when value_error then
? 8????? dbms_output.put_line('variable datatype length is small');
? 9? end;
?10? /
Enter value for eno: 7788
old?? 4:?? select ename into v_ename from emp where empno = &eno;
new?? 4:?? select ename into v_ename from emp where empno = 7788;
variable datatype length is small
PL/SQL procedure successfully completed.
TOO_MANY_ROWS(对应Oracle错误号ORA-01422)
SQL>declare
? 2??? v_ename emp.ename%type;
? 3? begin
? 4??? select ename into v_ename from emp where deptno = &deptno;
? 5??? dbms_output.put_line(v_ename);
? 6? exception
? 7??? when too_many_rows then
? 8????? dbms_output.put_line('Too many rows are returned');
? 9? end;
?10? /
Enter value for deptno: 30
old?? 4:?? select ename into v_ename from emp where deptno = &deptno;
new?? 4:?? select ename into v_ename from emp where deptno = 30;
Too many rows are returned
PL/SQL procedure successfully completed.
非预定义异常示例

非预定义异常使用的基本过程如下
a.定义一个异常名
b.将异常名与异常编号相关联
c.在异常处理部分捕捉并处理异常

SQL> delete from dept where deptno = 10;
delete from dept where deptno = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
SQL> declare
? 2??? e_deptid exception;?????? --定义异常
? 3??? pragma exception_init(e_deptid,-2292);?????? --将异常和错误号关联
? 4? begin
? 5??? delete from dept where deptno = 10;
? 6? exception
? 7??? when e_deptid then???????? --捕获异常
? 8????? dbms_output.put_line('There is record at sub table');
? 9? end;
?10? /
There is record at sub table
PL/SQL procedure successfully completed.
自定义异常示例
自定义异常与Oracle错误没有任何关系，由开发人员为特定情况所定义的例外。下面的例子中，通过自定义异常，当雇员编号不存在时，PL/SQL代码能够给出适当的提示
对于自定义的异常处理需要显示的触发，其步骤如下
a.定义异常(在declare部分进行定义)
b.显示触发异常(在执行BEGIN部分触发异常，使用RAISE语句)
c.引用异常(在EXCEPTION部分捕捉并处理异常)
SQL> update emp set deptno=20 where empno=1111;
0 rows updated.
SQL> declare
? 2??? e_integrity exception;
? pragma exception_init(e_integrity,-2291);
? 4??? update emp set deptno = &dno where empno = &eno;
? e_no_employee exception;
begin
? update emp set deptno = &dno where empno = &eno;
? if sql%notfound then
??? raise e_no_employee;
? end if;
?10? exception
?11??? when e_integrity then
??? dbms_output.put_line('The dept does not exists');
? when e_no_employee then
??? dbms_output.put_line('The employess does not exists');
?15? end;
?16? /
Enter value for dno: 20
Enter value for eno: 1111
old?? 6:?? update emp set deptno = &dno where empno = &eno;
new?? 6:?? update emp set deptno = 20 where empno = 1111;
The employess does not exists
PL/SQL procedure successfully completed.
工资如果少于1500，则抛出异常
SQL> declare
? 2??? v_empno emp.empno%type;
? 3??? v_sal?? emp.sal%type;
? 4??? e_sal exception;
? 5? begin
? 6??? v_empno := &empno;
? 7??? v_sal?? := &sal;
? 8??? insert into emp(empno,sal) values(v_empno,v_sal);
? 9??? if v_sal < 1500 then
?10????? raise e_sal;
?11??? end if;
?12? exception
?13??? when e_sal then
?14????? rollback;
?15????? dbms_output.put_line('Salary must be more then 1500');
?16? end;
?17? /
Enter value for empno: 8888
old?? 6:?? v_empno := &empno;
new?? 6:?? v_empno := 8888;
Enter value for sal: 1234
old?? 7:?? v_sal?? := &sal;
new?? 7:?? v_sal?? := 1234;
Salary must be more then 1500
PL/SQL procedure successfully completed.
使用异常函数处理异常??????????

SQLCODE与SQLERRM

SQLCODE与SQLERRM为异常处理函数。函数SQLCODE用于取得Oracle错误号，函数SQLERRM用于取得与错误号对应的相关错误消息
SQL> declare
? 2??? v_ename emp.ename%type;
? 3? begin
? 4??? select ename into v_ename from emp
? 5??? where sal = &sal;
? 6??? dbms_output.put_line('Employee Name:' || v_ename);
? 7? exception
? 8??? when no_data_found then
? 9????? dbms_output.put_line('The employee does not exists');
?10??? when others then
?11????? dbms_output.put_line('Error No:' || SQLCODE);
?12????? dbms_output.put_line(SQLERRM);
?13? end;
?14? /
Enter value for sal: 1250
old?? 5:?? where sal = &sal;
new?? 5:?? where sal = 1250;
Error No:-1422
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
RAISE_APPLICATION_ERROR
调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。该函数用于在PL/SQL中定义错误消息，且只能在数据库端的子程序中使用(存储过程、函数、包、触发器)，不能在匿名块和客户端的子程序中使用
使用方法
RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
该函数内的错误代码和内容，都是用用户自定义
error_number：用于定义错误号，且错误号从-20000 到-20999 之间，以避免与ORACLE 的任何错误代码发生冲突。
message：用于指定错误消息，且消息长度不能超过k，超出部分将被截取
可选参数true,false:默认值为false，会替换先前的所有错误。当设置为true，则该错误会被放在先前错误堆栈中。
SQL> create or replace procedure raise_comm
? 2? (v_no emp.empno%type,v_comm out emp.comm%type)
? 3? as
? 4? begin
? 5??? select comm into v_comm from emp where empno = v_no;
? 6??? if v_comm is null then
? 7????? raise_application_error(-20001,'This employee has no comm');
? 8??? end if;
? 9? exception
?10??? when no_data_found then
??? dbms_output.put_line('The employee does not exists');
?12? end;
?13? /
Procedure created.
SQL> var g_sal number;
SQL> exec raise_comm(7788,:g_sal);
BEGIN raise_comm(7788,:g_sal); END;
*
ERROR at line 1:
ORA-20001: This employee has no comm
ORA-06512: at "SCOTT.RAISE_COMM", line 7
ORA-06512: at line 1
SQL> exec raise_comm(7499,:g_sal);
PL/SQL procedure successfully completed.
SQL> print g_sal;
???? G_SAL
----------
?????? 300
PL/SQL编译警告
PL/SQL警告的分类
SEVERE: 用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题.
PERFORMANCE: 用于检查可能引起性能问题,如在INSERT操作是为NUMBER列提供了VARCHAR2类型数据.
INFORMATIONAL: 用于检查程序中的死代码.
ALL: 用于检查所有警告.
控制PL/SQL警告消息
通过设置初始化参数PLSQL_WARNINGS来启用在编译PL/SQL子程序时发出警告消息，缺省为DISABLE:ALL
设置警告消息时有如下不同的范围
系统级别
会话级别
ALTER PROCEDURE????? ---只针对设置的过程有效
将特定的消息号设置为错误,也可以激活或禁止特定消息号
SQL> show parameter plsql%ings;
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings?????????????????????? string????? DISABLE:ALL??????
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'
SQL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> alter session set plsql_warnings='enable:(5001,5002)';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
PL/SQL编译告警示例
检测死代码
在下面的代码中，ELSE子句永远不会执行,应该避免出现类似的死代码.从Oracle 10g开始,在编写PL/SQL子程序之前开发人员可以激活警告检查.

SQL> alter session set plsql_warnings='enable:informational';
Session altered.

SQL> show parameter plsql_warnings
NAME???????????????????????????????? TYPE????????????????????????????? VALUE
------------------------------------ --------------------------------- ------------------------------
plsql_warnings?????????????????????? string??????????????????????????? ENABLE:INFORMATIONAL, DISABLE:
?????????????????????????????????????????????????????????????????????? PERFORMANCE, DISABLE:SEVERE
SQL> create or replace procedure dead_code
? 2? as
? 3? x number := 10;
? 4? begin
? 5??? if x > 10 then
? 6????? x := 1;
? 7??? else
? 8????? x := 2;????? --死代码
? 9??? end if;
?10? end ;
?11? /
SP2-0804: Procedure created with compilation warnings
检测引起性能问题的代码
SQL>? alter session set plsql_warnings='enable:performance';
Session altered.
SQL>? create or replace procedure update_sal
? 2?? (no number,salary varchar2)
? 3?? as
? 4?? begin
? 5???? update emp set sal=salary where empno=no;
? 6?? end;
? 7?? /
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE UPDATE_SAL:
LINE/COL ERROR
-------- ---------------------------------------------------------------
5/23???? PLW-07202: bind type would result in conversion away from column? type

参考至:http://blog.csdn.net/robinson_0612/article/details/6080119
????????????? http://www.cnblogs.com/soundcode/archive/2012/01/10/2318385.html

????????????? http://blog.csdn.net/endless_horizen/article/details/6563034

????????????? http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/initparams165.htm

本文原创，转载请注明出处 、作者

如有错误，欢迎指正

邮箱:[email protected]