使用EXIT WHEN指令在Oracle中通过CURSOR循环时是不好的做法吗? [英] Is it a bad practice to use EXIT WHEN instruction when looping through CURSORs in Oracle?
问题描述
- 当谈到 Spaghetti代码,它的基础是
使用GOTO。我有一个对等体,用来说如果我在代码的结尾处放置一个断点,这个断点没有达到每次,一些是错误的。 - 然而,使用
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.
- 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.
- 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屋!