oracle FOR LOOP不会在SYS_REFCURSOR中进行迭代 [英] oracle FOR LOOP does not iterate in SYS_REFCURSOR

问题描述

这是程序:

1. 打开游标，然后通过批量收集获取选择查询的输出.
2. 问题是所有ID都存储在批量收集中，但是我无法使用批量收集变量作为输入来遍历第二个选择查询，它只考虑第一个ID而不是全部考虑.
3. 输出应为SYS_REFCURSOR，请说明我在这里缺少什么

表1的测试数据:

<身体>

ID	货币	T_ID
10	GBP	PB1
15	GBP	RB
20	GBP	CC
25	AUD	DC

基于t_id，我正在获取相应的ID，然后在进一步选择for循环语句时使用这些ID.

通过SYS_REFCURSOR进行的过程的当前输出:

<身体>

ID	国家/地区	帐户
10	英国	PB1

期望的输出:

<身体>

ID	国家/地区	帐户
10	英国	PB1
15	威尔士	RB
20	SH	CC

 创建或替换过程myproc(varchar2中的i_id，sys_refcursor退出rc)作为游标names_cur是从table1中选择id，其中currency ='GBP'和t_id = i_id；names_t names_cur％ROWTYPE;names_ntt类型是names_t％TYPE表；l_names names_ntt;开始打开names_cur;提取names_cur批量收集到l_names中；-内部l_names(10,15& 20)将被存储关闭names_cur;--iSSUE的吸引力不足用于l_names.first..l_names.last循环中的cur为以下内容打开rc-对于下面的选项，我想迭代l_names，因此对于上述情况，它应该迭代三次从table2中选择s.id，s.country，s.account，其中s.id = l_names(cur).id;结束循环；结束myproc; 

解决方案

注意以下扩展注释:

问题的中心也许是对游标是什么的误解.它不是一个充满记录的容器，而是某个时间点基于单个SQL查询的结果集规范.所以如果你

 打开rc以从table1中选择ID； 

并将 rc 传递回调用者，您没有传递任何数据，而是传递了一个指向包含准备查询的私有存储区的指针.您不推送结果，调用者将其拉出.就像调用者将执行以获取行的程序一样.您再也不能打开它来添加另一行，我想这就是您希望执行的操作.

---

要在过程中的游标中使用集合，必须将集合类型创建为单独的架构对象(尽管您当然可以在其他过程中重用集合类型，所以它听起来并不那么严格)./p>

如果无法创建类型，请查看可以使用的类型:

 选择所有者，类型名称来自all_coll_types t其中t.coll_type ='TABLE'和t.elem_type_name ='NUMBER'; 

例如:

 创建或替换number_tt类型作为数字表；创建表table1(id主键，货币，t_id)为从双并集全部中选择10，'GBP'，'PB1'从双并集全部中选择15，'GBP'，'RB'从双联合中全部选择20，'GBP'，'CC'从双中选择25，"AUD"，"DC"；创建表table2(id，country，account)为从双并集全部中选择10，"UK"，"PB1"从双重联合中选择15，"Wales"，"RB"从双中选择20，"SH"，"CC"； 

现在过程可以是:

 创建或替换过程myproc(rc out sys_refcursor)作为l_names number_tt;开始选择ID批量收集到l_names中来自表1其中Currency ='GBP';为...打开rc从表2中选择t.id，t.country，t.account其中t.id是l_names的成员；结束myproc; 

光标输出:

  ID COUNT ACC---------- ----- ---10英国PB115威尔士RB20上海 

(由于不清楚我想如何使用它，因此我在您的过程中删除了 i_id 参数.)

大概这是实际问题的简化版本，因为按目前的情况，您可以将第一个查询用作子查询，并且不需要集合:

 创建或替换过程myproc(rc out sys_refcursor)作为开始为...打开rc从表2中选择t.id，t.country，t.account在哪里(选择编号来自表1其中currency ='GBP');结束myproc; 

或者按照Littlefoot的建议加入它:

 创建或替换过程myproc(rc out sys_refcursor)作为开始为...打开rc选择t2.id，t2.country，t2.account从table1 t1在t2.id = t1.id上加入table2 t2其中t1.currency ='GBP';结束myproc; 

但是，您评论了该答案，您无法做到这一点，因为您的要求似乎是通过收集，回路，一些胶带，两只猫和一个融合发生器来完成的.

