为什么在 FOR 循环中使用 CONNECT BY 表达式只执行一次？

Question

我刚刚发现了Oracle中PLSQL和SQL之间某些意外的行为差异。

如果我在SQLDeveloper上运行这个查询，我会得到5个结果：

select level lvl from dual connect by level <=5;

但如果我在SQLDeveloper中运行此语句：

declare
  w_counter number :=0;
begin
  for REC in (select level lvl from dual connect by level <=5)
  loop
    w_counter := w_counter+1;
  end loop;
  dbms_output.put_line('W_COUNTER: '|| w_counter);
end;

变量w_counter以值1结束（奇怪）

但最奇怪的是，如果我将查询封装在子查询中...就像这样：

declare
  w_counter number :=0;
begin
  for REC in (select * from (select level lvl from dual connect by level <=5))
  loop
    w_counter := w_counter+1;
  end loop;
  dbms_output.put_line('W_COUNTER: '|| w_counter);
end;

w_counter变量以值5结束...

对此，你有什么要说的吗？

我正在使用Oracle 9.2i。

- JGS

---

我在Oracle 11g中的两个块中都得到了“5”。不幸的是，我没有9i！ - Maheswaran Ravisankar

听起来像是一个优化器的 Bug… 你可以尝试在没有任何优化的情况下运行这个 PL/SQL 吗？我猜优化器会看到从 dual 中选择，推断出从 dual 中选择只会返回一行，并将循环剥离为单个调用… 只是一个猜测；-) 建议翻译：听起来像是一个优化器的错误。你能否尝试不进行任何优化地运行 PL/SQL？我猜想优化器会看到从“dual”表中选择，推断出从“dual”中选择只会返回一行，并将循环简化为单个调用……只是一个猜测；-) - Falco

我在两个块中都得到了5。我使用的是Oracle 10G。如果我在9.2.0.7.0上尝试这个，我看到的结果和你一样。 - abhi

这是一个漏洞。9.2的首席支持已于2007年到期。这个问题在后来的版本中得到了修复。你应该考虑升级你的服务器——我们现在使用的是11gR2，我还以为我们落后了呢……抱歉，我认为你需要登录Oracle支持才能看到我下面发布的链接。 - kayakpim

请参见asktom.oracle.com上这个帖子：https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:40476301944675#40490066762235。Tom Kyte在其他帖子中解释了9i与10g之间的优化器差别。 - Lord Peter

Answer 1

这是一个错误，发生在Oracle 9i的一个版本中，已确认至9.2.0.8，但不包括此版本之后的版本。

此前在Ask Tom上讨论过此问题，回复简单地说“sqlplus会这样做”。

对于Oracle 9.2的首席支持，于2007-07-31结束，扩展支持于2010-06-30结束。为了修复此问题，建议升级到当前版本的Oracle；如果无法升级，则应将数据库补丁更新至9.2.0.8以上。

- Jon Heller

---

你能添加一个 bug 编号吗？根据前面的评论，这是一个 bug 是相当明显的。 - Guido Leenders

@GuidoLeenders，我查看了但找不到错误编号。如果没有更好的答案，我添加了这个回答来总结评论。我将其标记为社区维基，这样我就不会因为重复其他人说的话而获得任何积分。 - Jon Heller

@jonearles：那么请不要将其发布为答案，这应该是一条评论。 - Patrick Hofman

如果您不喜欢这个答案，请编辑它。这就是为什么它是一个社区维基的原因。所有评论都基本上在说“这是一个漏洞”。即使答案不是很好，这个问题也值得回答。没有人想阅读所有这些评论。我从这个帖子得到了这个想法。 - Jon Heller