PL/SQL --> 异常处理(Exception)

矫情吗；* 2022-05-15 01:41 234阅读 0赞

\--================================

\-- PL/SQL --> 异常处理(Exception)

\--================================

Exception是一种PL/SQL标识符，当运行的PL/SQL块出现错误或警告，则会触发异常处理。为了提高程序的健壮性，可以在PL/SQL块中引

入异常处理部分，进行捕捉异常，并根据异常出现的情况进行相应的处理。

一、异常的类型

ORACLE异常分为两种类型：系统异常、自定义异常。

其中系统异常又分为：预定义异常和非预定义异常。

1.预定义异常

ORACLE定义了他们的错误编号和异常名字，常见的预定义异常处理Oracle常见的错误

NO\_DATA\_FOUND SELECT ... INTO ...时，没有找到数据

DUL\_VAL\_ON\_INDEX 试图在一个有惟一性约束的列上存储重复值

CURSOR\_ALREADY\_OPEN 试图打开一个已经打开的游标

TOO\_MANY\_ROWS SELECT ... INTO ...时，查询的结果是多值

ZERO\_DIVIDE 零被整除

2.非预定义异常

ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，

通过伪过程PRAGMA EXCEPTION\_INIT，将异常名与错误号关联起来。

3.自定义异常

程序员从我们业务角度出发，制定的一些规则和限制。

二、异常处理

PL/SQL中，异常处理按个步骤进行：

定义异常

抛出异常

捕获及处理异常

a.定义异常

exception\_name EXCEPTION;

b.抛出异常

RAISE exception\_name

c.捕获及处理异常

EXCEPTION

WHEN e\_name1 \[OR e\_name2 ... \] THEN

statements;

WHEN e\_name3 \[OR e\_name4 ... \] THEN

statements;

......

WHEN OTHERS THEN

statements;

END;

三、异常处理示例

1.预定义异常的例子

\--演示一个整除的异常

DECLARE

v\_n1 NUMBER:=50;

v\_n2 NUMBER:=0;

v\_n3 NUMBER;

BEGIN

v\_n3:=v\_n1/v\_n2;

DBMS\_OUTPUT.PUT\_LINE('v\_n3='||v\_n3);

EXCEPTION

WHEN ZERO\_DIVIDE THEN

DBMS\_OUTPUT.PUT\_LINE('v\_n2 don't is 0');

END;

\--演示VALUE\_ERROR(错误号ORA-06502)

scott@ORCL> 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)

scott@ORCL> declare

2 v\_ename emp.ename%type;

3 begin

4 select ename into v\_ename from emp where deptno=&dno;

5 exception

6 when too\_many\_rows then

7 dbms\_output.put\_line('Too many rows are returned');

8 end;

9 /

Enter value for dno: 10

old 4: select ename into v\_ename from emp where deptno=&dno;

new 4: select ename into v\_ename from emp where deptno=10;

Too many rows are returned

PL/SQL procedure successfully completed.

2.非预定义异常

非预定义异常使用的基本过程

a.定义一个异常名

b.将异常名与异常编号相关联

c.在异常处理部分捕捉并处理异常

DECLARE

e\_deptid EXCEPTION;

PRAGMA EXCEPTION\_INIT(e\_deptid,-2292);

BEGIN

DELETE FROM DEPT WHERE DEPTNO=10;

EXCEPTION

WHEN e\_deptid THEN

DBMS\_OUTPUT.PUT\_LINE('at sub table have record!');

END;

DECLARE

e\_emp EXCEPTION;

PRAGMA EXCEPTION\_INIT(e\_emp,-2291);

BEGIN

INSERT INTO EMP(empno,deptno) VALUES (7935,60);

EXCEPTION

WHEN e\_emp THEN

DBMS\_OUTPUT.PUT\_LINE('60 at table dept not exist');

END;

3.自定义异常

自定义异常与Oracle错误没有任何关系，有开发人员为特定情况所定义的例外

下面的例子中当输入empno为时，尽管该雇员编号不存在，但PL/SQL代码并未给出适当的提示

scott@ORCL> declare

2 e\_integrity exception;

3 pragma exception\_init(e\_integrity,-2291);

4 begin

5 update emp set deptno=&dno where empno=&eno;

6 exception

7 when e\_integrity then

8 dbms\_output.put\_line('The dept is not exists');

9 end;

10 /

Enter value for dno: 20

Enter value for eno: 1111

old 5: update emp set deptno=&dno where empno=&eno;

new 5: update emp set deptno=20 where empno=1111;

PL/SQL procedure successfully completed.

针对上述情况，可以使用自定义异常处理。

对于自定义的异常处理需要显示的触发，其步骤如下

a.定义异常(在declare部分进行定义)

b.显示触发异常(在执行BEGIN部分触发异常，使用RAISE语句)

c.引用异常(在EXCEPTION部分捕捉并处理异常)

\--对上面的例子，使用自定义异常来处理，代码如下：

scott@ORCL> declare

2 e\_integrity exception;

3 pragma exception\_init(e\_integrity,-2291);

