[Oracle] Pipelined Table Functions

출처 : https://oracle-base.com/articles/misc/pipelined-table-functions

Table Functions

DROP TYPE T_TF_TAB;

DROP TYPE T_TF_ROW;

CREATE TYPE T_TF_ROW AS OBJECT (

  ID           NUMBER,

  DESCRIPTION  VARCHAR2(50)

);

/

CREATE TYPE T_TF_TAB IS TABLE OF T_TF_ROW;

/

-- BUILD THE TABLE FUNCTION ITSELF.

CREATE OR REPLACE FUNCTION GET_TAB_TF (

    P_ROWS IN NUMBER

)

RETURN T_TF_TAB

AS

  L_TAB  T_TF_TAB := T_TF_TAB();

BEGIN

  FOR I IN 1 .. P_ROWS LOOP

    L_TAB.EXTEND;

    L_TAB(L_TAB.LAST) := T_TF_ROW(I, 'DESCRIPTION FOR ' || I);

  END LOOP;

  RETURN L_TAB;

END;

/

-- TEST IT.

SELECT *

FROM   TABLE(GET_TAB_TF(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.

Pipelined Table Functions

-- 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.

NO_DATA_NEEDED Exception

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

    DBMS_OUTPUT.put_line('Row: ' || i);

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

  END LOOP;

  RETURN;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line('OTHERS Handler');

    RAISE;

END;

/

-- Test it.

SELECT *

FROM   TABLE(get_tab_ptf(10))

WHERE  rownum <= 5;

        ID DESCRIPTION

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

         1 Description for 1

         2 Description for 2

         3 Description for 3

         4 Description for 4

         5 Description for 5

5 rows selected.

Row: 1

Row: 2

Row: 3

Row: 4

Row: 5

OTHERS Handler