表函数和流水线函数的区别?

2021-12-24 00:00:00 oracle plsql pipelined-function 
CREATE OR REPLACE TYPE ty_1 AS OBJECT ( fn VARCHAR2(100),
                                        sl NUMBER,
                                        hd DATE );
CREATE OR REPLACE TYPE ty_1_table AS TABLE OF ty_1;

CREATE OR REPLACE FUNCTION FN_RET_COL
  RETURN ty_1_table
AS
  c ty_1_table := TY_1_TABLE();
  BEGIN
    c.extend;
    C(1) := TY_1('A', 1, '10-JUN-2013');
    c.extend;
    C(2) := TY_1('B', 2, '11-JUN-2013');
    c.extend;
    C(3) := TY_1('C', 3, '12-JUN-2013');

    RETURN c;
  END;

CREATE OR REPLACE FUNCTION FN_RET_PIPE RETURN ty_1_table PIPELINED IS
  BEGIN
    PIPE ROW (TY_1('A', 1, '10-JUN-2013'));
    PIPE ROW (TY_1('B', 2, '11-JUN-2013'));
    PIPE ROW (TY_1('C', 3, '12-JUN-2013'));
  END;

SELECT * FROM TABLE (fn_ret_col);

SELECT * FROM TABLE (fn_ret_pipe);

第一个FN_RET_COL是正则表函数，第二个FN_RET_PIPE是流水线函数.我在一本类似的书上学习常规表函数要求在返回集合之前完全填充集合，其中作为 PIPELINED FUNCTION使用 PIPE ROW 调用将行在创建后立即推出函数，而不是构建表集合.节省内存并允许在生成所有行之前开始后续处理.我的疑问是:PIPELINED Function 如何节省内存?如果我没记错的话，它正在管道所有行并将它们存储在内存区域中，然后在控制台中打印所有行.或者它就像是在控制台中通过管道传输新记录后直接逐行打印而不将其存储在任何地方?

First one FN_RET_COL is Regular table function and second one FN_RET_PIPE is Pipelined Function. I studied in a book like Regular table functions require collections to be fully populated before they are returned where as PIPELINED FUNCTION use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. saving memory and allowing subsequent processing to start before all the rows are generated. My doubt is : How PIPELINED Function saves memory? If I am not wrong, It is piping all the rows and storing them in a memory area and then printing all the rows in the console. Or is it like, it is directly printing row by row as soon as a new record is piped in the console without storing it anywhere?

CREATE OR REPLACE FUNCTION FN_RET_COL RETURN TY_1_TABLE
 PIPELINED IS
 BEGIN   
    PIPE ROW(TY_1('A',1,'10-JUN-2013'));
    DBMS_LOCK.sleep(seconds => 10);
    PIPE ROW(TY_1('B',2,'11-JUN-2013'));
    DBMS_LOCK.sleep(seconds => 10);
    PIPE ROW(TY_1('C',3,'12-JUN-2013'));
 END;

如果我的第二种情况是正确的，那么上面的代码是如何工作的?

If my second case is right, then how does the above code works?

推荐答案

Pipelined 函数，一个非常经典的例子就是你在 SELECT * FROM table name代码>SQL*Plus.发生的情况是，Oracle 从表中流式传输数据..

Pipelined functions, a very classic example is where you do a SELECT * FROM table name in SQL*Plus. What happens is, Oracle streams the data from the table..

喜欢在 youtube 上观看视频.

Like watching a video in youtube.

请注意Streaming"这个词.在我们的函数中，我们定义了我们流式传输的行数.每个流式传输行都立即可供调用者使用.Pipelining 通俗地说，就是不要让我等你完成，把你有的给我，同时继续处理和更新我.

Please note the word, 'Streaming'.. And in our function we define how many rows we stream.. Every streamed row is immediately available to the caller. Pipelining means in lay man terms, dont make me wait till you complete, give me what ever you have, and keep processing and updating me simultaneously.

在你的最后一个过程中，在管道每一行之后，你启动一个 sleep 调用 10s ，所以记录是 Streamed 到调用者每 10 秒一次.

In your last procedure, after piping every row, you initiate a sleep call for 10s , so the record is Streamed to the caller every 10s.

而且，一个普通的表函数会一直等待，直到所有的处理工作完成，然后它才会返回对结果集游标的引用.

And, a normal table function will keep waiting until all processing work is done, and then it will return the reference to the result set cursor.

流水线函数，他们声称可以节省内存，是通过立即刷新内容，因此使用的缓冲区总是最小的，而往返次数会增加.

pipelined functions , they claim to save memory, is by flushing the content immediately, and hence the buffer being used is always minimal, whereas the round trips count get higher.

相关文章