Table Functions in PLSQL?

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

-- Create the types to support the table function.

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.

SQL>

Notice the above output is in reverse order because the query includes a descending order by clause.