4 e\_no\_employee exception;

5 begin

6 update emp set deptno=&dno where empno=&eno;

7 if sql%notfound then

8 raise e\_no\_employee;

9 end if;

10 exception

11 when e\_integrity then

12 dbms\_output.put\_line('The dept is not exists');

13 when e\_no\_employee then

14 dbms\_output.put\_line('The employee is not exists');

15 end;

16 /

Enter value for dno: 20

Enter value for eno: 1234

old 6: update emp set deptno=&dno where empno=&eno;

new 6: update emp set deptno=20 where empno=1234;

The employee is not exists

PL/SQL procedure successfully completed.

\--下面的例子中，如果插入的工资少于，就抛出异常

DECLARE

v\_sal emp.sal%TYPE;

v\_id emp.empno%TYPE;

e\_sal EXCEPTION; \--定义异常

BEGIN

v\_id:=&inputid;

v\_sal:=&inputsal;

INSERT INTO emp (empno,sal) VALUES (v\_id,v\_sal);

IF v\_sal<700 THEN

RAISE e\_sal; \--捕捉异常

END IF;

EXCEPTION \--处理异常

WHEN e\_sal THEN

ROLLBACK;

DBMS\_OUTPUT.PUT\_LINE('Sal must be more than 700');

END;

四、使用异常函数处理异常

1.SQLCODE与SQLERRM函数

函数SQLCODE用于取得Oracle错误号

函数SQLERRM用于取得与错误号对应的相关错误消息

scott@ORCL> get /u01/bk/scripts/sqlcode\_errm.sql

1 undef v\_sal

2 declare

3 v\_ename emp.ename%type;

4 begin

5 select ename into v\_ename from emp

6 where sal=&&v\_sal;

7 dbms\_output.put\_line('Employee Name:'||v\_ename);

8 exception

9 when no\_data\_found then

10 dbms\_output.put\_line('The employee is not exists for salary '||&v\_sal);

11 when others then

12 dbms\_output.put\_line('Error No:'||SQLCODE);

13 dbms\_output.put\_line(SQLERRM);

14\* end;

scott@ORCL> start /u01/bk/scripts/sqlcode\_errm.sql

Enter value for v\_sal: 1600

old 5: where sal=&&v\_sal;

new 5: where sal=1600;

old 9: dbms\_output.put\_line('The employee is not exists for salary '||&v\_sal);

new 9: dbms\_output.put\_line('The employee is not exists for salary '||1600);

Error No:-1422

ORA\-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed.

2.RAISE\_APPLICATION\_ERROR

是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)。

该函数用于在PL/SQL中定义错误消息，且只能在数据库端的子程序中使用(存储过程、函数、包、触发器)，不能在匿名块和客户端的

子程序中使用

使用方法

RAISE\_APPLICATION\_ERROR(error\_number,message\[,\{true | false\}\]);

该函数内的错误代码和内容，都是用用户自定义

error\_number：用于定义错误号，且错误号从\-20000 到\-20999 之间，以避免与ORACLE 的任何错误代码发生冲突。

message：用于指定错误消息，且消息长度不能超过k，超出部分将被截取

可选参数true,false:默认值为false，会替换先前的所有错误。当设置为true，则该错误会被放在先前错误堆栈中。

scott@ORCL> get /u01/bk/scripts/sp\_raise\_comm.sql

1 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,'It is no comm for this employee');

8 end if;

9 exception

10 when no\_data\_found then

11 dbms\_output.put\_line('The employee is not exist');

12\* end;

scott@ORCL> start /u01/bk/scripts/sp\_raise\_comm.sql

Procedure created.

scott@ORCL> variable g\_sal number;

scott@ORCL> call raise\_comm(7788,:g\_sal);

call raise\_comm(7788,:g\_sal)

ERROR at line 1:

ORA\-20001: It is no comm for this employee

ORA\-06512: at "SCOTT.RAISE\_COMM", line 7

scott@ORCL> call raise\_comm(7499,:g\_sal);

Call completed.

scott@ORCL> print g\_sal

G\_SAL

\----------

300

五、PL/SQL编译警告

1.PL/SQL警告的分裂

SEVERE:用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题.

PERFORMANCE:用于检查可能引起性能问题,如在INSERT操作是为NUMBER列提供了VARCHAR2类型数据.

INFORMATIONAL: 用于检查程序中的死代码.

ALL: 用于检查所有警告.

2.控制PL/SQL警告消息

通过设置初始化参数PLSQL\_WARNINGS来启用在编译PL/SQL子程序时发出警告消息，缺省为DISABLE:ALL

警告消息设置的级别

系统级别

会话级别

ALTER PROCEDURE

既可以激活或禁止所有警告类型,也可以激活或禁止特定消息号

scott@ORCL> show parameter plsql%ings;

NAME TYPE VALUE

\------------------------------------ ----------- ------------------------------

plsql\_warnings string DISABLE:ALL

scott@ORCL> ALTER SYSTEM SET PLSQL\_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'

scott@ORCL> ALTER SESSION SET PLSQL\_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'

