Oracle批量收集问题

发布于 2024-09-25 20:47:41 字数 789 浏览 4 评论 0原文

我在批量收集 SQL 时遇到了一些小问题，希望您能帮忙解决。

使用以下代码：

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 行。

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

如果找到的记录少于 1000 条，提取会返回 %notfound 吗？我应该将出口移到循环的末尾吗？（我打算尝试这个，但它在一段代码中很深，需要 3 个小时才能到达故障点。）

提前致谢。

原文

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

With the following code:

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;
/

The cursor c1 returns 53166 rows.

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.

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

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

却一份温柔 2024-10-02 20:47:41

好吧，比我已经做过的更好的谷歌搜索给了我答案，你不应该使用 %notfound 进行限制。

请查看此处了解说明。

回复收藏 0 原文

贵在坚持 2024-10-02 20:47:41

仅供参考，这里有一个使代码正确运行的简单更改：

open c1;
loop
    fetch c1 
    into v_array_cust, v_array_prod
    limit 1000;

    -- the count will be 0 if no rows were found, so this loop will do nothing.
    for i in 1..v_array_cust.count
    loop
        --Do some processing here.
    end loop;

    -- exit now if the last fetch got the last set of rows
    exit when c1%notfound;
end loop;
close c1;

Just for reference, here's one simple change to make the code run correctly:

open c1;
loop
    fetch c1 
    into v_array_cust, v_array_prod
    limit 1000;

    -- the count will be 0 if no rows were found, so this loop will do nothing.
    for i in 1..v_array_cust.count
    loop
        --Do some processing here.
    end loop;

    -- exit now if the last fetch got the last set of rows
    exit when c1%notfound;
end loop;
close c1;

回复收藏 0 原文