Returning a Table from a PL/SQL function

1. Create an object type for the record

create or replace type emp_rec_t as object
(
employee_id number,
first_name varchar2(25),
salary number,
manager_id number,
department_id number
);

2. create a SQL table type object
create or replace type emp_tab as table of emp_rec_t;


3. Define a function in the following way.
create or replace
function demo_proc( p_start_row in number,
p_end_row in number )
return emp_tab
as
l_data emp_tab := emp_tab();
l_cnt number default 0;
begin
for x in ( select * from employee order by salary desc )
loop
l_cnt := l_cnt + 1;
if ( l_cnt >= p_start_row )
then
l_data.extend;
l_data(l_data.count) :=
emp_rec_t( x.employee_id,
x.first_name,
x.salary,
x.manager_id,
x.department_id);
end if;
exit when l_cnt = p_end_row;
end loop;

return l_data;
end;



4. you can query the data returned from the function as follows:
select * from the( select cast(demo_proc(2,6) as emp_tab ) from dual ) a;