java - 使用 spring 的 SimpleJdbcCall 执行存储函数不给出输出

Question

我正在尝试使用 spring 的 SimpleJdbc 调用来调用存储函数。我编写了一个简单的函数，它接受两个数字作为输入并返回它们的总和。我使用 Oracle 11.2g 作为数据库。我没有得到任何异常，但同时也没有得到结果。当通过 sql-plus 从匿名 PL/SQL block 调用时，该函数运行良好。代码如下:

SimpleJdbcCall _simpleJdbcCall=new SimpleJdbcCall(this.jdbcTemplate);
_simpleJdbcCall.withCatalogName("BROADCASTSHEETMANAGEMENT");
_simpleJdbcCall.withSchemaName("PPV");
_simpleJdbcCall.withFunctionName("TEST");
_simpleJdbcCall.withoutProcedureColumnMetaDataAccess();

_simpleJdbcCall.declareParameters(new  SqlParameter("newChangeSequence",java.sql.Types.NUMERIC));
_simpleJdbcCall.declareParameters(new SqlParameter("number1",java.sql.Types.NUMERIC));
_simpleJdbcCall.declareParameters(new SqlParameter("number2",java.sql.Types.NUMERIC));

MapSqlParameterSource mapSqlParameterSource1=new MapSqlParameterSource();
mapSqlParameterSource1.addValue("newChangeSequence", Integer.valueOf(0));
mapSqlParameterSource1.addValue("number1", Integer.valueOf(10));
mapSqlParameterSource1.addValue("number2", Integer.valueOf(20));

    newChangeSequence = _simpleJdbcCall.executeFunction(Integer.class,mapSqlParameterSource1);

System.out.println("Returned changeSequence is: " + newChangeSequence);

堆栈跟踪显示以下信息:

2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: newChangeSequence
2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: number1
2013/12/19 18:52:53,604 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Added declared parameter for [TEST]: number2
2013/12/19 18:52:53,605 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - JdbcCall call not compiled before execution - invoking compile
2013/12/19 18:52:53,608 [main] - [] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
2013/12/19 18:52:53,609 [main] - [] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@localhost:1521:orcl]
2013/12/19 18:52:53,647 [main] - [] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Registering transaction synchronization for JDBC Connection
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory - Using org.springframework.jdbc.core.metadata.OracleCallMetaDataProvider
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - Compiled stored procedure. Call string is [{? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)}]
2013/12/19 18:52:53,649 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - SqlCall for function [TEST] compiled
2013/12/19 18:52:53,651 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataContext - Matching [number2, number1, newChangeSequence] with [number2, newChangeSequence, number1]
2013/12/19 18:52:53,651 [main] - [] DEBUG org.springframework.jdbc.core.metadata.CallMetaDataContext - Found match for [number2, number1, newChangeSequence]
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - The following parameters are used for call {? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)} with: {number2=20, number1=10, newChangeSequence=0}
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 1: newChangeSequence SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 2: number1 SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,652 [main] - [] DEBUG org.springframework.jdbc.core.simple.SimpleJdbcCall - 3: number2 SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013/12/19 18:52:53,653 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - Calling stored procedure [{? = call PPV.BROADCASTSHEETMANAGEMENT.TEST(?, ?)}]
2013/12/19 18:52:53,655 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - CallableStatement.execute() returned 'false'
2013/12/19 18:52:53,655 [main] - [] DEBUG org.springframework.jdbc.core.JdbcTemplate - CallableStatement.getUpdateCount() returned -1
Returned changeSequence is: null

存储过程代码为:

function test(number1 number, number2 number) return number is
    newChangeSequence number(4);
begin
     newChangeSequence:= number1 + number2;
             return newChangeSequence;
end test; 

Answer 1

你必须像这样使用SqlOutParameter:

_simpleJdbcCall.declareParameters.declareParameters(new SqlOutParameter("newChangeSequence",java.sql.Types.NUMERIC));

也请发布您的存储过程代码。