plsql的错误信息与异常处理

ewrQ

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;