java - 如何在 PL/SQL 存储过程中返回自定义集

Question

如何从 PL/SQL 返回一组数据？

我有一个存储过程，需要运行 select 语句并将结果返回给调用 (Java) 程序。

select 语句包含来自多个表的一些联接，因此我不确定如何在存储过程定义或主体中定义此类型。

我想也许可以按如下方式完成，但是 SQL Developer 给了我错误:

CREATE OR REPLACE PACKAGE my_package
AS
  TYPE a_collection_records IS RECORD (
        NUMBER FIRST_COL,
        VARCHAR2 SECOND_COL -- a few others
  );
  -- Procedure API that uses a_collection_records type
END;

CREATE OR REPLACE PROCEDURE sample_procedure( 
   p_some_select_sql_result OUT my_package.a_collection_records 
) 
AS 
BEGIN
  -- Populate p_some_select_sql_result with some select data 
END;

Answer 1

除非您特别喜欢使用集合，否则使用引用游标会更简单:

CREATE OR REPLACE PROCEDURE sample_procedure ( 
   p_some_select_sql_result OUT SYS_REFCURSOR 
) 
AS 
BEGIN
   OPEN p_some_select_sql_result FOR
      SELECT ...
      FROM ...
      JOIN ...
      ... etc.;
END;
/

通过 JDBC，您可以执行以下操作:

cStmt = conn.prepareCall('{ call sample_procedure(?) }');
cStmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cStmt.execute();
rSet = cStmt.getCursor(1);

然后您可以像处理任何其他结果集一样迭代结果集。

您也可以使用函数来代替:

CREATE OR REPLACE FUNCTION sample_function RETURN SYS_REFCURSOR
AS 
   l_some_select_sql_result
BEGIN
   OPEN l_some_select_sql_result FOR
      SELECT ...
      FROM ...
      JOIN ...
      ... etc.;

   RETURN l_some_select_sql_result;
END;
/

和

cStmt = conn.prepareCall('{ ?=call sample_function }');
cStmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cStmt.execute();
rSet = cStmt.getCursor(1);

显然，您需要处理传递给实际过程/函数的任何其他参数。