How To Return Multiple Rows From Oracle Stored Procedure From Multiple Cursors?

I need to have stored procedure where I can run multiple cursors. Loop over each cursor and then do some operation on each row. This way I will have the desired result from these

Solution 1:

My suggestion is going to be insert the rows from your cursor into a temporary table. Then join the temporary table with your existing table for the filter criteria you mention. Psuedocode:

createor replace function my_func
return sysrefcursor
iscursor cursor_one isSELECT*FROM table_one ; 

    cursor cursor_two isSELECT*FROM table_one ; 
    BEGINFORcurrent_rowin cursor_one
      loop 

          -- do some modification on each row and insert into temporary tableend loop; 



     FORcurrent_rowin cursor_two
      loop 

          -- do some modification on each row and insert into temporary tableend loop; 


    -- results from cursor 1 and 2 exist in temporary tableopen out_cursor forselect t.*from
      my_temp_table t
      join
      my_other_table tt
      on (t.col1 = tt.col1) -- or whatever columns are appropriatewhere t.col2 ='some criteria'-- or whatever filter criteria you like.return out_cursor;

    END;  

Solution 2:

create  type emp_obj AS object 
(
 empno    NUMBER (4)        
,ename  VARCHAR2(10)
,sal      number(7,2)
,job      varchar2(9)
);

CREATE TYPE EMP_NT ASTABLEOF emp_OBJ;


createor replace package test_pkg
IS
TYPE abc_cur isREFCURSOR;

procedure test_proc
(
p_rec INOUT abc_cur
);

END test_pkg;
/createor replace package body test_pkg
ISprocedure test_proc
(
p_rec INOUT abc_cur
)
IS
v_emp_nt emp_nt;
BEGINSELECT emp_obj(empno,ename,sal,job) BULK COLLECTINTO v_emp_nt FROM EMP;

FOR i in v_emp_nt.first..v_emp_nt.last 
LOOP

IF v_emp_nt(i).job='CLERK'THEN 

    v_emp_nt(i).sal := v_emp_nt(i).sal +200;

ELSIF v_emp_nt(i).job='MANAGER'THEN

    v_emp_nt(i).sal := v_emp_nt(i).sal +800;
END IF;

END LOOP;

open p_rec forselect*fromtable(v_emp_nt); 

END test_proc;

END test_pkg;
/

As you have seen the code,what i do ,is to get the desired result in nested table(what your cursor is doing) ,and do some manipulation based on the resultant records ,as well as update the nested table.

At the end i will create a cursor from this updated nested table and return the cursor after opening.

Now your question :How can you return append cursor ?

It is simple create two nested table ,do some manipulation on both the nested table

Suppose you have v_emp_nt1 as first nested table ,you do some manipulation on that . you have another v_emp_nt2 as second nested table ,you do some manipulation on that .

Now your cursor will be like

open p_rec FOR (select*from v_emp_nt1 unionselect*from v_empnt2);

With this way you can achieve your desired output .

**Note:**The above code is for one nested table ,you need to create another nested table for your code to get complete

Solution 3:

create
package my_pkg as

   type my_rec is record
   (
     <list your fields here>
   );

   type my_rec_tab is table of my_rec;

   function get_my_rows
     return my_rec_tab pipelined;

end my_pkg;

create
package body my_pkg asfunction get_my_rows
     return my_rec_tab pipelined
   as
   begin

      for c_cur in (select * from table_one)
      loop

         -- do some modification on the current row andreturn the modified row

         pipe row (c_cur);

      endloop;

      for c_cur in (select * from table_one)
      loop

         -- do some modification on the current row andreturn the modified row

         pipe row (c_cur);

      endloop;

      return;

   end get_my_rows;

end my_pkg;

select * from table(my_pkg.get_my_rows);