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

问题描述

1. 当谈到 Spaghetti代码，它的基础是
   使用GOTO。我有一个对等体，用来说如果我在代码的结尾处放置一个断点，这个断点没有达到每次，一些是错误的。


2. 然而，使用 EXIT WHEN
   Oracle软件包中的结构（通常后跟％NOTFOUND
   test）。

$ c> EXIT 打破了编程流程，是不是在1和2之间不匹配？

/ SQL跟着坏习惯？

在Oracle的帽子里有没有使用这样的语句的性能原因？

解决方案

如果这个问题已经被问过，是的，很多人都在跟着一个坏的做法。

不良样式

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

首先，你通常希望尽可能在纯SQL中做。如果需要使用PL / SQL，请使用隐式游标。它会为你保存一行代码，并帮助你保持相关的逻辑更紧密。

我坚信，保持单个代码单元尽可能小。乍一看，看起来像是 CURSOR 可以帮你做到这一点。您可以在一个位置定义您的SQL顶部，然后再执行PL / SQL循环。

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

 用于（< simple SQL& 
< complex PL / SQL> 
 end loop; 
  

或：

 code> 
中的记录（
< complex SQL> 
）loop 
< simple PL / SQL> ;; 
 end loop; 
  

无论哪种方式，你的代码段之一会非常小。分离这两段代码的复杂性大于较大的单段代码的复杂性。 （但这显然是我的意见。）

效果不佳

使用OPEN / FETCH / CLOSE显着性能影响。

编译器可以在一些for循环中自动使用批量收集，而不是使用游标for循环或隐式游标。但是，引用Oracle演示PL / SQL性能 - 解析神话，第122页：

不要使用打开，获取循环，关闭窗体

这是一个快速示例：

   - 示例数据
创建表t（数字，b数字）; 
插入到t选择级别，从双连接级别<= 100000插入; 
 commit; 
 
 --OPEN / FETCH / CLOSE 
 --1.5秒
声明
游标test_cur是
从t中选择a，b; 
 test_rec test_cur％rowtype; 
计数器编号; 
 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; 
 / 
 
  - 隐式游标
 --0.2秒
声明
计数器编号; 
 begin 
 for test_rec in（select a，b from t）loop 
 counter：= counter + 1; 
 end loop; 
 end; 
 / 
  

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

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

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

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

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.

Bad Style

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.

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.

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.

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;

or:

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

Bad Performance

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.

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

Here's a quick example:

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

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