plsql的错误信息与异常处理

7 错误消息与异常处理

7.1 异常的介绍

(1) 处理异常分为三个步骤：
A 声明异常
B 引发异常
C 处理异常

(2) 异常的特征
A 错误类型： ORA-xxxxx 运行时错误
PLS-xxxxx 编译错误
B 错误代码：xxxxx
C 错误的文本描述

案例1：编译错误的案例PLS

SQL> create or replace procedure p1 is
2 begin
3 null;
4 end;
5 /

Procedure created.

SQL> create or replace procedure p1 is
2 begin
3 null --特意不写分号
4 end;
5 /

Warning: Procedure created with compilation errors. --出现警告

SQL> show error --查看错误消息
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
;
The symbol ";" was substituted for "END" to continue.

案例2：运行时错误 -- 在编译的时候没有错误，但是在执行的时候发生错误。

SQL> create or replace procedure p2 is
2 v_descr varchar2(20);
3 begin
4 select hrc_descr
5 into v_descr
6 from hrc_tab
7 where hrc_code=8;
8 dbms_output.put_line(to_char(v_descr));
9 end;
10 /

Procedure created.

SQL> exec p2; --运行一个存储过程
BEGIN p2; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "PLSQL.P2", line 4
ORA-06512: at line 1

总结：
A PLSQL错误 -- 编译错误，在执行之前就已经报错，需要检查程序，修改程序，debug
B ORA错误 -- 运行时错误，出现这种错误的时候需要手工处理，可以采用第三方软件的单步调试方式处理

(2) 异常处理中的声明，分为三个部分

A exception声明，在声明自己定义异常的时候需要用到这个方法。
B raise语句：显示地引发异常
C pragma excetption_init 这个指令可以将oracle错误和自己定义异常关联起来

函数(需要定义变量来接收)

sqlcode -- 返回错误的代码号，如果没有错误就返回0，可以根据sqlcode返回的值查询官方文档，获得更详细的错误描述
sqlerrm -- 返回错误的文本描述，如果没有错误返回normal或者successful completion，也就是官方文档定义的错误

(3) 异常处理的常见案例
declare
v_descr varchar2(20);
begin
select hrc_descr
into v_descr
from hrc_tab
where hrc_code=8;
dbms_output.put_line(v_descr);
exception when no_data_found then --异常的名称
dbms_output.put_line('not exists');
end;

no_data_found --oracle预定义好的异常的名称,oracle官方文档上有每个异常名称描述和引发的场景介绍
《PL/SQL User's Guide and Reference》pdf版本中第264页

sqlcode与sqlerrm两个函数的使用

declare
v_descr varchar2(20);
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
select hrc_descr
into v_descr
from hrc_tab
where hrc_code=8;
dbms_output.put_line(v_descr);
exception when no_data_found then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
dbms_output.put_line('not exists');
dbms_output.put_line('ERR: an error with info :'||to_char(v_sqlcode));
dbms_output.put_line(v_sqlerrm);
end;

输出：
not exists
ERR: an error with info :100 --100是错误的代码号，其他的错误sqlcode都是ora-后面的号码，这个异常特殊
ORA-01403: no data found --错误的描述

对程序的异常进行处理，让程序不会在发生异常

declare
v_descr varchar2(20);
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
select hrc_descr
into v_descr
asdfasdg from hrc_tab
where hrc_code=8;
dbms_output.put_line(v_descr);
exception when no_data_found then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
dbms_output.put_line('not exists');
dbms_output.put_line('ERR: an error with info :'||to_char(v_sqlcode));
dbms_output.put_line(v_sqlerrm);
insert into hrc_tab values(8,'asdfasdg');
commit;
end;

第一次运行

输出：
not exists
ERR: an error with info :100
ORA-01403: no data found

再一次运行
输出：
asdfasdg

(4)PLSQL异常的功能性分类

A 预定义的异常 oracle自己预先定义好的
B 用户自定义的异常

用户自定义的异常

