在 PL/SQL 中循环 CURSOR 的 FETCH/FOR 之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3885469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between FETCH/FOR to loop a CURSOR in PL/SQL
提问by Jimmy
I know that fetching a cursor will give me access to variables like %ROWCOUNT, %ROWTYPE, %FOUND, %NOTFOUND, %ISOPEN
我知道获取游标将使我能够访问诸如 %ROWCOUNT、%ROWTYPE、%FOUND、%NOTFOUND、%ISOPEN 之类的变量
...but I was wondering if there are any other reasons to use
...但我想知道是否还有其他使用原因
Open - Fetch - Close instructions to loop a cursor
打开 - 获取 - 关闭指令以循环游标
rather than
而不是
Loop the cursor with a FOR cycle... (In my opinion this is better becase it is simple)
用 FOR 循环循环光标......（我认为这更好，因为它很简单）
What do you think?
你怎么认为？
回答by Justin Cave
From a performance standpoint, the difference is a lot more complicated than the Tim Hall tip that OMG Ponies linked towould imply. I believe that this tip is an introduction to a larger section that has been excerpted for the web-- I expect that Tim went on to make most if not all of these points in the book. Additionally, this entire discussion depends on the Oracle version you're using. I believe this is correct for 10.2, 11.1, and 11.2 but there are definitely differences if you start going back to older releases.
从性能的角度来看，这种差异比OMG Ponies所暗示的Tim Hall 提示要复杂得多。我相信这个技巧是对为网络摘录的更大部分的介绍——我希望蒂姆继续在书中提出大部分观点，如果不是全部的话。此外，整个讨论取决于您使用的 Oracle 版本。我相信这对于 10.2、11.1 和 11.2 来说是正确的，但是如果您开始回到旧版本，肯定会有不同。
The particular example in the tip, first of all, is rather unrealistic. I've never seen anyone code a single-row fetch using an explicit cursor rather than a SELECT INTO. So the fact that SELECT INTO is more efficient is of very limited practical importance. If we're discussing loops, the performance we're interested in is how expensive it is to fetch many rows. And that's where the complexity starts to come in.
首先，提示中的特定示例相当不切实际。我从未见过有人使用显式游标而不是 SELECT INTO 对单行提取进行编码。因此，SELECT INTO 更有效这一事实的实际重要性非常有限。如果我们在讨论循环，我们感兴趣的性能是获取多行的成本。这就是复杂性开始出现的地方。
Oracle introduced the ability to do a BULK COLLECT of data from a cursor into a PL/SQL collection in 10.1. This is a much more efficient way to get data from the SQL engine to the PL/SQL collection because it allows you to minimize context shifts by fetching many rows at once. And subsequent operations on those collections are more efficient because your code can stay within the PL/SQL engine.
Oracle 在 10.1 中引入了将数据从游标批量收集到 PL/SQL 集合的能力。这是一种将数据从 SQL 引擎获取到 PL/SQL 集合的更有效的方法，因为它允许您通过一次获取多行来最小化上下文转换。对这些集合的后续操作更加高效，因为您的代码可以保留在 PL/SQL 引擎中。
In order to take maximum advantage of the BULK COLLECT syntax, though, you generally have to use explicit cursors because that way you can populate a PL/SQL collection and then subsequently use the FORALL syntax to write the data back to the database (on the reasonable assumption that if you are fetching a bunch of data in a cursor, there is a strong probability that you are doing some sort of manipulation and saving the manipulated data somewhere). If you use an implicit cursor in a FOR loop, as OMG Ponies correctly points out, Oracle will be doing a BULK COLLECT behind the scenes to make the fetching of the data less expensive. But your code will be doing slower row-by-row inserts and updates because the data is not in a collection. Explicit cursors also offer the opportunity to set the LIMIT explicitly which can improve performance over the default of 100 for an implicit cursor in a FOR loop.
但是，为了最大限度地利用 BULK COLLECT 语法，您通常必须使用显式游标，因为这样可以填充 PL/SQL 集合，然后随后使用 FORALL 语法将数据写回数据库（在合理的假设是，如果您在游标中获取一堆数据，则很有可能您正在进行某种操作并将操作的数据保存在某处）。如果您在 FOR 循环中使用隐式游标，正如 OMG Ponies 正确指出的那样，Oracle 将在幕后进行 BULK COLLECT 以降低获取数据的成本。但是您的代码将执行较慢的逐行插入和更新，因为数据不在集合中。
In general, assuming that you're on 10.2 or greater and that your code is fetching data and writing it back to the database,
一般来说，假设您使用的是 10.2 或更高版本，并且您的代码正在获取数据并将其写回数据库，
Fastest
最快的
- Explicit cursors doing a BULK COLLECT into a local collection (with an appropriate LIMIT) and using FORALL to write back to the database.
- Implicit cursors doing a BULK COLLECT for you behind the scenes along with single-row writes back to the datbase.
- Explicit cursors that are not doing a BULK COLLECT and not taking advantage of PL/SQL collections.
- 显式游标对本地集合执行 BULK COLLECT（具有适当的 LIMIT）并使用 FORALL 写回数据库。
- 隐式游标在幕后为您执行 BULK COLLECT 以及单行写回数据库。
- 不执行 BULK COLLECT 并且不利用 PL/SQL 集合的显式游标。
Slowest
最慢
On the other hand, using implicit cursors gets you quite a bit of the benefit of using bulk operations for very little of the upfront cost in refactoring old code or learning the new feature. If most of your PL/SQL development is done by developers whose primary language is something else or who don't necessarily keep up with new language features, FOR loops are going to be easier to understand and maintain than explicit cursor code that used all the new BULK COLLECT functionality. And when Oracle introduces new optimizations in the future, it's far more likely that the implicit cursor code would get the benefit automatically while the explicit code may require some manual rework.
另一方面，在重构旧代码或学习新功能时，使用隐式游标可以让您获得使用批量操作的相当多的好处，而只需很少的前期成本。如果您的大部分 PL/SQL 开发是由主要语言是其他语言或不一定跟上新语言特性的开发人员完成的，那么 FOR 循环将比使用所有这些的显式游标代码更容易理解和维护新的批量收集功能。当 Oracle 将来引入新的优化时，隐式游标代码更有可能自动获得好处，而显式代码可能需要一些手动返工。
Of course, by the time you're troubleshooting performance to the point where you really care about how much faster different variants of your looping code might be, you're often at the point where you would want to consider moving more logic into pure SQL and ditching the looping code entirely.
当然，当您对性能进行故障排除到您真正关心循环代码的不同变体可能有多快时，您通常会想要考虑将更多逻辑移动到纯 SQL 中并完全抛弃循环代码。
回答by OMG Ponies
The OPEN / FETCH / CLOSE is called explicit cursor syntax; the latter is called implicit cursor syntax.
OPEN/FETCH/CLOSE 被称为显式游标语法；后者称为隐式游标语法。
One key difference you've already noticed is that you can't use %FOUND/%NOTFOUND/etc in implicit cursors... Another thing to be aware of is that implicit cursors are faster than explicit ones--they read ahead (~100 records?) besides not supporting the explicit logic.
您已经注意到的一个主要区别是您不能在隐式游标中使用 %FOUND/%NOTFOUND/etc... 另一件需要注意的事情是隐式游标比显式游标更快——它们会提前阅读（~ 100 条记录？）除了不支持显式逻辑。
Additional info:
附加信息：
回答by andr
I don't know about any crucial differences in this two realizations besides one: for ... loopimplicitly closes the cursor after the loop is finished and if open ... fetch ... closesyntax you'd rather close the cursor yourself (just a good manner) - thought this is not a necessity: Oracle will close the cursor automatically outbound the visibility scope. Also you can't use %FOUNDand %NOTFOUNDin for ... loopcursors.
除了一个之外，我不知道这两种实现中的任何重要区别：for ... loop在循环完成后隐式关闭游标，如果open ... fetch ... close语法你宁愿自己关闭游标（只是一种好方法） - 认为这不是必需的： Oracle 会自动关闭游标出站的可见性范围。你也不能在游标中使用%FOUND和。%NOTFOUNDfor ... loop
As for me I find the for ... looprealization much easier to read and support.
至于我，我发现for ... loop实现更容易阅读和支持。
回答by Jokke Heikkil?
Correct me if I'm wrong but I think both have one nice feature what other one doesn't have.
如果我错了，请纠正我，但我认为两者都有一个不错的功能，而另一个则没有。
With for loop you can do like this:
使用 for 循环，您可以这样做：
for i in (select * from dual)
dbms_output.put_line('ffffuuu');
end loop;
And with open .. fetch you can do like this:
使用 open .. fetch 你可以这样做：
declare
cur sys_refcursor;
tmp dual.dummy%type;
begin
open cur for 'select dummy from dual';
loop
fetch cur into tmp;
exit when cur%notfound;
dbms_output.put_line('ffffuuu');
end loop;
close cur;
end;
So with open fetch you can use dynamic cursors but with for loop you can define normal cursor without declaration.
因此，使用 open fetch 您可以使用动态游标，但使用 for 循环您可以定义普通游标而无需声明。
