PL/SQL 异常处理

异常分为预定义异常和用户自定义异常。
预定义异常是由系统定义的异常。由于它们已在standard包中预定义了，因此，这些异常可以直接在程序中使用，而不必在定义部分声明。
用户自定义异常则需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是oracle异常，例如，它可能是一个数据错误。
1、预定义异常
常见的预定义异常...
--下面的块中产生一个NO_DATA_FOUND异常：

代码

delcare   
  type t_numbertabletype is table of number index by binary_integer;   
  v_numbertable t_numbertabletype;   
  v_tempvar number;   
begin   
  v_tempvar:=v_numbertable(1);   
end; 

 

--下面的语句会产生INVALID_NUMBER异常，因为'123a'不是一个合法的工资值：

代码

insert into auths(author_code,name,birthdate,entry_date_time,salary)   
  values('A00022','王',to_date('02-may-60'),to_date(04-MAR-90'),'123a');   
--下面的块中将产生value_error异常：   
delcare   
  v_tempvar varchar2(3);   
begin   
  v_tempvar:='ABCD';   
end;  

 

2、异常的声明

代码

create or replace package globals   
  --这个包中声明的对象可在任意块中引用。注意这个包没有包体。   
  e_userdefinedexception exception;   
end global; 

由于预定义异常只是与一部分oracle错误相连的异常，所以如果要处理没有与预定义异常相应的oracle的错误时，则需要为这些oracle错误声明相应的用户自定义的异常。
声明这样的异常需要使用exception_init编译指令。
例如，在块定义部分声明了一个与错误"ORA-01401:inserted value too large for column"对应的用户自定义异常e_toolarge。当在块的可执行部分向表中插入的列值超出指定的列长度时，则产生e_toolarge异常：

代码

declare   
  v_code auths.name%type;   
  e_toolarge exception;   
  pragma exception_init(e_toolarge,-1401);  

注意，通过exception_init，一个用户自定义异常只能和一个oracle错误相连。在异常处理语句中，sqlcode和sqlerrm将返回这个oracle错误的代码和消息文本，而不是返回用户自定义消息。

 

3、异常的产生
当与预定义异常相应的错误出现时，则该预定义异常就会自动产生。而一个用户自定义异常通常由RAISE语句来产生(由exception_init编译指令声明的用户自定义异常也可通过对应oracle错误的出现而产生)。
例1，在下面块中使用raise语句产生用户自定义异常e_toosmallsalary：

代码

declare   
  --声明用户自定义异常   
  e_toosmallsalary exception;   
  v_currentsalary number(8,2);   
  v_smallsalary number(8,2) default 100;   
begin   
  select salary into v_currentsalary from auths where author_code='A00002';   
  if v_currentsalary<v_smallsalary then   
    raise e_toosmallsalary;   
  end if;   
end;

当一个异常产生时，控制权立即交给块的异常处理部分。如果该块没有异常处理部分，则向该块的外层块传递。一旦控制权交给了异常处理部分，则再没有办法返回到块的可执行部分。
例2,预定义异常通常是在与之相对应的错误发生时产生。下面的块产生no_data_found异常。

代码

declare   
  v_name varchar2(10);   
begin   
  select name into v_name from auths where author_code='B00006';   
end;  

由于没有作家代码号为"B00006"的作家，所以会产生"ORA-01403:not data found"错误，它就对应于no_data_found异常。
例3，由exception_init编译指令声明的用户自定义异常与一个oracle错误相连，所以这样的用户自定义异常也是在一个oracle错误发生时产生。
上节中已声明了一个用户自定义异常e_toolarge(使用exception_init编译指令声明)。当向表中插入一个列值超出了该列指定的长度时，会自动产生该异常：

代码

begin   
  insert into auths(author_code,name,birthdate,entry_date_time)   
    values('A000001','wang','to_date('11-3月-50'),to_date('12-1月-97'));  
end;

由于输入的作家代码值'A000001'超出了列的长度，所以产生错误"ORA-01401:inserted value too large for column"，它对应用户自定义异常toolarge，同时控制权转到块外的调用环境。
通常用户自定义异常是在声明后才能产生，但如果我们使用raise_application_error函数就可以直接产生异常，并且能为异常定义用户自己指定的错误消息。执行完raise_application_error函数后，控制权转到块外的调用情境。
raise_application_error(error_number,error_message[,keep_errors]);
其中，error_number是一个错误号，值在-20000到-20999之间，error_message是与该错误相连的错误消息文本，最大不超过512个字符。keep_errors是一个boolean值。是可选参数，如果为true，则这个新的错误将加在已产生的错误列表之后，如果为false，则这个新错误将代替当前的错误列表。
如：
raise_application_error(-20001,'没有代码为'||p_author_code||'的作家存在');

 

4、异常处理
异常处理部分包含着对异常的处理语句。当一个异常相应的错误发生导致这个异常发生时，异常处理语句被执行。

代码

declare   
  e_toosmallsalary exception;   
  v_currentsalary number(8,2);   
  v_smallsalary number(8,2) default 100;   
begin   
  select salary into v_currentsalary from auths where author_code='A00002';   
  if v_currentsalary<v_smallsalary then   
    raise e_toosmallsalary;   
  end if;   
exception   
  when e_toosmallsalary then   
    update auths set salary=500 where author_code='A00002';   
end; 

一条异常处理语句可以处理多个异常。只要在when子句中加由or分隔的多个异常名即可。
如果块中的异常没有被处理，则该块会带着未处理的异常返回调用它的程序，这会导致调用它的程序出错。如果在存储过程中出现异常，则存储过程的OUT参数将得不到返回值。
为了避免未处理异常带来的问题，我们最好在块的最外层使用others子句处理块中所有未处理的异常。这样就能确保所有的错误都能被发现和处理。
(1)处理可执行部分产生的异常
(2)处理定义部分产生的异常
如果是定义部分的一个赋值语句产生了异常，即使在当前块的异常处理部分有处理该异常的处理语句时，也不去执行，而是立刻被传递到外部块中。由外部块处理。
(3)处理异常处理部分产生的异常
在异常处理语句中也可以产生异常，这个异常可以通过raise语句产生，或是由于出现一个运行错误而产生。这两种情况下产生的异常都被立刻传递到块外。
(4)sqlcode和sqlerrm函数
由于others子句处理when子句没有处理的异常，所以在others子句中处理的异常是未知的。我们可以用sqlcode和sqlerrm函数来确定异常对应的错误代码和信息。
异常种类 sqlcode sqlerrm
oracle错误对应的异常 负数 oracle错误
no_data_found +100 no data found
用户自定义异常 +1 user-defined exception
没有产生异常 0 ora-0000:normal,successful completion
注意，如果使用exception_init预编译指令声明与oracle错误相连的自定义异常，则sqlcode和sqlerrm返回对应的oracle错误代码和相应的错误信息，则不是返回"+1"和"user-defined exception"。
例如，下面是一个带有完整的others异常处理语句的PL/SQL块：

代码

declare   
  e_toosmallsalary exception;   
  v_currentsalary number(8,2);   
  v_smallsalary number(8,2) default 100;   
  v_errorcode number;--获得错误消息代码的变量。   
  v_errortext varchar2(200);--获得错误消息文本的变量。   
begin   
  select salary into v_currentsalary from auths where author_code='A00002';   
  if v_currentsalary<v_smallsalary then   
    raise e_toosmallsalary;   
  end if;   
exception   
  when e_toosmallsalary then   
    delete auths where author_code='A00002';   
  when others then   
    v_errorcode:=sqlcode;   
    v_errortext:=substr(sqlerrm,1,200);   
    dbms_output.put_line(v_errorcode);   
    dbms_output.put_line(v_errortext);   
end;  

注意，如果要在SQL语句中使用sqlcode和sqlerrm，则一定要先把它们的值赋给局部变量，然后再将这些局部变量用在SQL语句中，因为这些函数都是过程性的，不能直接用在SQL语句中。