oracle存储过程抛出异常之RAISE和RAISE_APPLICATION_ERROR

以前我们项目中存储过程里面抛出异常都是用的【RAISE E_EXCEPTION】方式，但是最近发现程序在正式环境好像出了点什么问题，老是返回些1User-defined Exception之类的结果，但是在开发环境却不会。诡异的是，相同的数据，一样的代码，返回结果却不一样。令人百思不得其解。

经过百般尝试，终于发现【RAISE_APPLICATION_ERROR】这种抛出异常的方式没有任何问题。然后就对RAISE E_EXCEPTION和RAISE_APPLICATION_ERROR进行了一次测试研究。

在多次测试和分析后发现，开发环境数据库是11.1版本，而正式环境的是11.2版本，会不会是数据库版本导致？下面就开始测试

先测试RAISE_APPLICATION_ERROR：

  /*P_TEST_RAISE_EXCEPTION 
  * 在 11.1 版本中返回 自定义的异常
  * 在 11.2 版本中返回 自定义的异常
  */
  PROCEDURE P_TEST_RAISE_EXCEPTION(P_INVOICEIDS IN VARCHAR2,
                                   P_OPERATORID IN NUMBER,
                                   P_RESULT     OUT NUMBER,
                                   P_MSG        OUT VARCHAR2) AS
    E_EXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(E_EXCEPTION, -20001);
  BEGIN
    FOR V_INVOICEID IN (SELECT COLUMN_VALUE AS ID
                          FROM TABLE(PKG_FSSS_RMS_RI.F_SPLIT(P_INVOICEIDS,
                                                             ','))) LOOP
      P_RESULT := -1;
      P_MSG    := '自定义的异常';
      RAISE_APPLICATION_ERROR(-20001, P_MSG);
    END LOOP;
  EXCEPTION
    WHEN E_EXCEPTION THEN
      P_RESULT := -1;
      P_MSG    := P_MSG;
    WHEN OTHERS THEN
      P_RESULT := -1;
      P_MSG    := '出错[' || SQLCODE || SQLERRM || ']';
  END;

在使用RAISE_APPLICATION_ERROR抛出异常时，开发环境和正式环境都正常返回了【自定义的异常】。

接下来测试RAISE E_EXCEPTION

  /*P_TEST_RAISE_APP_EXCEPTION 
  * 在 11.1 版本中返回 自定义的异常
  * 在 11.2 版本中返回 出错[1User-defined Exception]
  */
  PROCEDURE P_TEST_RAISE_APP_EXCEPTION(P_INVOICEIDS IN VARCHAR2,
                                       P_OPERATORID IN NUMBER,
                                       P_RESULT     OUT NUMBER,
                                       P_MSG        OUT VARCHAR2) AS
    E_EXCEPTION EXCEPTION;
  BEGIN
    FOR V_INVOICEID IN (SELECT COLUMN_VALUE AS ID
                          FROM TABLE(PKG_FSSS_RMS_RI.F_SPLIT(P_INVOICEIDS,
                                                             ','))) LOOP
      P_RESULT := -1;
      P_MSG    := '自定义的异常';
      RAISE E_EXCEPTION;
    END LOOP;
  EXCEPTION
    WHEN E_EXCEPTION THEN
      P_RESULT := -1;
      P_MSG    := P_MSG;
    WHEN OTHERS THEN
      P_RESULT := -1;
      P_MSG    := '出错[' || SQLCODE || SQLERRM || ']';
  END;

在 11.1 版本数据库中返回 自定义的异常
在 11.2 版本数据库中返回 出错[1User-defined Exception]
 

结果一下就清晰了，RAISE E_EXCEPTION时，11.1版本的oracle数据库会在WHEN E_EXCEPTION THEN中捕获该异常。而11.2版本的数据库却不会被WHEN E_EXCEPTION THEN捕获，而进了OTHER异常的捕获。所以在存储过程抛出异常时，最好使用第一种形式，虽然代码多些，但是靠谱。千万不要一时方便使用RAISE E_EXCEPTION。