我可以在HSQLDB中使用CURSOR类型的OUT参数吗? [英] Can I have an OUT parameter of type CURSOR in HSQLDB?

问题描述

我有一个Java Web项目，该项目通过存储过程与Oracle数据库对话.当存储过程需要返回RecordSet时，我让存储过程接受SYS_REFCURSOR类型的OUT参数.然后，Java代码遍历记录集以读取值.我正在尝试对DOA层进行单元测试，并且我开始使用HSQLDB来模拟数据库，但是似乎不可能在HSQLDB中使用OUT CURSOR参数在存储的proc中进行操作.我以前从未使用过HSQLDB，因此请原谅我对如何使用它的无知.有人处理过吗?我觉得我的选择是:

1. 使用MS SQL，实际上允许我对存储过程进行选择，而不必使用OUT SYS_REFCURSOR将数据传递回Java代码.我更希望这样，我的公司不会让我使用MS SQL:(.
2. 在PL/SQL中找到一种方法，使存储过程以Java调用者可以使用的方式返回没有OUT参数的记录.
3. 找到HSQLDB的替代方法，使我可以在单元测试期间模拟存储过程，这些存储过程的行为更像Oracle.
4. 不要打扰DOA层的单元测试.看来我将要结束的地方并不重要，因为DOA层中几乎没有在测试中有价值的业务逻辑，而更多的是试图改善我的代码覆盖率统计信息.

作为参考，以下是对存储的proc的典型调用:

 私有静态最终RowMapper< MyRecord>RECORD_ROW_MAPPER =(rs，i)->新的MyRecord(rs.getInteger("OWNER_ID")，rs.getString("NAME")，rs.getString("DESCRIPTION"));私有静态最终SimpleJdbcCall getRecordProcCall = new SimpleJdbcCall(dataSource).withSchemaName("app_data").withProcedureName("getMyRecords").returningResultSet("OUT_RECORD"，RECORD_ROW_MAPPER);公共ArrayList< MyRecord>getMyRecords(int ownerId){SqlParameterSource in = new MapSqlParameterSource().addValue("IN_OWNER_ID"，ownerId);返回getRecordProcCall.executeFunction(ArrayList.class，in);} 

还有我相应的PL/SQL函数:

  CREATE PROCEDURE getMyRecords(IN_OWNER_ID IN NUMBER，OUT_RECORD OUT SYS_REFCURSOR)作为开始OPEN OUT_RECORD FOR选择OWNER_ID，名称，DESCRIPTION从MY_RECORD哪里OWNER_ID = IN_OWNER_ID;结尾 

我知道我可以使用直接SQL来完成这个简单的示例案例，而不使用存储过程，但是在本文中展示我如何访问DB只是很简单的.实际上，我正在做大量的工作，最好在存储过程中完成.

解决方案

从Oracle 12c第1版(12.1)开始，Oracle Database 12c中的隐式语句结果

I have a java web project that talks to an Oracle db through stored procedures. When the stored procedures need to return a RecordSet, I have the stored proc accept an OUT parameter of type SYS_REFCURSOR. Then the Java code iterates over the record set to read the values. I'm trying to Unit test the DOA layer, and I started to use HSQLDB to mock out the database, but it doesn't seem possible to have a stored proc in HSQLDB take an OUT CURSOR parameter. I haven't ever used HSQLDB before, so please forgive my ignorance about how it can be used. Has anyone dealt with this before? I feel like my options are:

1. Use MS SQL that actually allows me to have the Stored Procedure do a select, rather than having to use an OUT SYS_REFCURSOR to pass data back to the Java code. This is more wishing thinking on my part, my company won't let me use MS SQL :(.
2. Find a way in PL/SQL to have a stored procedure return records without an OUT parameter in a way that can be consumed by the Java caller.
3. Find an alternative to HSQLDB that lets me mock out stored procedures during unit-testing which behave more like Oracle.
4. Don't bother unit-testing the DOA layer. This seems like where I'll end up, it's not critical as there is almost no business logic in the DOA layer that has value in testing, it's more of just trying to improve my code coverage statistics.

For reference, here is a typical call to a stored proc:

   private static final RowMapper<MyRecord> RECORD_ROW_MAPPER=
        (rs, i) -> new MyRecord(rs.getInteger("OWNER_ID"),
                                rs.getString("NAME"), 
                                rs.getString("DESCRIPTION"));

   private static final SimpleJdbcCall getRecordProcCall = new SimpleJdbcCall(dataSource)
                              .withSchemaName("app_data")
                              .withProcedureName("getMyRecords")
                              .returningResultSet("OUT_RECORD", RECORD_ROW_MAPPER);

   public ArrayList<MyRecord> getMyRecords(int ownerId) {
      SqlParameterSource in = new MapSqlParameterSource()
                              .addValue("IN_OWNER_ID", ownerId);
      return getRecordProcCall.executeFunction(ArrayList.class, in);
   }

And my corresponding PL/SQL function:

CREATE PROCEDURE getMyRecords (
  IN_OWNER_ID IN  NUMBER,
  OUT_RECORD  OUT SYS_REFCURSOR
)AS
BEGIN
  OPEN OUT_RECORD FOR 
  SELECT OWNER_ID, NAME, DESCRIPTION
  FROM MY_RECORD
  WHERE OWNER_ID = IN_OWNER_ID;
END

I understand that I could do this trivial example case with direct SQL and not use a stored procedure, but it's only trivial to show how I'm accessing the DB in this post. In reality, I'm doing a significant amount of work that's best done within a stored procedure.

解决方案

As of Oracle 12c Release 1 (12.1) the DBMS_SQL package includes the RETURN_RESULT procedure which returns the result of an executed statement to the client application similar to the way MS SQL Server procedures implicitly return record sets.

Tim Hall of the ORACLE-BASE blog has a good posting on Implicit Statement Results in Oracle Database 12c

这篇关于我可以在HSQLDB中使用CURSOR类型的OUT参数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！