A PL/SQL function that returnes a nested tablecollection can be programmed so that it returns a «virtual table» that can be queried in an SQL statement like an «ordinary» table.
This is demonstrated in the following example.
Record type
First, we need to 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)
);
/
With the table type, we're ready to create a function.
Note: 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.
The previous function is rather boring in that it returns the same result set each time it is called.
The following function makes that a bit more dynamic. It takes a parameter that specifies the maximum count of records to be returned.
Then, it uses rownum and bulk collect to select a result set into a variable of type t_table and returns it.
create or replace function return_objects (
p_max_num_rows in number
)
return t_table as
v_ret t_table;
begin
select
t_record(rownum, object_name)
bulk collect into
v_ret
from
user_objects
where
rownum <= p_max_num_rows;
return v_ret;
end return_objects;
/