Spring 呼叫 Stored Procedure 並獲取返回值

簡述

Stored Procedure 叫做儲存過程（簡稱SP），類似於 Java 中的函式，包含一些 sql 語句集，完成相應的功能，並且也有 輸入（IN）和 輸出（OUT）。

建立 SP

　　1.無 輸出（OUT）引數　

create or replace PROCEDURE SP_TEST_1(
     inPara1 IN  NUMBER,     -- 輸入引數
     inPara2 IN  VARCHAR2
 )  is
  V_CURR_TIME              TIMESTAMP(6);
BEGIN    
    SELECT SYSTIMESTAMP INTO V_CURR_TIME FROM DUAL;
           .
           .
           .
EXCEPTION WHEN OTHERS THEN
    dbms_output.PUT_LINE('Error'); 
END SP_TEST_1;
/
-- 賦給角色需要的許可權
GRANT EXECUTE ON SP_TEST_1 TO read;
GRANT EXECUTE ON SP_TEST_1 TO write;
/

 

   　2.有 輸出（OUT）引數

create or replace PROCEDURE SP_TEST_2(
     inPara1 IN  NUMBER,          -- 輸入引數
     inPara2 IN  VARCHAR2,
     outPara1 OUT VARCHAR2      -- 輸出引數
 )  is
  V_CURR_TIME              TIMESTAMP(6);
BEGIN    
    SELECT SYSTIMESTAMP INTO V_CURR_TIME FROM DUAL;
           .
           .
           .
EXCEPTION WHEN OTHERS THEN
    dbms_output.PUT_LINE('Error'); 
END SP_TEST_2;
/
-- 賦給角色需要的許可權
GRANT EXECUTE ON SP_TEST_2 TO read;
GRANT EXECUTE ON SP_TEST_2 TO write;
/

呼叫 SP

Spring 有很多方法可以呼叫 Oracle 的 procedure，原來我一直用 

getJdbcTemplate().update("{call SP_TEST_1(?, ?)}", new Object[] { inPara1, inPara2 });

在 procedure 沒有 OUT 輸出的時候這樣做很方便，但是當要獲取 OUT 輸出時，這種方法就不靈了。

getJdbcTemplate().update("{call SP_TEST_2(?, ?, ?)}", new Object[] { inPara1, inPara2, outPara});

雖然 SP可以正常被呼叫執行，但是卻不能獲得 SP 返回的輸出引數 outPara。

這時，可以用另一種方法：

public String invokeSPTest(final Long inPara1, final String inPara2) {
　　String outResult = "";
　　try {
　　　　outResult = (String) getJdbcTemplate().execute( new ConnectionCallback(){
   　　　　@Override
   　　　　public Object doInConnection(Connection con) throws SQLException, DataAccessException {
      　　 　　CallableStatement  callableSt = con.prepareCall("{call SP_TEST_2(?, ?, ?)}");
       　　　　callableSt.setLong(1, inPara1);
　　　　　　　　callabelSt.setString(2, inPara2);
      　　 　　callableSt.registerOutParameter(3, Types.VARCHAR);
       　　　　callableSt.executeUpdate();
      　　 　　return callableSt.getString(3);
        　　}
     　　});
　　} catch (CannotGetJdbcConnectionException e) {
　　　　e.printStackTrace();
　　}
　　return outResult;
}

參考資料

• Different Ways of Calling Stored Procedure using Spring