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
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>