--================================
-- 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 基础--> ROLLUP与CUBE运算符实现数据汇总
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
有关PL/SQL请参考
