Table Function in Oracle

Table Function:

Table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function like the name of a database table, in the FROM clause of a query, or like a column name in the SELECT list of a query.

A table function can take a collection of rows as input. An input collection parameter can be either a collection type (such as a VARRAY or a PL/SQL table) or a REF CURSOR.

What Are Table Functions?

Table functions return a collection type instance representing rows in a table. They can be queried like a table by calling the function in the FROM clause of a query, enclosed by the TABLE keyword. They can be assigned to a PL/SQL collection variable by calling the function in the SELECT list of a query.

For Example:

The collection type BookSet_t is defined as:

CREATE TYPE Book_t AS OBJECT

( name VARCHAR2(100),

  author VARCHAR2(30),

  abstract VARCHAR2(1000));

CREATE TYPE BookSet_t AS TABLE OF Book_t;

The CLOBs are stored in a table Catalogs:

CREATE TABLE Catalogs

( name VARCHAR2(30),

  cat CLOB);

Function GetBooks is defined as follows:

CREATE FUNCTION GetBooks(a CLOB) RETURN BookSet_t;

The query below returns all the catalogs and their corresponding book listings.

SELECT c.name, Book.name, Book.author, Book.abstract

  FROM Catalogs c, TABLE(GetBooks(c.cat)) Book;

See another Link for Table Function: Link-1 or Link-2 

Pipelined Function:

A pipelined function is one that returns a result set as a collection, but does so iteratively.

Example:

CREATE TYPE pet_t IS OBJECT (

   NAME                          VARCHAR2 (60),

   breed                         VARCHAR2 (100),

   dob                           DATE);

/

CREATE TYPE pet_nt IS TABLE OF pet_t;

/

CREATE OR REPLACE FUNCTION pet_family (dad_in IN pet_t, mom_in IN pet_t)

    RETURN pet_nt

    PIPELINED

IS

    l_count PLS_INTEGER;

    retval   pet_nt := pet_nt ();

BEGIN

    PIPE ROW (dad_in);

    PIPE ROW (mom_in);

    IF mom_in.breed = 'RABBIT'

    THEN

       l_count := 12;

    ELSIF mom_in.breed = 'DOG'

    THEN

       l_count := 4;

    ELSIF mom_in.breed = 'KANGAROO'

    THEN

       l_count := 1;

    END IF;

    FOR indx IN 1 .. l_count

    LOOP

       PIPE ROW (pet_t ('BABY' || indx, mom_in.breed, SYSDATE));

    END LOOP;

    RETURN;

END;

SELECT *

  FROM TABLE (pet_family (

                    pet_t ('Bob', 'KANGAROO', SYSDATE),

                    pet_t ('Sally', 'KANGAROO', SYSDATE)

                 )

              );

OutPut:

NAME     BREED    DOB

Bob      KANGAROO    3/3/2010 11:24:41 AM

Sally    KANGAROO    3/3/2010 11:24:41 AM

BABY1    KANGAROO    3/3/2010 11:24:41 AM