Wednesday, September 26, 2007

ORCL- Table Function(2)

From Oracle online docs:

Example: Querying a Table Function

The following example shows a table function GetBooks that takes a CLOB as input and returns an instance of the collection type BookSet_t. The CLOB column stores a catalog listing of books in some format (either proprietary or following a standard such as XML). The table function returns all the catalogs and their corresponding book listings.

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;

Example: Assigning the Result of a Table Function
The following example shows how you can assign the result of a table function to a PL/SQL collection variable. Because the table function is called from the SELECT list of the query, you do not need the TABLE keyword.create type numset_t as table of number;
/

create function f1(x number) return numset_t pipelined is
begin
for i in 1..x loop
pipe row(i);
end loop;
return;
end;
/

-- pipelined function in from clause
select * from table(f1(3));

COLUMN_VALUE
------------
1
2
3

3 rows selected.

-- pipelined function in select list

select f1(3) from dual;

F1(3)
---------------------------------
NUMSET_T(1, 2, 3)

-- Since the function returns a collection, we can assign
-- the result to a PL/SQL variable.
declare
func_result numset_t;
begin
select f1(3) into func_result from dual;
end;
/

No comments:

Post a Comment