Pages

Monday, November 24, 2014

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

No comments:

Post a Comment