oracle 如何使用 Spring 调用带有引用游标作为输出参数的存储过程？

How to call a stored procedure with ref cursor as an output parameter using Spring?

提问by Sharda Prasad Jaiswal

I have a stored procedure which has body like :-

我有一个存储过程，它的主体如下：-

PROCEDURE PROC_NAME(param1 in varchar2,param2 in varchar2,results_cursor OUT CURSOR_TYPE);

PROCEDURE PROC_NAME(param1 in varchar2,param2 in varchar2,results_cursor OUT CURSOR_TYPE);

Each row of result is equivalent to an instance of a certain user defined class.

结果的每一行相当于某个用户定义的类的一个实例。

How can I call this in Spring. I went through lot of google and stackoverflow but could not find an apt answer.

我如何在 Spring 中调用它。我浏览了很多 google 和 stackoverflow，但找不到合适的答案。

Can anyone please provide me a solution to this. Thanks in advance.

任何人都可以请为我提供解决方案。提前致谢。

回答by Luke Woodward

Here's something I put together based on this StackOverflow questionand the Spring documentation:

这是我根据此 StackOverflow 问题和Spring 文档整理的内容：

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class SampleStoredProcedure extends StoredProcedure {

    public SampleStoredProcedure(DataSource dataSource) {
        super(dataSource, "PROC_NAME");
        declareParameter(new SqlParameter("param1", Types.VARCHAR));
        declareParameter(new SqlParameter("param2", Types.VARCHAR));
        declareParameter(new SqlOutParameter("results_cursor", OracleTypes.CURSOR, new SomeRowMapper()));
        compile();
    }

    public Map<String, Object> execute(String param1, String param2) {
        Map<String, Object> inParams = new HashMap<>();
        inParams.put("param1", param1);
        inParams.put("param2", param2);
        Map output = execute(inParams);
        return output;
    }
}

If your stored procedure is in another schema or in a package, you'll need to adjust the stored procedure name in the above. Also, you'll need to specify a row mapper to use in place of SomeRowMapper.

如果您的存储过程在另一个架构或包中，则需要在上面调整存储过程名称。此外，您需要指定一个行映射器来代替SomeRowMapper.

To call it:

调用它：

    DataSource dataSource = ... ; // get this from somewhere
    SampleStoredProcedure sp = new SampleStoredProcedure(dataSource);
    Map<String, Object> result = sp.execute("some string", "some other string");
    // Do something with 'result': in particular, result.get("results_cursor")
    // will be the list of objects returned

---

---

Alternatively, you can use a SimpleJdbcCall:

或者，您可以使用SimpleJdbcCall：

    DataSource dataSource = ... ; // get this from somewhere
    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource);
    Map<String, Object> result =
        jdbcCall.withProcedureName("PROC_NAME")
            .declareParameters(
                    new SqlParameter("param1", Types.VARCHAR),
                    new SqlParameter("param2", Types.VARCHAR),
                    new SqlOutParameter("results_cursor", OracleTypes.CURSOR, new SomeRowMapper()))
            .execute("some string", "some other string");

If the stored procedure is in a package, you'll need to add a line

如果存储过程在包中，则需要添加一行

            .withCatalogName("PACKAGE_NAME")

to the setup of jdbcCall. Similarly, if it's in a different schema, you'll need to add

的设置jdbcCall。同样，如果它在不同的架构中，您需要添加

            .withSchemaName("SCHEMA_NAME")

回答by Kiran Kawade

Please have look a look once. List userData;

请看一看。列出用户数据；

    SimpleJdbcCall  executor = new SimpleJdbcCall(jdbcTemplate)
                                .withProcedureName("SP_CL_USERPKS_FOLDER").withoutProcedureColumnMetaDataAccess()
                                .declareParameters(
                                    new SqlParameter("INparam1", Types.INTEGER),
                                    new SqlParameter("INparam2", Types.VARCHAR),
                                    new SqlOutParameter("OUTParam1", OracleTypes.CURSOR),
                                    new SqlOutParameter("OUTParam2", OracleTypes.VARCHAR));
    executor.compile();
    SqlParameterSource param = new MapSqlParameterSource()
            .addValue("INparam1", loginPk)
            .addValue("INparam2", email);

    Map map = executor.execute(param);
    userData = (List<Map>) map.get("OUTParam1");
    String msg = (String) map.get("OUTParam2");