SQL 将不同的（多个）游标传递给同一个 For 循环

Passing different (multiple) cursors to the same For Loop

提问by

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.

09 年 1 月 28 日添加：为了便于解释，我对代码进行了简化，但是 select 语句非常长且复杂，并且第二个语句依赖于第一个光标完成并循环后的第一个含义创建插入第二个选择实际上将第一个插入视为 where 子句的一部分。

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.

我正在创建一个包 (Oracle 10)，其中有 4 个不同的选择语句（可能还有更多），我为所有这些语句创建了一个游标并获取我的数据。现在通常我会获取数据并创建一个 For 循环，一切都很好。

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

我的问题是我有 4 个不同的选择，但我想重新使用循环，以便我可以让光标 c2 使用相同的循环以及 c3 和 c4。所有这些都是从非常不同的选择中获取不同信息的游标，但它们都与循环中的插入语句一起进入同一个表。此外，我无法将所有选择连接在一起，必须在每次循环后按顺序提交

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.

我在下面创建了一个带有 4 个循环的示例，但正如您所看到的，它们都是相同的，唯一的区别是：c1 循环中的 r，c2 循环中的 r ......我认为必须有某种方式来重用循环。我有一些想法都没有奏效。

 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;

回答by

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;

回答by

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)

我创建了一个包含 2 个程序的包第一个是多个游标，第二个是循环（我过度简化了选择和插入以显示它是如何完成的）

The first procedure called Select_Cursor :

第一个过程称为 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:

称为 Insert_Cursor 的第二个过程是：

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

再次感谢所有给出答案并调查问题的人，不胜感激

回答by Ricardo Villamil

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.

我假设 77、88 和 99 来自参数到您的存储过程中。

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)
...

回答by Thomas Jones-Low

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.

您可以通过将 c1 声明为其他未知类型的 SYS_REFCURSOR 来完成此操作，并确保每个查询的所有列都属于相同类型（或接近它）。您不能使用行类型，您必须单独声明列，并声明适用于所有查询的通用类型。但以下确实有效。

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;

回答by tuinstoel

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.

不需要游标循环。