[Oracle SQL] Passing different (multiple) cursors to the same For Loop

Question

Edited for better clarification:

---

Added 1/28/09: I over simplified the code to make it easy to explain, but the select statements are very long and complicated, and the second one is dependent on the first meaning after the first cursor is done and looped through and the inserts are created the second select actually looks at the first inserts as part of the where clause.

This is why I need to use the loop more then once and not combine the selects in any way. I need them to run when I call them in the order I want to call them, which brings me back to my original question is there anyway to re-use a loop with a different cursor?

Thanks again.

---

I am creating a package (Oracle 10) in which I have 4 different select statements (possibly more to come) all of which I create a cursor for and get my data. Now usually I take the data and create a For Loop and all is well.

My problem is that I have 4 different selects but I want to re-use the loop so that I can have cursor c2 utilize the same loop as well as c3, and c4. All of which are cursors getting different information from very different selects but they are all going into the same table with my insert statement in the loop. Also I cannot join all the selects together, they have to be done in order with a commit after each loop

I created an example below with 4 loops but as you can see they are all the same, the only difference is : For r in c1 loop, For r in c2 loop ... I think there must be someway to reuse the loop. I had a few ideas none of which worked.

 cursor c1 is  select info_a, info_b from table_x where info_g = 77; 
 cursor c2 is  select info-a, info_b from table_x where info_g = 88;
 cursor c3 is  select info-a, info_b from table_y where info_j = 88;
 cursor c4 is  select info-a, info_b from table_y where info_j = 99;


  Begin

     For r in c1 loop
        insert into fun_table (good_info, boring_info) values (r.info_a, r.info-b);
     end loop;
    commit;

     For r in c2 loop
        insert into fun_table (good_info, boring_info) values (r.info_a, r.info-b);
     end loop;
    commit;

     For r in c3 loop
        insert into fun_table (good_info, boring_info) values (r.info_a, r.info-b);
     end loop;
    commit;

     For r in c4 loop
        insert into fun_table (good_info, boring_info) values (r.info_a, r.info-b);
     end loop;
    commit;

   end;

Hope this makes more sense and thanks

I edited and then some answers came in.. sorry. The original looked something like this:

 cursor c1 is  select some_info, other_info from some_table where where some_thing = 'xyz'; 
cursor c2 is select some_info, other_info from some_table where where some_thing = 'abc';

   For r in c1 loop
        insert into fun_table (good_info, boring_info) values (r.some_info, r.other_info);
    end loop;

Answer 1

So you are going to have the following select statements:

select some_info, other_info from some_table where some_thing = 'xyz'; 
select c2_info, c2_other from c2_table where c2_thing = 'XYZ;'

You do this by declaring c1 a SYS_REFCURSOR of otherwise unknown type, and make sure all the columns of each query are of the same type (or close to it). You can't use a row type, you'll have to declare the columns individually, and of a generic type that applies to all queries. But the following does work.

DECLARE
  C1 SYS_REFCURSOR;
  TableID NUMBER;
  TableName VARCHAR2(240);
BEGIN

  OPEN C1 FOR select CALENDAR_ID, CALENDAR_NAME from CALENDARS;

  LOOP
    FETCH C1 INTO tableid, tablename;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.put_line ('ID: ' || to_char(tableID) || ' -- NAME: ' || TableName);
  END LOOP;
  CLOSE C1;
  OPEN C1 for SELECT INIT_ID, NAME FROM INITS;  
  LOOP
    FETCH C1 INTO tableid, tablename;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.put_line ('ID: ' || to_char(tableID) || ' -- NAME: ' || TableName);
  END LOOP;
  CLOSE C1;
END;

Answer 2

Just do:

begin
  insert into cool_table 
  ( neat_info 
  , boring_info)
  select some_info
  ,      other_info 
  from some_table 
  where some_thing = 'XYZ';
end;

no cursor loops needed.

Answer 3

Or Just do:

cursor c1 is  
select info_a, info_b from table_x where info_g IN (77, 88) UNION ALL
select info-a, info_b from table_y where info_j IN (88, 99);

Begin

     For r in c1 loop
        insert into fun_table (good_info, boring_info) values (r.info_a, r.info-b);
     end loop;
    commit;

END;

Answer 4

Why not build your cursor query dynamically and only use one cursor?

Where 77, 88 and 99 come from parameters into your stored procedure, I assume.

cursor c1 is  
select info_a, info_b from table_x where info_g in( 77, 88)
UNION
select info-a, info_b from table_y where info_j in (88, 99)
...

Answer 5

Here's an answer that works, if anyone ever need to know how to do this. As given to me by another guy in my office who did some research:

I created a package with 2 procedures The first is the multiple cursors, the second is the loop (I over simplified the selects and insert to just show how it's done)

The first procedure called Select_Cursor :

procedure Select_Cursors is 

  the_cursor sys_refcursor;    -- this defines the_cursor as type sys_refcursor  

begin

 open the_cursor for 

     select  application_idn, account_idn     
      from accounts ac,  applications ha
     where  something = somethingelse

 Insert_Cursor ( the_cursor );  
 close the_cursor;

  open the_cursor for 

     select  application_idn, account_idn     
      from accounts ac,  applications ha
     where  somethingfunny = somethingcrazy

  Insert_Cursor ( the_cursor );  
 close the_cursor;


 ...  repeat for every select

 end Select_Cursors;

The second procedure called Insert_Cursor is:

procedure Insert_Cursor ( p_cursor in sys_refcursor ) is


    begin

       loop
            fetch p_cursor into  application_idn, account_idn ;
            exit when p_cursor%notfound;

            insert into payments (issue_type_des, issued_amt, payment_Type_cde,payment_Status_Cde, created_by, application_idn, account_idn)
                 values          (v_paytype, v_amount, 'S','PE','This Process',  application_idn, account_idn);
       end loop;

       commit;

    end Insert_Cursor;

Thanks again for everyone who gave an answer and looked into the problem, it's appreciated