PLSQL----异常处理

一、异常

在PL/SQL中出现的警告或错误叫异常，对异常的处理称为异常处理

异常可以分为：预定义异常、用户自定义异常

 

1、预定义异常
它是由系统定义的异常，由于它们已在standard包中预定义了，因此这些预定义异常可以直接在程序中使用，而不必在定义部分声明。

 

2、用户自定义异常

它需要在定义部分声明后，才能在可执行部分使用

 

3、异常声明（用户自定义）
注意：异常声明，它必须先在定义部分声明

方法一：异常声明的语法如下
exception_name  EXCEPTION
说明如下：
（1）EXCEPTION用来指定声明的是异常，exception是一个自定义异常名
（2）这种声明的异常，可以通过RAISE语句来产生这个异常，且这种异常是跟预定义异常对应的Oracle的错误相连的

例1：声明一个名为e_TooManyAuthors的异常
DECLARE

  e_TooManyAuthors EXCEPTION;
注意如下：
①异常是一个错误状态，而不是一个数据项，所以异常不能出现在赋值语句或者SQL语句中
②异常的作用域，它与定义部分其它变量的作用域相同
③如果让一个用户在异常的作用域外使用该异常，那么可以将该异常放在包中进行声明，而使用时在异常前加包名前缀即可

 

例2：下面的包中声明了一个异常e_UserDefinedException，这个异常可以在任何块中使用

CREATEORREPLACEPACKAGE globals

  /*这个包中声明的对象可在任意块中引用,注意这个包没有包体*/

  e_UserDefinedExceptionEXCEPTION;

  ....

END globals;

 

方法二：由于预定义异常只是与一部分Oracle错误相连的异常，所以如果要处理没有与预定义异常对应的Oracle的错误时，则需要为这些Oracle错误声明相应的用户自定义异常。声明语的语法如下：
exception_name  EXCEPTION;
PRAGMA  EXCEPTION_INIT(exception_name,Oracle_error_number);
说明如下：
（1）exception_name是预先被声明的异常名，Oracle_error_number是错误号
（2）这种声明的异常，可以自动产生异常（当然也是可以使用RAISE来产生异常的），且这种异常是与预定义异常对应的Oracle的错误不相连的

 

例1：在块定义部分声明了一个与错误”ORA-01401：inserted value too large for column”对应的用户自定义异常c_TooLarge。当在块的可执行部分向表中插入的列值超出指定的列长度时，则产生e_TooLarge异常
DECLARE

  v_Code auths.name%TYPE;

  e_TooLarge EXCEPTION;

  PRAGMAEXCEPTION_INIT(e_TooLarge,-1401);

注意如下：
①通过EXCEPTION_INIT，一个用户自定义异常只能和一个Oracle错误相连
②在异常处理语句中，SQLCODE和SQLERRM将返回这个Oracle错误的代码和消息文本，而不是返回用户自定义消息

 

4、异常产生（用户自定义）

注意：当与预定义异常对应的错误出现时，则该预定义异常就会自动产生
1）第一种：由exception_name EXCEPTION定义的异常，通常由RAISE语句产生（由EXCEPTION_INIT编译指令声明的用户自定义异常也可以通过对应原Oracle错误的出现而产生）。当然如果需要的话，预定义异常也可以使用RAISE语句来产生

3）例子
例1：下面块中使用RAISE语句产生用户自定义异常e_TooSmallSalary
DECLARE

  --声明用户自定义异常e_TooSmallSalary

  e_TooSmallSalaryEXCEPTION;

  --声明当前作用的工资变量v_CurrentSalary

  v_CurrentSalaryNUMBER(8,2);

  --声明作家工资的最低限变量v_SmallSalary

  v_SmallSalaryNUMBER(8,2)DEFAULT100;

BEGIN

  --查找"A00002"作家的工资

  SELECT salaryINTO v_CurrentSalaryFROM auths

  WHERE author_code='A00002';

  --检查作家的工资

  IF v_CurrentSalary< v_SmallSalaryTHEN

   --产生e_TooSmallSalary异常

    RAISE e_TooSmallSalary;

  ELSE

    NULL;

  ENDIF;

END;
注意如下：当一个异常产生时，控制权立即转交给块的异常处理部分。如果该块没有异常处理部分，则向该块的外一层块传递。一旦控制权交给了异常处理部分，则再没有办法返回到块的可执行部分 

 

