如何使用Spring调用带有ref游标作为输出参数的存储过程? [英] How to call a stored procedure with ref cursor as an output parameter using Spring?

问题描述

我有一个存储过程，其主体类似于:-

I have a stored procedure which has body like :-

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.

我该如何在Spring中称呼它.我经历了很多google和stackoverflow，但找不到合适的答案.

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

任何人都可以为我提供解决方案.预先感谢.

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

推荐答案

这是我根据

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

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.

调用它:

    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

---

或者，您可以使用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")

到jdbcCall的设置.同样，如果它在其他模式中，则需要添加

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

            .withSchemaName("SCHEMA_NAME")

这篇关于如何使用Spring调用带有ref游标作为输出参数的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！