scott@ORCL> ALTER PROCEDURE usp COMPILE PLSQL\_WARNINGS='ENABLE:PERFORMANCE';

scott@ORCL> ALTER SESSION SET PLSQL\_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';

3.演示PL/SQL编译告警

a.检测死代码

在下面的代码中，ELSE子句永远不会执行,应该避免出现类似的死代码.

从Oracle 10g开始,在编写PL/SQL子程序之前开发人员可以激活警告检查.

scott@ORCL> get /u01/bk/scripts/dead\_code.sql

1 CREATE OR REPLACE PROCEDURE dead\_code AS

2 x number := 10;

3 BEGIN

4 IF x>0 THEN

5 x:=1;

6 ELSE

7 x:=2; \--死代码

8 END IF;

9\* END dead\_code;

scott@ORCL> start /u01/bk/scripts/dead\_code.sql

scott@ORCL> alter session set plsql\_warnings='enable:informational';

scott@ORCL> show parameter plsql%ings

NAME TYPE VALUE

\------------------------------------ ----------- ------------------------------

plsql\_warnings string ENABLE:INFORMATIONAL, DISABLE:

PERFORMANCE, DISABLE:SEVERE

scott@ORCL> alter procedure dead\_code compile;

SP2\-0805: Procedure altered with compilation warnings

scott@ORCL> show errors;

Errors for PROCEDURE DEAD\_CODE:

LINE/COL ERROR

\-------- -----------------------------------------------------------------

4/6 PLW\-06002: Unreachable code

7/3 PLW\-06002: Unreachable code

b.检测引起性能问题的代码

scott@ORCL> 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 /

Procedure created.

scott@ORCL> alter session set plsql\_warnings='enable:performance';

scott@ORCL> alter procedure update\_sal compile;

SP2\-0805: Procedure altered with compilation warnings

scott@ORCL> show errors

Errors for PROCEDURE UPDATE\_SAL:

LINE/COL ERROR

\-------- -----------------------------------------------------------------

5/24 PLW\-07202: bind type would result in conversion away from column

六、更多参考

有关SQL请参考

[SQL 基础--> 子查询][SQL _--_]

[SQL 基础-->多表查询][SQL _--]

[SQL基础-->分组与分组函数][SQL_--]

[SQL 基础-->常用函数][SQL _-- 1]

[SQL 基础--> ROLLUP与CUBE运算符实现数据汇总][SQL _--_ ROLLUP_CUBE]

[SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)][SQL_--_START BY ... CONNECT BY PRIOR]

有关PL/SQL请参考

[PL/SQL --> 语言基础][PL_SQL --_]

[PL/SQL --> 流程控制][PL_SQL --_ 1]

[PL/SQL --> 存储过程][PL_SQL --_ 2]

[PL/SQL --> 函数][PL_SQL --_ 3]

[PL/SQL --> 游标][PL_SQL --_ 4]

[PL/SQL -->隐式游标(SQL%FOUND)][PL_SQL --_SQL_FOUND]

[PL/SQL --> 异常处理(Exception)][PL_SQL --_ _Exception]

[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[PL/SQL --> 包的创建与管理][PL_SQL --_ 5]

[PL/SQL --> 包重载、初始化][PL_SQL --_ 6]

[PL/SQL --> DBMS\_DDL包的使用][PL_SQL --_ DBMS_DDL]

[PL/SQL --> DML 触发器][PL_SQL --_ DML]

[PL/SQL --> INSTEAD OF 触发器][PL_SQL --_ INSTEAD OF]

[SQL _--_]: http://blog.csdn.net/robinson_0612/archive/2010/05/14/5592002.aspx
[SQL _--]: http://blog.csdn.net/robinson_0612/archive/2010/05/06/5563199.aspx
[SQL_--]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559110.aspx
[SQL _-- 1]: http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx
[SQL _--_ ROLLUP_CUBE]: http://blog.csdn.net/robinson_0612/archive/2010/06/01/5640031.aspx
[SQL_--_START BY ... CONNECT BY PRIOR]: http://blog.csdn.net/robinson_0612/archive/2010/05/22/5616877.aspx
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063399.aspx
[PL_SQL --_ 1]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063422.aspx
[PL_SQL --_ 2]: http://blog.csdn.net/robinson_0612/archive/2010/12/08/6063434.aspx
[PL_SQL --_ 3]: http://blog.csdn.net/robinson_0612/archive/2010/12/11/6069531.aspx
[PL_SQL --_ 4]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[PL_SQL --_ _Exception]: http://blog.csdn.net/robinson_0612/archive/2010/12/16/6080119.aspx
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084390.aspx
[PL_SQL --_ 5]: http://blog.csdn.net/robinson_0612/archive/2010/12/18/6084475.aspx
[PL_SQL --_ 6]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092097.aspx
[PL_SQL --_ DBMS_DDL]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098248.aspx
[PL_SQL --_ DML]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098258.aspx
[PL_SQL --_ INSTEAD OF]: http://blog.csdn.net/robinson_0612/archive/2010/12/25/6098263.aspx