sql - How to return multiple rows from oracle stored procedure from multiple cursors?

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

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;