Spring 调用 Store Procedure 并获取返回值

简述

Store 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