当前位置：文江博客话题详情

PL/SQL 中 FETCH/FOR 循环 CURSOR 之间的区别

发布于 2024-09-26 17:35:15 字数 203 浏览 8 评论 0原文

我知道获取游标将使我能够访问 %ROWCOUNT、%ROWTYPE、%FOUND、%NOTFOUND、%ISOPEN 等变量

...但我想知道是否还有其他原因使用

Open - Fetch - Close 指令来循环游标

而不是

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

您觉得怎么样？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪漫人生路 2024-10-03 17:35:15

从性能的角度来看，差异比 OMG Ponies 链接的 Tim Hall 提示复杂得多to 意味着。我相信这篇技巧是对网络上摘录的较大部分的介绍——我希望蒂姆继续在书中阐述大部分（如果不是全部的话）这些观点。此外，整个讨论取决于您使用的 Oracle 版本。我相信这对于 10.2、11.1 和 11.2 来说是正确的，但如果您开始回到旧版本，肯定会有差异。

首先，提示中的具体示例是相当不现实的。我从未见过有人使用显式游标而不是 SELECT INTO 编写单行获取代码。因此，SELECT INTO 更高效这一事实的实际重要性非常有限。如果我们讨论循环，我们感兴趣的性能是获取许多行的成本。这就是复杂性开始出现的地方。Oracle

在 10.1 中引入了将游标中的数据批量收集到 PL/SQL 集合中的功能。这是一种将数据从 SQL 引擎获取到 PL/SQL 集合的更有效方法，因为它允许您通过一次获取多行来最大程度地减少上下文转换。对这些集合的后续操作会更加高效，因为您的代码可以保留在 PL/SQL 引擎中。

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

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

最快

显式游标对本地集合执行 BULK COLLECT（使用适当的 LIMIT ）并使用 FORALL 写回数据库。
隐式游标在幕后为您执行批量收集操作，并将单行写回数据库。
不执行 BULK COLLECT 且不利用 PL/SQL 集合的显式游标。

最慢

另一方面，使用隐式游标可以让您在重构旧代码或学习新功能时以很少的前期成本获得使用批量操作的很大好处。如果您的大部分 PL/SQL 开发是由主要语言是其他语言或者不一定跟上新语言功能的开发人员完成的，那么 FOR 循环将比使用所有新的批量收集功能。当 Oracle 将来引入新的优化时，隐式游标代码更有可能自动获得好处，而显式代码可能需要一些手动返工。

当然，当您对性能进行故障排除并真正关心循环代码的不同变体可能有多快时，您通常会想要考虑将更多逻辑移至纯 SQL 中并完全放弃循环代码。

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

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.

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.

回复收藏 0 原文

感受沵的脚步 2024-10-03 17:35:15

OPEN / FETCH / CLOSE 称为显式游标语法；后者称为隐式游标语法。

您已经注意到的一个关键区别是，您不能在隐式游标中使用 %FOUND/%NOTFOUND/etc...另一件事要注意的是，隐式游标比显式游标更快——它们会预读（~ 100 条记录？）此外不支持显式逻辑。

其他信息：

隐式游标与显式游标

回复收藏 0 原文

爱*していゐ 2024-10-03 17:35:15

除了一个之外，我不知道这两种实现有什么重要的区别： for ... Loop 会在循环完成后隐式关闭游标，而 if open ... fetch ... close 语法您宁愿自己关闭游标（只是一种好方式）- 认为这不是必需的：Oracle 将在可见范围外自动关闭游标。此外，您不能在 for ...loop 游标中使用 %FOUND 和 %NOTFOUND 。

对我来说，我发现 for ...loop 实现更容易阅读和支持。

回复收藏 0 原文

路还长，别太狂 2024-10-03 17:35:15

如果我错了，请纠正我，但我认为两者都有一个很好的功能，而另一个则没有。

使用 for 循环，您可以这样做：

for i in (select * from dual)
  dbms_output.put_line('ffffuuu');
end loop;

使用 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;

因此，使用 open fetch，您可以使用动态游标，但使用 for 循环，您可以定义普通游标而不需要声明。

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 i in (select * from dual)
  dbms_output.put_line('ffffuuu');
end loop;

And with open .. fetch you can do like this:

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.

回复收藏 0 原文

~没有更多了~