How to return multiple rows from oracle stored pro

Question

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 cursors. Result of such multiple cursors then needs to be union with some other rows and then filtered out and return those rows finally from the proc.

Please note that each cusror and another queries will have same columns.

I am not sure how to do this in the oracle.

Please help me out.

        create or replace PROCEDURE test_proc
    (
      -- some inputs 
      hc_cursor OUT SYS_REFCURSOR
    ) 

    IS 

    cursor cursor_one is 
        SELECT * FROM table_one ; 

    BEGIN    

     FOR current_row in cursor_one
      loop 

          -- do some modification on each row and return each modified row

      end loop; 

    cursor cursor_two is 
        SELECT * FROM table_one ; 

    BEGIN    

     FOR current_row in cursor_two
      loop 

          -- do some modification on each row and return each modified row
          -- append to result from first cursor 

      end loop; 


    -- union results from both these cusrors with some another query 
    -- now filter these records on some criterais 
    -- return finally

    END;    

Answer 1

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 as

   function 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 and return the modified row

         pipe row (c_cur);

      end loop;

      for c_cur in (select * from table_one)
      loop

         -- do some modification on the current row and return the modified row

         pipe row (c_cur);

      end loop;

      return;

   end get_my_rows;

end my_pkg;

select * from table(my_pkg.get_my_rows);

Answer 2

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:

create or replace function my_func
return sysrefcursor
is
    cursor cursor_one is 
        SELECT * FROM table_one ; 

    cursor cursor_two is 
        SELECT * FROM table_one ; 
    BEGIN    

     FOR current_row in cursor_one
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 



     FOR current_row in cursor_two
      loop 

          -- do some modification on each row and insert into temporary table

      end loop; 


    -- results from cursor 1 and 2 exist in temporary table

    open out_cursor for
     select t.* from
      my_temp_table t
      join
      my_other_table tt
      on (t.col1 = tt.col1) -- or whatever columns are appropriate
      where t.col2 = 'some criteria' -- or whatever filter criteria you like.

    return out_cursor;

    END;  

Answer 3

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

CREATE TYPE EMP_NT AS TABLE OF emp_OBJ;


create or replace package test_pkg
IS
TYPE abc_cur is REF CURSOR;

procedure test_proc
(
p_rec IN OUT abc_cur
);

END test_pkg;
/

create or replace package body test_pkg
IS 
procedure test_proc
(
p_rec IN OUT abc_cur
)
IS
v_emp_nt emp_nt;
BEGIN

SELECT emp_obj(empno,ename,sal,job) BULK COLLECT INTO 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 for select * from table(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 union select * 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