FETCH / FOR之间的区别在PL / SQL中循环CURSOR [英] Difference between FETCH/FOR to loop a CURSOR in PL/SQL

问题描述

我知道抓取游标会让我访问％ROWCOUNT，％ROWTYPE，％FOUND，％NOTFOUND，％ISOPEN

...等变量，

打开 - Fetch - 关闭指令循环游标

而不是

用一个FOR循环循环游标...（在我看来这是更好，因为它很简单）

解决方案

从性能的角度来看，差别比 href =http://www.dba-oracle.com/plsql/t_plsql_cursors.htm =noreferrer> Tim Hall提示OMG小马连接到会暗示。我相信这个提示是一个介绍了一个更大的部分，已被摘录为网络 - 我希望蒂姆继续做的大部分，如果不是所有的这些点在这本书。此外，这整个讨论取决于您使用的Oracle版本。我相信这是正确的10.2，11.1和11.2，但如果你开始回到较旧的版本有一定的区别。

提示中的特定示例，所有，是不现实的。我从来没有见过任何人使用显式游标而不是SELECT INTO来对单行获取进行编码。因此，SELECT INTO更高效的事实是非常有限的实际重要性。如果我们讨论循环，我们感兴趣的性能是获取许多行是多么昂贵。这就是复杂性开始出现的地方。

Oracle引入了从游标到PL / SQL集合中的数据进行BULK COLLECT的能力。这是一种从SQL引擎到PL / SQL集合的更有效的方法，因为它允许您通过一次提取多行来最小化上下文的移位。对这些集合的后续操作更高效，因为您的代码可以保留在PL / SQL引擎中。

但是，为了最大限度地利用BULK COLLECT语法，通常必须使用显式游标，因为这样可以填充PL / SQL集合，然后使用FORALL语法将数据写回数据库（基于合理的假设，如果您在游标中获取一堆数据，则很有可能您正在进行某种操作并在某处保存操作数据） 。如果在FOR循环中使用隐式游标，OMG Ponies会正确地指出，Oracle将在后台执行BULK COLLECT，以使数据获取更便宜。但是你的代码将执行更慢的逐行插入和更新，因为数据不在集合中。显式游标也提供了显式设置LIMIT的机会，它可以在FOR循环中隐式游标的默认值100的情况下提高性能。

一般来说，假设你're on 10.2或更高版本，并且您的代码正在获取数据并将其写回数据库，

最快

1. 显式游标执行BULK COLLECT到本地集合（使用适当的LIMIT），并使用FORALL写回数据库。


2. 隐式游标会在幕后对单行写入回到资料库。


3. 隐藏的游标，不会进行大量收集，也不利用PL / SQL集合。

最慢​​

另一方面，使用隐式游标可以让您在重构旧代码或学习新功能时使用大量操作来获得非常少的前期成本。如果大多数PL / SQL开发是由主要语言是其他东西或不一定跟上新的语言特性的开发者完成的，那么FOR循环比使用所有的语言的显式游标代码更容易理解和维护新的BULK COLLECT功能。当Oracle在将来引入新的优化时，隐式游标代码很可能会自动获得好处，而显式代码可能需要一些手工修改。

当你对性能进行故障排除时，你真正关心你的循环代码可能有多快更快的不同变体，你经常在你想要考虑将更多的逻辑转移到纯SQL，彻底抛弃循环代码。

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

rather than

Loop the cursor with a FOR cycle... (In my opinion this is better becase it is simple)

What do you think?

解决方案

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.

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

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.

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,

Fastest

1. Explicit cursors doing a BULK COLLECT into a local collection (with an appropriate LIMIT) and using FORALL to write back to the database.
2. Implicit cursors doing a BULK COLLECT for you behind the scenes along with single-row writes back to the datbase.
3. Explicit cursors that are not doing a BULK COLLECT and not taking advantage of PL/SQL collections.

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.

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.

这篇关于FETCH / FOR之间的区别在PL / SQL中循环CURSOR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！