PL/SQL Procedure Returning Ref Cursor Example

Oracle 9i Introduced in-built Ref cursors sys_refcursor, Independent procedure/function can return data in terms of ref cursors. Application programmers can consume ref cursor data in terms of ResultSets. Note:Unlike Static cursors Ref cursors are used for dynamic queies.

Creating a Procedure which returns Ref cursors as Output Parameter.

   CREATE OR REPLACE Procedure REFSP(p_orders OUT sys_refcursor)
  as
  begin
  open p_orders for select * from pearson.orders;

 end;

 Calling a Procedure Returning Ref Cursor.

declare
v_order spathi.orders%rowtype;
v_orders sys_refcursor;
begin
refsp(v_orders);
loop
fetch v_orders into v_order;
exit when v_orders%notfound;
dbms_output.put_line(v_order.ORDERID||' '||
v_order.CUSTOMERID||' '||
v_order.EMPLOYEEID||' '||
v_order.ORDERDATE||' '||
v_order.REQUIREDDATE||' '||
v_order.SHIPPEDDATE||' '||
v_order.SHIPVIA||' '||
v_order.FREIGHT||' '||
v_order.SHIPNAME||' '||
v_order.SHIPADDRESS||' '||
v_order.SHIPCITY||' '||
v_order.SHIPREGION||' '||
v_order.SHIPPOSTALCODE||' '||
v_order.SHIPCOUNTRY
);
end loop;
end;

OUTPUT:

11066 WHITC 7 01-MAY-98 29-MAY-98 04-MAY-98 2 44.72 White Clover Markets 1029 - 12th Ave. S. Seattle
WA 98124 USA
11067 DRACD 1 04-MAY-98 18-MAY-98 06-MAY-98 2 7.98 Drachenblut Delikatessen Walserweg 21 Aachen
52066 Germany
11068 QUEEN 8 04-MAY-98 01-JUN-98  2 81.75 Queen Cozinha Alameda dos Canαrios, 891 Sao Paulo SP
05487-020 Brazil
11069 TORTU 1 04-MAY-98 01-JUN-98 06-MAY-98 2 15.67 Tortuga Restaurante Avda. Azteca 123 MΘxico D.F.

Tags:   Function Returning Ref Cursor Example,Sys_refcursor example, using Sys_refcursor in functions,

Creating a function which returns Ref cursor, Creating a Independent function which returns Ref cursor,Declaring Ref cursor, Calling a function Returning Ref Cursor.