Skip to content
Advertisement

table in database rows

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>
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement