如何在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

除非您特别打算使用集合，否则使用ref游标会更简单：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_REFCURSORAS    l_some_select_sql_resultBEGIN   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);显然，您需要处理传递给实际过程/函数的任何其他参数。

Answer 2

您的类型定义有点混乱。您必须在项目名称之前输入类型定义。TYPE a_collection_records IS RECORD (    NUMBER FIRST_COL,    VARCHAR2 SECOND_COL -- a few others);它应该是TYPE a_collection_records IS RECORD (    FIRST_COL NUMBER,    SECOND_COL VARCHAR2 -- a few others);列的名称位于列的数据类型之前。我希望这是您要寻找的。您总是可以做一个refcursor，但是如果您想要的名称不是您要从中选择的表上的实际列名，那么您仍然会选择哪种记录类型。为了能够将其创建为自定义集，只需在RECORD定义关闭后声明以下代码行 TYPE collection_list IS TABLE a_collection_records;完整的例子：TYPE a_collection_records IS RECORD (    FIRST_COL NUMBER,    SECOND_COL VARCHAR2 -- a few others);TYPE collection_list IS TABLE OF a_collection_records;这将为您提供一组自定义（和列掩码）的数据。