With collections and the table() function, a function can return a table that can be queried in an SQL statement
First,create a record type. In this example, the records consist of two attributes:
i , a number and
n , a varchar2.
The record type is created with
create or replace type t_record as object ( i number, n varchar2(30) ); /
Based on the record type, we can now create a table type.
create or replace type t_table as table of t_record; /
With the table type, we're ready to create a function.
The function returns a t_table.
create or replace function return_table return t_table as v_ret t_table; begin -- -- Call constructor to create the returned -- variable: -- v_ret := t_table(); -- -- Add one record after another to the returned table. -- Note: the "table" must be extended before adding -- another record: -- v_ret.extend; v_ret(v_ret.count) := t_record(1, 'one' ); v_ret.extend; v_ret(v_ret.count) := t_record(2, 'two' ); v_ret.extend; v_ret(v_ret.count) := t_record(3, 'three'); -- -- Return the record: -- return v_ret; end return_table; /
In order to use the function's returned value as a table in a SQL statement, we have to enclose the function within the table() statement. From the SQL's perspective, the
table(...) construct behaves as though it were an actual table.
select * from table(return_table);
Within an SQL statement, the expression
table(collection) is treated like a table:
select * from table(collection);
If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to email@example.com . See your article appearing on the dEexams.com main page and help others to learn.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.