Difference between table function and pipeline function?

Question

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

The first FN_RET_COL is a regular table function, and the second FN_RET_PIPE is a pipeline function. I studied in a book like Regular Table Functions requires collections to be completely populated before they return to where PIPELINED FUNCTION use the PIPE ROW call to pop rows from a function immediately after they are created, rather than creating a table. saving memory and allowing subsequent processing to run until all rows are created. I doubt that: PIPELINED FUNCTION saves memory? If I'm not mistaken, it concatenates all the lines and stores them in a memory area, and then prints all the lines in the console. Or does it look like it directly prints line by line as soon as a new record is sent to the console without saving 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 this code work?

Answer 1

Pipelined , a very classic example is that you make a SELECT * FROM table name in SQL*Plus . What happens, Oracle passes data from the table.

Like watching videos on YouTube.

Pay attention to the word "Streaming". And in our function, we determine the number of lines that we pass. Each stream line is immediately available to the caller. Pipelining means in layman terms, don't make me wait until you are done, give me what you have, and continue to process and update me at the same time.

In the last procedure, after each line, you initiate a sleep call for 10s , so a record is sent to the caller every 10 seconds.

And the normal table function will continue to wait until all processing is complete, and then it will return a link to the cursor of the result set.

pipelined functions that they claim to save memory are located directly on the flushing content, and therefore the buffer used is always minimal, while the number of round-trip calls becomes higher.