oracle 从打包函数返回集合以用于选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7888990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return collection from packaged function for use in select
提问by TownCube
I'm currently using this block of code to return a collection of rows from my function.
我目前正在使用此代码块从我的函数返回行的集合。
--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;
/
Which I call by issuing SQL
我通过发出 SQL 调用
select * from table(return_table);
Object types can not be defined in a package, I tried using the record type which worked (in PL/SQL) but I couldn't select from it in the same way as I can here.
对象类型不能在包中定义，我尝试使用有效的记录类型（在 PL/SQL 中），但我无法像在这里一样选择它。
How do I achieve this result using a function inside a package?
如何使用包内的函数实现此结果？
回答by Vincent Malgrat
You could either use SQL objects inside your package or use pipelined functions (tested with 10gr2). Using SQL objects is straightforward, your actual function could be used as is inside a package.
您可以在包内使用 SQL 对象，也可以使用流水线函数（使用 10gr2 测试）。使用 SQL 对象很简单，您的实际函数可以在包内按原样使用。
Here's how you could use a pipelined function with a RECORD type:
以下是如何使用带有 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
What happens behind the scene is that Oracle understands that since you want to use your function in a query (because of the PIPELINED keyword), you will need SQL objects, so those objects are created behind the scene for you:
在幕后发生的事情是，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));
回答by user272735
create or replace type t_col as object (
i number,
n varchar2(30)
);
/
create or replace package foo as
type t_nested_table is table of t_col;
function return_table return t_nested_table pipelined;
end;
/
show errors
create or replace package body foo as
data t_nested_table := t_nested_table(t_col(1, 'one'),
t_col(2, 'two'),
t_col(3, 'three'));
function return_table return t_nested_table pipelined as
begin
for i in data.first .. data.last loop
pipe row(data(i));
end loop;
return;
end;
end;
/
show errors
column n format a10
select * from table(foo.return_table);
I N
---------- ----------
1 one
2 two
3 three