Here is the Procedure:

1. Opening a cursor and then fetching the output of select query through bulk collect.
2. Issue is all the ID's are getting stored in bulk collect but I am unable to loop through the second select query by using the bulk collect variable as input, It only takes first ID into consideration instead of all.
3. OUTPUT should be a SYS_REFCURSOR, please shed light on what am I missing here

test data for table1:

ID	CURRENCY	T_ID
10	GBP	PB1
15	GBP	RB
20	GBP	CC
25	AUD	DC

Based on the t_id I am fetching the corresponding ID's and then using those ID's in further select for loop statements.

CURRENT OUPUT OF THE PROC THROUGH SYS_REFCURSOR:

ID	COUNTRY	ACCOUNT
10	UK	PB1

EXPECTED OUTPUT:

ID	COUNTRY	ACCOUNT
10	UK	PB1
15	Wales	RB
20	SH	CC

create or replace procedure myproc (i_id in varchar2, rc out sys_refcursor)
as
    cursor names_cur is
        select id from table1 where currency='GBP' and t_id=i_id;
    names_t names_cur%ROWTYPE;
    type names_ntt is table of names_t%TYPE;
    l_names names_ntt;
begin
    open names_cur;
    fetch names_cur bulk collect into l_names ; --Inside l_names (10,15 & 20) would be stored 
    close names_cur;
--iSSUE IS WITH BELOW FOR LOOP
    for cur in l_names.first..l_names.last loop
        open rc for --For the below select I want to iterate l_names so for the above scenario it should iterate thrice

        select s.id,s.country,s.account from table2 s where s.id=l_names(cur).id;
    end loop;

end myproc;

解决方案

Note following extended comments:

Perhaps at the centre of the question is a misunderstanding of what a cursor is. It's not a container full of records, it's a specification for a result set, as at a point in time, based on a single SQL query. So if you

open rc for select id from table1;

and pass rc back to the caller, you are not passing any data, you are passing a pointer to a private memory area containing a prepared query. You don't push the results, the caller pulls them. It's like a program that the caller will execute to fetch the rows. You can't open it a bit more to add another row, which I think is what you were hoping to do.

---

To use a collection in a cursor within a procedure, the collection type has to be created as a separate schema object (though of course you can reuse collection types in other procedures, so it's not as restrictive as it sounds).

If you can't create a type, see what types already exist that you can use:

select owner, type_name
from   all_coll_types t
where  t.coll_type = 'TABLE'
and    t.elem_type_name = 'NUMBER';

For example:

create or replace type number_tt as table of number;

create table table1 (id primary key, currency, t_id) as
    select 10, 'GBP', 'PB1' from dual union all
    select 15, 'GBP', 'RB' from dual union all
    select 20, 'GBP', 'CC' from dual union all
    select 25, 'AUD', 'DC' from dual;

create table table2 (id,country,account) as
    select 10, 'UK', 'PB1' from dual union all
    select 15, 'Wales', 'RB' from dual union all
    select 20, 'SH', 'CC' from dual;

Now the procedure can be:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
    l_names number_tt;
begin
    select id bulk collect into l_names
    from   table1
    where  currency = 'GBP';

    open rc for
        select t.id,t.country,t.account from table2 t
        where  t.id member of l_names;
end myproc;

Cursor output:

        ID COUNT ACC
---------- ----- ---
        10 UK    PB1
        15 Wales RB
        20 SH    CC

(I removed the i_id parameter in your procedure as I wasn't clear how you wanted to use it.)

Presumably this is a simplified version of the actual issue, because as it stands you could use the first query as a subquery and you wouldn't need the collection:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
begin
    open rc for
        select t.id,t.country,t.account from table2 t
        where  t.id in
               ( select id 
                 from   table1
                 where  currency = 'GBP' );
end myproc;

or just join it, as Littlefoot suggested:

create or replace procedure myproc
    ( rc out sys_refcursor)
as
begin
    open rc for
        select t2.id, t2.country, t2.account
        from   table1 t1
               join table2 t2 on t2.id = t1.id
        where  t1.currency = 'GBP';
end myproc;

However, you commented on that answer that you couldn't do that because your requirement seemed to be to do it via a collection, a loop, some duct tape, two cats and a fusion generator.

这篇关于oracle FOR LOOP不会在SYS_REFCURSOR中进行迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！