2）第二种：由EXCEPTION_INIT定义的异常，会自动产生该异常
例如：声明了一个用户自定义异常e_TooLarge（使用EXCEPTION_INIT），当向表中插入一个列值超出了该列指定的长度时，会自动产生异常
DECLARE

  v_Code auths.name%TYPE;

  e_TooLarge EXCEPTION;

  PRAGMAEXCEPTION_INIT(e_TooLarge,-1401);

BEGIn

  INSERTINTO auths(author_code,name, birthdate, entry_date_time)

    VALUES('A00001','WANG',TO_DATE('11-3-50'),TO_DATE('12-1-97'));

END;
说明如下：
（1）由于插入的作家代码值”A00001”超出了列author_code的长度，所以产生错误”ORA-01401: inserted value too large for column”，它对应用户自定义异常”e_ToolArge”，同时控制权转到块外的调整用环境。
（2）当用户自定义异常e_TooLarge也可以使用RAISE语句来产生

 

3）第三种：使用RAISE_APPLICATION_ERROR函数来直接产生异常，并且能为异常定义用户自己指定的错误的消息。执行完RAISE_APPLICATION_ERROR函数后，控制权转到块外的调用环境
（1）语法如下
RAISE_APPLICATION_ERROR(error_number,error_message, [keep_errors]);

说明如下
①error_number：是一个错误号，但值必须在-20000到-20999之间

②error_message：是与该错误相连的错误消息文本，它最大不能超过512个字符

③keep_errors是一个boolean值（可选参数），如果该值为TRUE，则这个新的错误将加在已产生的错误列表之后；如果该值为FLASE，则这个新错误将代替当前的错误列表。

（2）例子
CREATEORREPLACEPROCEDURE SalaryAdd(

  --p_Author_code用来确定准备长工资的作用

  p_Author_codeIN auths.author_code%TYPE,

  --p_AddSalary用来传入增加的工资

  p_AddSalary IN auths.salary%TYPE)AS

  --v_CurrentSalary用来存储作家工资增加以后的值

  v_CurrentSalary auths.salary%TYPE;

  --v_MaxSalary用来存储最高工资1000元

  v_MaxSalary auths.salary%TYPEDEFAULT1000;

BEGIN

  UPDATE auths SET salary=salary+p_AddSalary

  WHERE author_code = p_Author_code

  RETURNING salary INTO v_CurrentSalary;

  --如果作家p_Author_code不存在,则提示一个错误信息,并退出该存储过程

  IFSQL%NOTFOUNDTHEN

    --准备长工资的作家不存在时,提示一个错误信息

RAISE_APPLICATION_ERROR(-20001,'没有代码为'|| p_Author_code ||'的作家存在');

  ELSE

    NULL;

  ENDIF;

  COMMIT;

ENDSalaryAdd;

 

5、异常处理

1）异常处理部分包含着对异常的处理语句。当一个异常相应的错误发生导致这个异常产生时，异常处理语句被执行。异常处理部分的语法如下：
EXCEPTION

   WHEN  exception_name1OR exception_name2 THEN

      Sequence_of_statements1;

   WHEN  exception_name  THEN

      Sequence_of_statements2;
   ………………………………………………….
   WHEN OTHERS  THEN

      Sequence_of_statement3;

说明如下：
（1）exception_name：异常的名字
（2）OTHERS语句：它是对应前面WHEN子句中未出现的所有错误
（3）Sequence_of_statements：是对exception_name异常或其它的所有异常（OTHERS）进行处理的语句
（4）一条异常处理语句可以处理多个异常，只要在WHEN子句中加由OR分隔的多个异常名即可

例如：下列这个例子

DECLARE

  e_TooSmallSalaryEXCEPTION;

  v_CurrentSalaryNUMBER(8,2);

  v_SmallSalaryNUMBER(8,2)DEFAULT100;

BEGIN

  SELECT salaryINTO v_CurrentSalaryFROM auths

  WHERE author_code='A00002';

  IF v_CurrentSalary< v_SmallSalaryTHEN

    RAISE e_TooSmallSalary;

  ELSE

    NULL;

  ENDIF;

EXCEPTION

  WHEN e_TooSmallSalaryTHEN

    UPDATE authsSET salary=500WHERE author_code='A0002';

  WHENOTHERSTHEN

    ROLLBACK;  

END;

注意如下 ：

