Oracle存储过程中异常处理总结

最新推荐文章于 2022-12-20 10:37:08 发布

Smile_山庄

最新推荐文章于 2022-12-20 10:37:08 发布

阅读量1.1k

点赞数 1

文章标签： oracle 存储异常处理 exception 数据库

本文链接：https://blog.csdn.net/smile8912/article/details/25797365

版权

在Oracle中对于一些比较复杂的逻辑处理，我们通常会想到使用存储过程。但通常会遇到各种各样的异常，对于自己最近做过的一些项目中使用到存储过程中的异常处理做一个总结，希望对大家有所帮助。

有三种类型的异常错误：

1．预定义 ( Predefined )错误

ORACLE预定义的异常情况大约有21个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

预定义异常
命名的系统异常	产生原因
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游标已经打开
DUP_VAL_ON_INDEX	唯一索引对应的列上有重复的值
INVALID_CURSOR	在不合法的游标上进行操作
INVALID_NUMBER	内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND	使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为0
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED	PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON	PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR	PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待资源时超时

2．非预定义 ( Predefined )错误

即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

3．用户定义(User_define) 错误

EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;

一、捕获系统预定义异常

看下面的一个例子。

有以下几种表，

SYS_DEV ,系统设备表， SYS_DMS 系统服务表。其中设备挂在在服务器上。现在要处理的问题是：删除或者修改服务器，为了保证数据库的一致性，我们必须更改挂在在服务器上的设备的。 SYS_DEV与 SYS_DMS是通过DMS_ID匹配。

存储过程代如下：

create or replace PROCEDURE DELETE_DMS (
   V_ROWNO          IN VARCHAR2,  ---输入要删除的DMS服务器的rowno字段。
   IS_SUCCESSED     OUT NUMBER, --操作成功标识，0表示失败，1表示成功。
   RETURN_MESSAGE   OUT VARCHAR2, ---返回用户提示。
   DEV_DATA   OUT SYS_REFCURSOR  ----输出要删除的DMS服务器上挂在的卡口信息。
)
as
/*********************************************************
功能说明：
        DMS服务器删除
参数说明：
       
*********************************************************/        
        DEV_NAME VARCHAR2(128);  
        DMS_CODED VARCHAR2(64);
        DMS_NAME VARCHAR2(128);
        vTEXT VARCHAR2(400);  
BEGIN  
      SELECT CODED INTO DMS_CODED FROM SYS_DMS WHERE SYS_DMS.ROWNO = V_ROWNO;
      SELECT TITLE INTO DMS_NAME FROM SYS_DMS WHERE SYS_DMS.CODED = DMS_CODED;
      vTEXT := 'SELECT SD1.ROWNO, SD1.TITLE AS DEV_NAME, SD1.CODED AS DEV_ID, 
                       SD1.DMSID,SD.TITLE AS DMS_NAME
                 FROM (SELECT TITLE ,CODED FROM SYS_DMS WHERE SYS_DMS.CODED =' 
                 ||DMS_CODED||')SD JOIN SYS_DEV SD1 ON (SD.CODED = SD1.DMSID)';
    IF DMS_CODED IS NOT NULL AND DMS_NAME IS NOT NULL THEN
      BEGIN
        SELECT  TITLE INTO DEV_NAME FROM SYS_DEV WHERE SYS_DEV.DMSID = DMS_CODED;   
          IF DEV_NAME IS  NOT NULL THEN 
          RETURN_MESSAGE := 'DMS服务器：'||DMS_NAME||',关联了其他卡口，
                            请先修改这些卡口关联的DMS服务器后执行删除操作。';
          OPEN DEV_DATA FOR vTEXT;
          IS_SUCCESSED := 0;         
          END IF;        
        EXCEPTION
          WHEN TOO_MANY_ROWS THEN
          RETURN_MESSAGE := 'DMS服务器：'||DMS_NAME||',关联了其他卡口，
                             请先修改这些卡口关联的DMS服务器后执行删除操作。';
          OPEN DEV_DATA FOR vTEXT;
          IS_SUCCESSED := 0;           
          WHEN NO_DATA_FOUND THEN
          DELETE FROM SYS_DMS WHERE ROWNO =   V_ROWNO;       
          RETURN_MESSAGE := '成功删除DMS服务器：'||DMS_NAME||'。'; 
          IS_SUCCESSED := 1;
        END;
    END IF;
      EXCEPTION 
        WHEN NO_DATA_FOUND THEN
          /*数据已删除，对同一个DMS服务器执行多次删除造成无法找到数据的异常。*/
          RETURN_MESSAGE := '此DMS服务器不存在或已经删除，请检查后重新输入。';
          IS_SUCCESSED := 0;
        WHEN TOO_MANY_ROWS THEN
        /*DMS_CODED 不能唯一标识DMS一行数据造成的异常。*/
          RETURN_MESSAGE := '数据库内部错误，请联系开发人员。';
          IS_SUCCESSED := 0;
END DELETE_DMS;

总结：对于预定的异常只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理。

二、捕获非预定义异常

1. 在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;
PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);

