在 PL/SQL 中循环 CURSOR 的 FETCH/FOR 之间的区别 [英] Difference between FETCH/FOR to loop a CURSOR in PL/SQL
问题描述
我知道获取游标将使我能够访问诸如 %ROWCOUNT、%ROWTYPE、%FOUND、%NOTFOUND、%ISOPEN 之类的变量
I know that fetching a cursor will give me access to variables like %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
而不是
用 FOR 循环循环游标...(在我看来，这更好，因为它很简单)
Loop the cursor with a FOR cycle... (In my opinion this is better becase it is simple)
你怎么看?
推荐答案
从性能的角度来看，区别比 OMG Ponies 链接到的 Tim Hall 提示 暗示.我相信这个技巧是对网络摘录的更大部分的介绍——我希望蒂姆继续在书中提出大部分观点，如果不是全部的话.此外，整个讨论取决于您使用的 Oracle 版本.我相信这对于 10.2、11.1 和 11.2 来说是正确的，但如果您开始使用旧版本，肯定会有不同.
From a performance standpoint, the difference is a lot more complicated than the Tim Hall tip that OMG Ponies linked to would 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.
首先，提示中的特定示例相当不切实际.我从未见过有人使用显式游标而不是 SELECT INTO 对单行提取进行编码.因此，SELECT INTO 更有效这一事实的实际重要性非常有限.如果我们在讨论循环，我们感兴趣的性能是获取多行的成本.这就是复杂性开始出现的地方.
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.
Oracle 在 10.1 中引入了将数据从游标批量收集到 PL/SQL 集合的能力.这是一种将数据从 SQL 引擎获取到 PL/SQL 集合的更有效的方法，因为它允许您通过一次获取多行来最小化上下文转换.对这些集合的后续操作更加高效，因为您的代码可以保留在 PL/SQL 引擎中.
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.
为了最大限度地利用 BULK COLLECT 语法，您通常必须使用显式游标，因为这样您可以填充 PL/SQL 集合，然后使用 FORALL 语法将数据写回数据库(合理的假设是，如果您在游标中获取一堆数据，则很有可能您正在执行某种操作并将操作的数据保存在某处).如果您在 FOR 循环中使用隐式游标，正如 OMG Ponies 正确指出的那样，Oracle 将在幕后进行 BULK COLLECT 以降低获取数据的成本.但是您的代码将执行较慢的逐行插入和更新，因为数据不在集合中.显式游标还提供了显式设置 LIMIT 的机会，这可以提高 FOR 循环中隐式游标的默认值 100 的性能.
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.
一般来说，假设您使用的是 10.2 或更高版本，并且您的代码正在获取数据并将其写回数据库，
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,
最快
- 显式游标对本地集合执行 BULK COLLECT(具有适当的 LIMIT)并使用 FORALL 写回数据库.
- 隐式游标在幕后为您执行批量收集以及单行写回数据库.
- 未执行 BULK COLLECT 且未利用 PL/SQL 集合的显式游标.
最慢
另一方面，在重构旧代码或学习新功能时，使用隐式游标可以让您获得使用批量操作的相当多的好处，而只需很少的前期成本.如果您的大部分 PL/SQL 开发是由主要语言是其他语言或不一定跟上新语言功能的开发人员完成的，那么 FOR 循环将比使用所有这些的显式游标代码更容易理解和维护新的批量收集功能.当 Oracle 将来引入新的优化时，隐式游标代码更有可能自动获得好处，而显式代码可能需要一些手动返工.
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.
当然，当您对性能进行故障排除到您真正关心循环代码的不同变体可能会多快时，您通常会想要考虑移动更多逻辑进入纯 SQL 并完全抛弃循环代码.
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.
这篇关于在 PL/SQL 中循环 CURSOR 的 FETCH/FOR 之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