（1）如果块中的异常没有被处理，则该块会带着未处理的异常返回调用它的程序，这会导致调用它的程序出错，例如：在存储过程中出现异常时，则存储过程的OUT参数将得不到返回值

（2）最好在块的最外层使用OTHERS子句来处理块中所有未处理的异常，这样就可以确保所有的错误都能被发现和处理
（3）在块的三个部分（定义部分、可执行部分、异常处理部分）都可能产生异常，但大多数情况下是在可执行部分产生异常

 

2）处理“可执行部分”产生的异常

 

（1）第一种：异常e_exc在内部块中产生并被处理，然后控制权转到块外调用环境中

（2）第二种：在内部块中产生的异常e_exc2（注意：该异常是在外部中被声明）在本块中没有对应的处理语句，而在外部块中有e_exc2的处理语句，则e_exc2被传递到处部块中处理

 

3）处理“定义部分”产生的异常

注意：如果块的"定义部分"的一个赋值语句产生了异常，即使用在当前块的异常处理部分中有处理该异常的处理语句，也不去执行，而是立刻被传递到外部块中。当异常传递到外部块中以后，按照处理可执行部分中产生的异常一样去处理该异常

 

例如：在定义部分产生了一个名为VALUE_ERROR异常，块中的OTHERS处理语句并没有处理该异常，该异常被传递到块外

 

 

4）处理“异常部分”产生的异常

说明：在异常处理语句中也可以产生异常，这个异常可以通过RAISE语句产生，或是由于出现一个运行错误而产生。这两种情况下产生的异常都被立刻传递到块外，这与定义部分产生的异常一样（因为异常部分每一次只能有一个异常被处理，当一个异常处理时，产生了另一个异常，而一次不能同时处理多个异常，所以将异常处理部分产生的异常传递到块外

 

例如：在内部块的异常处理部分产生了异常e_exc2，同时在内部块中有处理异常e_exc2的语句，但异常e_exc2被传递到外部块中，在外部块中被处理，而且外部块成功结束

说明：在异常处理语句中，RAISE语句还可以不带参数地使用。如果RAISE语句不带参数，则当前的异常被传递到块外

 

例如：下面块中的异常e_exc执行完处理它的语句后，由不带参数的RAISE语句将其传递到块外

 

5）SQLCODE和SQLERRM函数
说明：由于OTHERS子句处理WHEN子句没有处理的异常，所以在OTHERS子句中处理的异常是未知的。我们可以使用SQLCODE和SQLERRM函数来确定异常对应的错误代码和信息

（1）SQLCODE：返回异常对应原错误代码

（2）SQLERRM：返回的是对应的错误信息

下表是异常和对应的SQLCODE、SQLERRM值，如下

注意：
（1）如果使用EXCEPTION_INIT预编译指令声明与Oracle错误相连的自定义异常，则SQLCODE和SQLERRM返回对应的Oracle错误代码和相应的错误信息，而不是返回“+1”和“User-Defined Exception"
（2）如果要在SQL语句中使用SQLCODE和SQLERRM，则一定要先把它们的值赋给局部变量，然后再将这些局部变量用在SQL语句中（因为这些函数是过程性的，不能直接用在SQL语句中）

 

例如：一个带有完整的OTHERS异常处理语句的PL/SQL块
DECLARE

  e_TooSmallSalaryEXCEPTION;

  v_CurrentSalaryNUMBER(8,2);

  v_SmallSalaryNUMBER(8,2)DEFAULT100;

  --获得错误消息代码的变量

  v_ErrorCode NUMBER;

  --获得错误消息文本的变量

  v_ErrorText VARCHAR2(200);

BEGIN

  SELECT salaryINTO v_CurrentSalaryFROM auths

  WHERE author_code='A00002';

  IF v_CurrentSalary< v_SmallSalaryTHEN

    RAISE e_TooSmallSalary;

  ELSE

    NULL;

  ENDIF;

EXCEPTION

  WHEN e_TooSmallSalaryTHEN

    DELETEFROM authsWHERE author_code='A00002';

  WHENOTHERSTHEN

    v_ErrorCode:=SQLCODE;

    v_ErrorText:=SUBSTR(SQLERRM,1,200);

    --显示错误代码

    DBMS_OUTPUT.PUT_LINE(v_ErrorCode);

    --显示错误文本

    DBMS_OUTPUT.PUT_LINE(v_ErrorText);

END;