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

Question

如何从PL / SQL返回一组数据？我有一个存储过程，需要运行select语句并将结果返回给调用（Java）程序。select语句由来自多个表的几个联接组成，因此我不确定如何在存储过程定义或主体中定义此类型。我在想也许可以按以下步骤完成，但是SQL Developer给我错误：CREATE OR REPLACE PACKAGE my_packageAS  TYPE a_collection_records IS RECORD (        NUMBER FIRST_COL,        VARCHAR2 SECOND_COL -- a few others  );  -- Procedure API that uses a_collection_records typeEND;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_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);

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

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;

这将为您提供一组自定义（和列掩码）的数据。