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 type
:
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 write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.