Pipelined Table Functions in PLSQL?

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated.

Pipelined table functions include the PIPELINED clause and 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. Notice the empty RETURN call, since there is no collection to return from the function.

-- Build a pipelined table function.

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS

BEGIN

  FOR i IN 1 .. p_rows LOOP

    PIPE ROW(t_tf_row(i, 'Description for ' || i));  

  END LOOP;

  RETURN;

END;

/

-- Test it.

SELECT *

FROM   TABLE(get_tab_ptf(10))

ORDER BY id DESC;

        ID DESCRIPTION

---------- --------------------------------------------------

        10 Description for 10

         9 Description for 9

         8 Description for 8

         7 Description for 7

         6 Description for 6

         5 Description for 5

         4 Description for 4

         3 Description for 3

         2 Description for 2

         1 Description for 1

10 rows selected.

SQL>

Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area.