Home » SQL & PL/SQL » SQL & PL/SQL » More than one recordset in a procedure
icon4.gif  More than one recordset in a procedure [message #237646] Tue, 15 May 2007 05:59 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
Can we return more than one recordset from a procedure using more than one select clause in Oracle.
I read somewhere that we can return each one into a ref cursor.
Please let me know how to do that.

Regards,
Mona

[Updated on: Tue, 15 May 2007 06:05]

Report message to a moderator

Re: More than one recordset in a procedure [message #237651 is a reply to message #237646] Tue, 15 May 2007 06:50 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script:
set serverout on

CREATE PROCEDURE refcurs( empcur  out sys_refcursor
                        , deptcur out sys_refcursor
                        )
is
begin
  open empcur for 'select employee_id from employees where employee_id = 100';
  open deptcur for 'select department_id from departments where rownum < 3';
end;
/
sho err

declare
  TYPE reftype is ref cursor;
  cv_empcur  reftype;
  cv_deptcur reftype;
  
  v_empid  number;
  v_deptid number;
begin
  refcurs(cv_empcur, cv_deptcur);
  loop
    fetch cv_empcur into v_empid;
    exit when cv_empcur%NOTFOUND;
    dbms_output.put_line('employee: '||v_empid);    
  end loop;
  
  loop
    fetch cv_deptcur into v_deptid;
    exit when cv_deptcur%NOTFOUND;
    dbms_output.put_line('department: '||v_deptid);
  end loop;
  close cv_empcur;
  close cv_deptcur;
end;
/

drop procedure refcurs
/


The run:
SQL> @orafaq

Procedure created.

No errors.
employee: 100
department: 10
department: 20

PL/SQL procedure successfully completed.


Procedure dropped.


MHE
Previous Topic: How to use a boolean returned function in a query
Next Topic: Explain Plan
Goto Forum:
  


Current Time: Sat Dec 10 01:12:52 CST 2016

Total time taken to generate the page: 0.13402 seconds