PL/SQL异常处理

2016-12-24 08:34:53来源:作者:人点击

第七城市

PL/SQL异常处理

Exception概述

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

错误号

异常错误信息名称

说明

ORA-0001

Dup_val_on_index

违反了唯一性限制

ORA-0051

Timeout-on-resource

在等待资源时发生超时

ORA-0061

Transaction-backed-out

由于发生死锁事务被撤消

ORA-1001

Invalid-CURSOR

试图使用一个无效的游标

ORA-1012

Not-logged-on

没有连接到ORACLE

ORA-1017

Login-denied

无效的用户名/口令

ORA-1403

No_data_found

SELECT INTO没有找到数据

ORA-1422

Too_many_rows

SELECT INTO 返回多行

ORA-1476

Zero-divide

试图被零除

ORA-1722

Invalid-NUMBER

转换一个数字失败

ORA-6500

Storage-error

内存不够引发的内部错误

ORA-6501

Program-error

内部错误

ORA-6502

Value-error

转换或截断错误

ORA-6504

Rowtype-mismatch

宿主游标变量与 PL/SQL变量有不兼容行类型

ORA-6511

CURSOR-already-OPEN

试图打开一个已处于打开状态的游标

ORA-6530

Access-INTO-null

试图为null 对象的属性赋值

ORA-6531

Collection-is-null

试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上

ORA-6532

Subscript-outside-limit

对嵌套或varray索引得引用超出声明范围以外

ORA-6533

Subscript-beyond-count

对嵌套或varray 索引得引用大于集合中元素的个数.

非预定义异常
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> declare2 v_n1 number := 50;3 v_n2 number := 0 ;4 v_n3 number;5 begin6 v_n3 := v_n1/v_n2;7 dbms_output.put_line('v_n3=' || v_n3);8 exception9 when zero_divide then10 dbms_output.put_line('v_n2 can' || '''' || 't be 0');11* end;
v_n2 can't be 0PL/SQL procedure successfully completed.VALUE_ERROR(错误号ORA-06502)SQL> declare2 v_ename varchar2(3);3 begin4 select ename into v_ename from emp where empno = &eno;5 dbms_output.put_line(v_ename);6 exception7 when value_error then8 dbms_output.put_line('variable datatype length is small');9 end;10 /Enter value for eno: 7788old 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 smallPL/SQL procedure successfully completed.TOO_MANY_ROWS(对应Oracle错误号ORA-01422)SQL>declare2 v_ename emp.ename%type;3 begin4 select ename into v_ename from emp where deptno = &deptno;5 dbms_output.put_line(v_ename);6 exception7 when too_many_rows then8 dbms_output.put_line('Too many rows are returned');9 end;10 /Enter value for deptno: 30old 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 returnedPL/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 foundSQL> declare2 e_deptid exception; --定义异常3 pragma exception_init(e_deptid,-2292); --将异常和错误号关联4 begin5 delete from dept where deptno = 10;6 exception7 when e_deptid then --捕获异常8 dbms_output.put_line('There is record at sub table');9 end;10 /There is record at sub tablePL/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> declare2 e_integrity exception;pragma exception_init(e_integrity,-2291);4 update emp set deptno = &dno where empno = &eno;e_no_employee exception;beginupdate emp set deptno = &dno where empno = &eno;if sql%notfound thenraise e_no_employee;end if;10 exception11 when e_integrity thendbms_output.put_line('The dept does not exists');when e_no_employee thendbms_output.put_line('The employess does not exists');15 end;16 /Enter value for dno: 20Enter value for eno: 1111old 6: update emp set deptno = &dno where empno = &eno;new 6: update emp set deptno = 20 where empno = 1111;The employess does not existsPL/SQL procedure successfully completed.工资如果少于1500,则抛出异常SQL> declare2 v_empno emp.empno%type;3 v_sal emp.sal%type;4 e_sal exception;5 begin6 v_empno := &empno;7 v_sal := &sal;8 insert into emp(empno,sal) values(v_empno,v_sal);9 if v_sal < 1500 then10 raise e_sal;11 end if;12 exception13 when e_sal then14 rollback;15 dbms_output.put_line('Salary must be more then 1500');16 end;17 /Enter value for empno: 8888old 6: v_empno := &empno;new 6: v_empno := 8888;Enter value for sal: 1234old 7: v_sal := &sal;new 7: v_sal := 1234;Salary must be more then 1500PL/SQL procedure successfully completed.


使用异常函数处理异常

SQLCODE与SQLERRM

SQLCODE与SQLERRM为异常处理函数。函数SQLCODE用于取得Oracle错误号,函数SQLERRM用于取得与错误号对应的相关错误消息

SQL> declare2 v_ename emp.ename%type;3 begin4 select ename into v_ename from emp5 where sal = &sal;6 dbms_output.put_line('Employee Name:' || v_ename);7 exception8 when no_data_found then9 dbms_output.put_line('The employee does not exists');10 when others then11 dbms_output.put_line('Error No:' || SQLCODE);12 dbms_output.put_line(SQLERRM);13 end;14 /Enter value for sal: 1250old 5: where sal = &sal;new 5: where sal = 1250;Error No:-1422ORA-01422: exact fetch returns more than requested number of rowsPL/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_comm2 (v_no emp.empno%type,v_comm out emp.comm%type)3 as4 begin5 select comm into v_comm from emp where empno = v_no;6 if v_comm is null then7 raise_application_error(-20001,'This employee has no comm');8 end if;9 exception10 when no_data_found thendbms_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 commORA-06512: at "SCOTT.RAISE_COMM", line 7ORA-06512: at line 1SQL> 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:ALLSQL> 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_warningsNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------plsql_warnings string ENABLE:INFORMATIONAL, DISABLE:PERFORMANCE, DISABLE:SEVERESQL> create or replace procedure dead_code2 as3 x number := 10;4 begin5 if x > 10 then6 x := 1;7 else8 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_sal2 (no number,salary varchar2)3 as4 begin5 update emp set sal=salary where empno=no;6 end;7 /SP2-0804: Procedure created with compilation warningsSQL> show errorsErrors for PROCEDURE UPDATE_SAL:LINE/COL ERROR-------- ---------------------------------------------------------------------------------------------------5/23 PLW-07202: bind type would result in conversion away from column type

 

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台