I have a table People, having people_id,people_name,people_lastname. I am facing difficulties creating a pl/sql function to display all the rows of that table. I have done this much so far
x
create or replace function PeopleTable(
people_id in int,student_name in varchar (255),student_lastname in varchar (255))
return table_People
as cursor curs
is select people_id,people_name,student_lastname from People
var table_People:=table_res();
begin open curs ;
loop
fetch curs
bulk collect into var limit 100;
exit when curs%notfound;
end loop;
close curs;
Advertisement
Answer
One option is to return refcursor:
SQL> create or replace function f_test (par_deptno in number)
2 return sys_refcursor
3 is
4 l_rc sys_refcursor;
5 begin
6 open l_rc for
7 select empno, ename, job, sal
8 from emp
9 where deptno = par_deptno;
10 return l_rc;
11 end;
12 /
Function created.
SQL> select f_test(10) from dual;
F_TEST(10)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7934 MILLER CLERK 1300
SQL>