如何在PL/SQL存储过程中返回自定义集 [英] How to Return a Custom Set in PL/SQL Stored Procedure

问题描述

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

How can I return a set of data from PL/SQL?

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

I have a stored procedure that needs to run a select statement and returns the result back to the calling (Java) program.

select语句由来自多个表的几个联接组成，因此我不确定如何在存储过程定义或主体中定义此类型.

The select statement comprises of a few joins from multiple tables, so I am not sure how to define this type in the stored procedure definition or body.

我当时想也许可以按照以下步骤来完成，但是SQL Developer给了我错误:

I was thinking maybe this can be done as following, but SQL Developer is giving me errors:

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;

推荐答案

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

Unless you are particularly set on using a collection, it would be simpler to use a ref cursor:

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中执行以下操作:

From JDBC you can then do something like:

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

然后您可以像访问其他任何对象一样遍历结果集.

and you can then iterate over the result set as you would with any other.

您还可以改用一个函数

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);

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

Obviously you need to handle any other parameters you're passing to your real procedure/function.

这篇关于如何在PL/SQL存储过程中返回自定义集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！