postgresql 异常处理 plsql异常处理

转载

jkfox 2024-05-08 12:38:47

文章标签 postgresql 异常处理异常处理自定义异常可执行 文章分类 云原生云计算

一、异常

在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_UserDefinedException EXCEPTION;
  ....
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_TooSmallSalary EXCEPTION;
  --声明当前作用的工资变量v_CurrentSalary
  v_CurrentSalary NUMBER(8,2);
  --声明作家工资的最低限变量v_SmallSalary
  v_SmallSalary NUMBER(8,2)DEFAULT100;
BEGIN
  --查找"A00002"作家的工资
  SELECT salary INTO v_CurrentSalary FROM auths
  WHERE author_code ='A00002';
  --检查作家的工资
  IF v_CurrentSalary < v_SmallSalary THEN
   --产生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_code IN 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_TooSmallSalary EXCEPTION;
  v_CurrentSalary NUMBER(8,2);
  v_SmallSalary NUMBER(8,2)DEFAULT100;
BEGIN
  SELECT salary INTO v_CurrentSalary FROM auths
  WHERE author_code ='A00002';
  IF v_CurrentSalary < v_SmallSalary THEN
    RAISE e_TooSmallSalary;
  ELSE
    NULL;
  ENDIF;
EXCEPTION
  WHEN e_TooSmallSalary THEN
    UPDATE auths SET 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_TooSmallSalary EXCEPTION;
  v_CurrentSalary NUMBER(8,2);
  v_SmallSalary NUMBER(8,2)DEFAULT100;
  --获得错误消息代码的变量
  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;
  ELSE
    NULL;
  ENDIF;
EXCEPTION
  WHEN e_TooSmallSalary THEN
    DELETEFROM auths WHERE 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;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。