2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：

3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例2：依然是上面的那个例子，删除服务器，确保服务器上没有挂载设备。

存储过程代码如下：

CREATE OR REPLACE PROCEDURE DELETE_DMS (
   V_ROWNO          IN VARCHAR2,  ---输入要删除的DMS服务器的rowno字段。
   IS_SUCCESSED     OUT NUMBER, --操作成功标识，0表示失败，1表示成功。     
)
as
/*********************************************************
功能说明：
        DMS服务器删除
*********************************************************/        
        DMS_REMAINING EXCEPTION;        
BEGIN        
      DELETE FROM SYS_DMS WHERE ROWNO = V_ROWNO;
      IS_SUCCESSED :=1;
     EXCEPTION
       WHEN DMS_REMAINING THEN
         DBMS_OUTPUT.PUT_LINE('DMS服务器关联了其他设备，请先修改这些设备关联的DMS服务器后执行删除操作');
         IS_SUCCESSED :=0;
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
          IS_SUCCESSED :=0;
END DELETE_DMS;

三、捕获用户定义异常

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：

1．在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;

2． RAISE <异常情况>；

3．在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例3：依然是上面那个例子，同样的处理。

代码如下：

create or replace PROCEDURE DELETE_DMS (
   V_ROWNO          IN VARCHAR2,  ---输入要删除的DMS服务器的rowno字段。
   IS_SUCCESSED     OUT NUMBER, --操作成功标识，0表示失败，1表示成功。
   RETURN_MESSAGE   OUT VARCHAR2, ---返回用户提示。  
)
as
/*********************************************************
功能说明：
        DMS服务器删除 
*********************************************************/        
        DMS_CODED VARCHAR2(64);        
	NO_RESULT EXCEPTION;
	TOO_MANY EXCEPTION;
BEGIN  
      SELECT CODED INTO DMS_CODED FROM SYS_DMS WHERE SYS_DMS.ROWNO = V_ROWNO;      
    IF  TOO_MANY_ROWS THEN
           RAISE  TOO_MANY;  
      END IF;  
    IF NO_DATA_FOUND AND SQL%NOTFOUND THEN
           RAISE NO_RESULT;
     END IF;
        EXCEPTION
          WHEN TOO_MANY THEN
          RETURN_MESSAGE := 'DMS服务器关联了其他卡口，请先修改这些卡口关联的DMS服务器后执行删除操作。';
          OPEN DEV_DATA FOR vTEXT;
          IS_SUCCESSED := 0;           
          WHEN NO_RESULT THEN
          DELETE FROM SYS_DMS WHERE ROWNO =  V_ROWNO;       
          RETURN_MESSAGE := '成功删除DMS服务器。'; 
          IS_SUCCESSED := 1;      
END DELETE_DMS;

总结：对于异常的处理，应尽量调用预定义异常，因为捕获和处理都比较简单，当预定义异常无法满足时使用是采用非预定义异常和用户自定义的异常，

在异常处理过程中应该多细心，对于一些很少发生或者只在极端情况下发生的异常可以采取适当的捕获后抛掉。下一篇文章在专门讲解用户自定义异常的处理。