oracle - 从打包函数返回集合以在 select 中使用

Question

我当前正在使用此代码块从我的函数返回行集合。

--Source: http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

create or replace type t_col as object (
i number,
n varchar2(30)
);
/
create or replace type t_nested_table as table of t_col;
/
create or replace function return_table return t_nested_table as
  v_ret   t_nested_table;
begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;
/

我通过发出 SQL 来调用

select * from table(return_table);

对象类型不能在包中定义，我尝试使用有效的记录类型(在 PL/SQL 中)，但我无法像在这里一样从它中进行选择。

如何使用包内的函数实现此结果？

Answer 1

您可以在包内使用 SQL 对象，也可以使用管道函数(使用 10gr2 进行测试)。使用 SQL 对象很简单，您的实际函数可以按原样在包内使用。

以下是如何使用具有 RECORD 类型的管道函数:

SQL> CREATE OR REPLACE PACKAGE my_pkg IS
  2     TYPE t_col IS RECORD(
  3        i NUMBER,
  4        n VARCHAR2(30));
  5     TYPE t_nested_table IS TABLE OF t_col;
  6     FUNCTION return_table RETURN t_nested_table PIPELINED;
  7  END my_pkg;
  8  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg IS
  2     FUNCTION return_table RETURN t_nested_table PIPELINED IS
  3        l_row t_col;
  4     BEGIN
  5        l_row.i := 1;
  6        l_row.n := 'one';
  7        PIPE ROW(l_row);
  8        l_row.i := 2;
  9        l_row.n := 'two';
 10        PIPE ROW(l_row);
 11        RETURN;
 12     END;
 13  END my_pkg;
 14  /

Package body created

SQL> select * from table(my_pkg.return_table);

         I N
---------- ------------------------------
         1 one
         2 two

幕后发生的事情是，Oracle 知道，由于您想要在查询中使用函数(由于 PIPELINED 关键字)，因此您将需要 SQL 对象，因此这些对象是在幕后为您创建的:

SQL> select object_name
  2    from user_objects o
  3   where o.created > sysdate - 1
  4     and object_type = 'TYPE';

OBJECT_NAME
--------------------------------------------------------------------------------
SYS_PLSQL_798806_24_1
SYS_PLSQL_798806_DUMMY_1
SYS_PLSQL_798806_9_1

SQL> select text from user_source where name='SYS_PLSQL_798806_9_1';

TEXT
--------------------------------------------------------------------------------
type        SYS_PLSQL_798806_9_1 as object (I NUMBER,
N VARCHAR2(30));