oracle 流水线函数调用另一个流水线函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2779495/
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
Pipelined function calling another pipelined function
提问by René Nyffenegger
Here's a package with two pipelined functions:
这是一个包含两个流水线函数的包：
create or replace type tq84_line as table of varchar2(25);
/
create or replace package tq84_pipelined as
function more_rows return tq84_line pipelined;
function go return tq84_line pipelined;
end tq84_pipelined;
/
Ant the corresponding package body:
Ant对应的包体：
create or replace package body tq84_pipelined as
function more_rows return tq84_line pipelined is
begin
pipe row('ist');
pipe row('Eugen,');
return;
end more_rows;
function go return tq84_line pipelined is
begin
pipe row('Mein');
pipe row('Name');
/* start */
for next in (
select column_value line from table(more_rows)
)
loop
pipe row(next.line);
end loop;
/* end */
pipe row('ich');
pipe row('weiss');
pipe row('von');
pipe row('nichts.');
end go;
end tq84_pipelined;
/
The important thing is that go sort ofcalls more_rowswith the for next in ...between /* start */and /* end */
重要的是，围棋之类的电话more_rows与for next in ...之间/* start */和/* end */
I can use the package as follows:
我可以按如下方式使用该包：
select * from table(tq84_pipelined.go);
This is all fine and dandy, but I hoped I could replace the lines between /* start */and /* end */with a simple call of more_rows.
这是所有罚款和花花公子，但我希望我能代替之间的界限/* start */，并/* end */用一个简单的调用more_rows。
However, this is obviously not possible, as it generetes a PLS-00221: 'MORE_ROWS' is not a procedure or is undefined.
但是，这显然是不可能的，因为它生成PLS-00221: 'MORE_ROWS' is not a procedure or is undefined。
So, my question: is there really no way to shortcut the loop?
所以，我的问题是：真的没有办法缩短循环吗？
EDIT
编辑
Obviously, from the answers so far, my question was not clear.
显然，从目前的答案来看，我的问题并不清楚。
The package, as stated works.
如前所述，该包有效。
But I am bothered with the 6 (that is: SIX) lines between the markers /* start */and /* end */. I'd like to replace these with one single line. But I havent found any way doing that.
但是我对标记/* start */和/* end */. 我想用一行替换它们。但我还没有找到任何办法做到这一点。
采纳答案by APC
The point of pipelined functions is to feed TABLE() functions. I don't think there is any way to avoid it. Unfortunately we have to assign its output to a PL/SQL variable. We can't assign a pipelined function to a nested table like this nt := more_rows;due to
流水线函数的重点是提供 TABLE() 函数。我认为没有办法避免它。不幸的是，我们必须将其输出分配给一个 PL/SQL 变量。nt := more_rows;由于以下原因，我们无法将流水线函数分配给这样的嵌套表
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
So SELECT ... FROM TABLE()it has to be.
所以SELECT ... FROM TABLE()必须如此。
I have a slightly different solution for your consideration. I don't know whether it solves your underlying problem.
我有一个稍微不同的解决方案供您考虑。我不知道它是否解决了你的根本问题。
create or replace package body tq84_pipelined as
function more_rows return tq84_line pipelined is
begin
pipe row('ist');
pipe row('Eugen,');
return;
end more_rows;
function go return tq84_line pipelined is
nt1 tq84_line;
nt2 tq84_line;
nt3 tq84_line;
nt0 tq84_line;
begin
nt1 := tq84_line('Mein','Name');
select *
bulk collect into nt2
from table(more_rows);
nt3 := tq84_line('ich','weiss','von','nichts.');
nt0 := nt1 multiset union nt2 multiset union nt3;
for i in nt0.first..nt0.last
loop
pipe row(nt0(i));
end loop;
return;
end go;
end tq84_pipelined;
/
As I'm sure you're aware (but for the benefit of other seekers) the MULTISET UNION syntax for glomming collections together was introduced in Oracle 10g.
我相信您知道（但为了其他寻求者的利益）在 Oracle 10g 中引入了用于将集合聚集在一起的 MULTISET UNION 语法。
This version of GO() produces the same output as your original implementation:
此版本的 GO() 产生与原始实现相同的输出：
SQL> select * from table( tq84_pipelined.go)
2 /
COLUMN_VALUE
-------------------------
Mein
Name
ist
Eugen,
ich
weiss
von
nichts.
8 rows selected.
SQL>
回答by Erich Kitzmueller
Try
select column_value line from table(tq84_line.more_rows)i.e. include the package name in the query.
尝试
select column_value line from table(tq84_line.more_rows)ie 在查询中包含包名称。
