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

--================================

-- 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 基础--> ROLLUP与CUBE运算符实现数据汇总

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

有关PL/SQL请参考

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

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

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器