在 Oracle 中循环遍历 CURSOR 时使用 EXIT WHEN 指令是一种不好的做法吗? [英] Is it a bad practice to use EXIT WHEN instruction when looping through CURSORs in Oracle?

问题描述

这听起来像是一个愚蠢的问题，但我希望我能说得足够清楚.

It may sound like a silly question, but I hope I'll make myself clear enough.

1. 当谈到 Spaghetti Code 时，它的基础是GOTO 的使用.我有一个同行曾经说过如果我在代码末尾放置一个断点并且每次都没有到达这个断点，那么就有问题.
2. 尽管如此，使用 何时退出Oracle 包中的结构(通常后跟 %NOTFOUND测试).

1. When talking about Spaghetti Code, the basis of it is the usage of GOTOs. I had a peer that was used to say if I put a breakpoint at the end of the code and this breakpoint isn't reached everytime, something is wrong.
2. Nevertheless, is a common (and I'd say, a rule) to use EXIT WHEN structures within Oracle packages (usually followed by a %NOTFOUND test).

理所当然地使用 EXIT 会破坏编程流程，这不是 1 和 2 之间不匹配的东西吗?

Taking for granted that using EXIT breaks the programming flow, isn't something that doesn't match between 1 and 2?

是否每个人都在使用 PL/SQL 进行编程?PL/SQL 是否不遵循这种特定的条件模式?

Is everyone programming in PL/SQL following a bad practice? Does PL/SQL don't follow this specific pattern for conditionals?

Oracle 是否有任何性能原因使用此类语句?

Is there any performance reason under Oracle's hood to use such statements?

抱歉，如果这个问题已经被问过，我找不到类似的东西.

Apologies if this question has been already asked, I couldn't find anything similar around.

推荐答案

是的，很多人都在遵循一个不好的做法.

Yes, many people are following a bad practice.

风格不好

我同意@Osy 的观点，即 OPEN/FETCH/CLOSE 添加了完全不必要的代码.我会更进一步，说你几乎不应该使用 CURSOR.

I agree with @Osy that OPEN/FETCH/CLOSE adds completely unnecessary code. I would go even further, and say that you should almost never use CURSOR.

首先，您通常希望尽可能多地使用纯 SQL.如果您需要使用 PL/SQL，请使用隐式游标.它将为您节省一行代码，并帮助您将相关逻辑更紧密地结合在一起.

First of all, you normally want to do as much as possible in plain SQL. If you need to use PL/SQL, use an implicit cursor. It will save you a line of code and will help you keep related logic closer together.

我坚信要使单个代码单元尽可能小.乍一看，CURSOR 似乎可以帮助您做到这一点.你可以在一个地方定义你的 SQL，然后再做 PL/SQL 循环.

I'm a strong believer in keeping individual units of code as small as possible. At first glance, it seems like a CURSOR can help you do this. You can define your SQL up top in one place, and then do the PL/SQL looping later.

但实际上，额外的间接层几乎不值得.有时很多逻辑在 SQL 中，有时很多逻辑在 PL/SQL 中.但在实践中，在两者中加入大量复杂逻辑几乎没有意义.您的代码通常最终看起来像其中之一:

But in reality, that extra layer of indirection is almost never worth it. Sometimes a lot of logic is in SQL, and sometimes a lot of logic is in PL/SQL. But in practice, it rarely makes sense to put a lot of complex logic in both. Your code usually ends up looking like one of these:

for records in (<simple SQL>) loop
    <complex PL/SQL>
end loop;

或:

for records in
(
    <complex SQL>
) loop
    <simple PL/SQL>;
end loop;

无论哪种方式，您的代码部分都会非常小.分离这两个代码部分的复杂性大于更大的单个代码部分的复杂性.(但这显然是我的看法.)

Either way, one of your code sections will be very small. The complexity of separating those two sections of code is greater than the complexity of a larger, single section of code. (But that is obviously my opinion.)

表现不佳

使用 OPEN/FETCH/CLOSE 会对性能产生重大影响.该方法比使用循环游标或隐式游标慢.

There are significant performance implications with using OPEN/FETCH/CLOSE. That method is much slower than using a cursor for loop or an implicit cursor.

编译器可以在某些 for 循环中自动使用批量收集.但是，引用 Oracle 演示文稿 PL/SQL 性能——揭穿神话"，第 122 页:

The compiler can automatically use bulk collect in some for loops. But, to quote from the Oracle presentation "PL/SQL Performance—Debunking the Myths", page 122:

不要通过使用打开、获取循环、关闭表单来放弃这个机会

Don’t throw this chance away by using the open, fetch loop, close form

这是一个简单的例子:

--Sample data
create table t(a number, b number);
insert into t select level, level from dual connect by level <= 100000;
commit;

--OPEN/FETCH/CLOSE
--1.5 seconds
declare
    cursor test_cur is
    select a, b from t;
    test_rec test_cur%rowtype;
    counter number;
begin
    open test_cur;
    loop
        fetch test_cur into test_rec;
        exit when test_cur%notfound;
        counter := counter + 1;
    end loop;
    close test_cur;
end;
/

--Implicit cursor
--0.2 seconds
declare
    counter number;
begin
    for test_rec in (select a, b from t) loop
        counter := counter + 1;
    end loop;
end;
/

这篇关于在 Oracle 中循环遍历 CURSOR 时使用 EXIT WHEN 指令是一种不好的做法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！