Oracle批量收集问题 [英] Oracle Bulk Collect issue

问题描述

我遇到了一个我希望您能提供帮助的批量收集sql的小问题.

I'm having a little issue with a piece of bulk collect sql that I was hoping you could help out with.

使用以下代码:

declare
    cursor c1
    is
    select customer,product
    from products;

    type type_cust is table of products.customer%type;
    type type_prod is table of products.product%type;

    v_array_cust    type_cust;
    v_array_prod    type_prod;
begin
    open c1;
    loop
        fetch c1 
        into v_array_cust, v_array_prod
        limit 1000;

        exit when c1%notfound;

        for i in 1..v_array_cust.count
        loop
            --Do some processing here.
        end loop;
    end loop;
end;
/

游标c1返回53166行.

The cursor c1 returns 53166 rows.

但是，该代码处理53000行，然后结束.似乎在检索最后的166条记录时，会出现某种故障.

However, the code process 53000 rows and then ends. It seems that when going to retrieve the last 166 records there is some sort of failure.

如果找到的记录少于1000条，提取是否会返回％notfound?我应该将出口移到循环的末尾吗? (我将尝试这种方法，但是它深入到一段代码中，需要3个小时才能到达故障点.)

Will the fetch return %notfound if it find's less than 1000 records? Should I move the exit to the end of the loop? (I am going to try this but it is deep in a piece of code that take 3 hours to get to the failure point.)

谢谢.

推荐答案

好吧，比我已经做过的更好的谷歌搜索给了我答案，你不应该使用带有限制的％notfound.

OK, well a better bit of googling than I'd already done gave me the answer you shouldn't use %notfound with limit.

在此处进行检查解释.

这篇关于Oracle批量收集问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！