流水线函数调用另一个流水线函数 [英] Pipelined function calling another pipelined function

问题描述

这是一个具有两个流水线函数的程序包:

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;
/

蚂蚁对应的包体:

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;
/

重要的是，在/* start */和/* end */

我可以按以下方式使用该软件包:

I can use the package as follows:

select * from table(tq84_pipelined.go);

这一切都很好，但我希望我可以用more_rows的简单调用替换/* start */和/* end */之间的行.

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.

但是，这显然是不可能的，因为它会产生 PLS-00221:'MORE_ROWS'不是过程或未定义.

However, this is obviously not possible, as it generetes a PLS-00221: 'MORE_ROWS' is not a procedure or is undefined.

所以，我的问题是:真的没有办法缩短循环吗?

So, my question: is there really no way to shortcut the loop?

编辑

很显然，到目前为止，我的问题还不清楚.

Obviously, from the answers so far, my question was not clear.

如前所述，该包装有效.

The package, as stated works.

但是我对标记/* start */和/* end */之间的6条线(即SIX)感到不安.我想用一行替换这些.但是我还没有找到任何办法.

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.

推荐答案

流水线函数的目的是提供TABLE()函数.我认为没有任何方法可以避免这种情况.不幸的是，我们必须将其输出分配给PL/SQL变量.由于

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

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

所以必须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; 
/

我敢肯定，您知道(但为了其他寻求者的利益)，在Oracle 10g中引入了将集合集合在一起的MULTISET UNION语法.

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.

此版本的GO()与原始实现产生的输出相同:

This version of GO() produces the same output as your original implementation:

SQL> select * from table( tq84_pipelined.go)
  2  /

COLUMN_VALUE
-------------------------
Mein
Name
ist
Eugen,
ich
weiss
von
nichts.

8 rows selected.

SQL>

这篇关于流水线函数调用另一个流水线函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！