declare
site_s_undefined_for_org exception;
v_cnt number;
begin
select count(*) into v_cnt from org_site_tab where org_id=1007; --本身查询是没问题的
if v_cnt=0 then --只有在v_cnt值为0的时候引发异常
raise site_s_undefined_for_org;
end if;
exception when site_s_undefined_for_org then
dbms_output.put_line('empty table!');
when others then
dbms_output.put_line('ERR: an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

--自己定义异常，自己抛出异常，自己处理异常

系统预定义的异常

DUP_VAL_ON_INDEX --唯一性约束上有列值的重复冲突

declare
site_s_undefined_for_org exception;
v_cnt number;
begin
select count(*) into v_cnt from org_site_tab where org_id=1007; --本身查询是没问题的
insert into hrc_tab values(8,'asfdadsagsa');--这里出现异常，程序就进入异常处理部分，后面不再执行
commit;
if v_cnt=0 then --只有在v_cnt值为0的时候引发异常
raise site_s_undefined_for_org;
end if;
exception when site_s_undefined_for_org then
dbms_output.put_line('empty table!');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('value repeat!');
when others then
dbms_output.put_line('ERR: an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

输出：value repeat！

(3) pragma exception_init 指令
这个指令就是把oracle的错误还有用户自定义异常关联起来

[oracle@test ~]$ oerr ora 02290 --知道错误号，可以使用该命令查看详细错误
02290, 00000, "check constraint (%s.%s) violated"
// *Cause: The values being inserted do not satisfy the named check
// constraint.
// *Action: do not insert values that violate the constraint.


select * from user_constraints where table_name='ORG_LEVEL';

SQL> conn plsql/plsql
Connected.
SQL> insert into org_level values(1001,'P');
insert into org_level values(1001,'P')
*
ERROR at line 1:
ORA-02290: check constraint (PLSQL.ORG_LEVEL_CK) violated


declare
invalid_org_level exception;
pragma exception_init(invalid_org_level,-2290); --关联以后，就不需要raise引发异常
begin
create table exception_monitor(
excep_tab_name varchar2(30),
excep_key varchar2(50),
excep_program varchar2(30),
excep_name varchar2(30),
excep_code number,
excep_txt varchar2(200),
excep_date date
);
insert into org_level values(1001,'P');
commit;
exception when invalid_org_level then
dbms_output.put_line('ERR:an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line('ERR:an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

可以让程序自己抛出

begin
insert into org_level values(1001,'P');
commit;
exception
when others then
dbms_output.put_line('ERR:an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

##########################################################################################

7.2 异常监控表

(1) 创建一个异常监控表:exception_monitor

字段
发生异常的表的名字： excep_tab_name
发生异常的行的主键： excep_key
发生异常的程序的名称： excep_program，如果是匿名块就置为null
异常的名称： excep_name 如果没有定义就写'others'
异常的sqlcode: excep_code
异常的文本描述： excep_txt
发生异常的时间： excep_date

以后编写程序的时候都要编写异常处理部分，获取到上面这些信息，插入这个表

创建异常监控表：
create table exception_monitor(
excep_tab_name varchar2(30),
excep_key varchar2(50),
excep_program varchar2(30),
excep_name varchar2(30),
excep_code number,
excep_txt varchar2(200),
excep_date date
);

改写上面的例子：
declare
invalid_org_level exception;
pragma exception_init(invalid_org_level,-2290);
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
insert into org_level values(1001,'P');
commit;
exception when invalid_org_level then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into exception_monitor values('ORG_LEVEL','1001',null,upper('invalid_org_level'),v_sqlcode,v_sqlerrm,sysdate);
commit;
when others then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into exception_monitor values('ORG_LEVEL','1001',null,upper('others'),v_sqlcode,v_sqlerrm,sysdate);
commit;
end;

练习7：将练习6那个程序修改它的异常处理部分，将错误捕获到监控表

错误号的20000~21299是错误号的空缺范围，这个范围用来自定义错误，用内置的函数来引发这个错误。

declare
site_s_undefined_fo_org exception;
pragma exception_init(site_s_undefined_fo_org,-20001);
v_cnt number;
begin
select count(1) into v_cnt from org_site_tab where org_id=1007;
if v_cnt=0 then
raise_application_error(-20001,'this table rows is empty!');
end if;
exception when site_s_undefined_fo_org then
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line('ERR : an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

不关联异常的名称也可以：

declare
v_cnt number;
begin
select count(1) into v_cnt from org_site_tab where org_id=1007;
if v_cnt=0 then
raise_application_error(-20001,'this table rows is empty!');
end if;
exception
when others then
dbms_output.put_line('ERR : an error with info :'||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

##########################################################################################

7.3 在声明部分引发的异常的处理

注意：异常需要在begin 和 exception 之间才能捕获到的

declare
v_cnt number(2):=100;
begin
null;
exception when others then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

改写：

begin
declare
v_cnt number(2):=100;
begin
null;
exception when others then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;
exception when others then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

解决方法：将原来的代码块嵌套在一个begin和exception之间即可捕获到

注意：
A 程序是从begin开始执行的，declare部分不是程序执行的部分
B 异常捕获的区间是begin到exception之间的代码

7.5 在异常部分引发异常的处理

declare
condition boolean:=true;
excep1 exception;
excep2 exception;
begin
if condition then
raise excep1;
end if;
exception when excep1 then
raise excep2;
end;

改写：

declare
condition boolean:=true;
excep1 exception;
excep2 exception;
begin
if condition then
raise excep1;
end if;
exception when excep1 then
begin
raise excep2;
exception when excep2 then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;
end;

或者

declare
condition boolean:=true;
excep1 exception;
excep2 exception;
begin
begin
if condition then
raise excep1;
end if;
exception when excep1 then
raise excep2;
end;
exception when excep2 then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;

7.6 一个异常可以被多次引发

declare
condition boolean:=true;
excep1 exception;
begin
begin
if condition then
raise excep1;
end if;
exception when excep1 then
raise excep1;
end;
exception when excep1 then
dbms_output.put_line('ERR CODE : '||to_char(sqlcode));
dbms_output.put_line(sqlerrm);
end;