为什么在FOR循环中按表达式连接仅执行一次? [英] Why does a connect by expression in a FOR loop, execute only once?

问题描述

我刚刚发现我认为PLSQL与Oracle中的SQL相比有些意外.

I just found what I think is somewhat unexpected behavior in PLSQL vs SQL in Oracle.

如果我在SQLDeveloper上运行此查询，则会得到5个结果:

If I run this query on SQLDeveloper I get 5 results:

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

但是如果我在SQLDeveloper中运行以下语句:

But if i run this statement in 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(很奇怪)结尾

The variable w_counter finishes with value 1 (weird)

但是最奇怪的部分是，如果我将查询封装在子查询中……类似:

but the weirdest part is that if I encapsulate the query in a subquery... something like:

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

The w_counter variable finishes with value 5...

你对此有什么话?

我正在使用Oracle 9.2i

I am using Oracle 9.2i

推荐答案

最多可以确认9.2.0.8 ，但不能超过此值.

It's a bug in a version of Oracle 9i confirmed up to 9.2.0.8, but not beyond.

之前已在).

It was previously discussed on Ask Tom, the response simply being that "sqlplus does that".

对Oracle 9.2的高级支持于2007年7月31日终止，并且扩展了对Oracle 9.2的支持2010-06-30.要解决此问题，建议您升级到当前版本的Oracle.如果无法，则应修补9.2.0.8版以上的数据库.

Premier support for Oracle 9.2 ended on 2007-07-31 and extended support ended on 2010-06-30. To fix this it is recommended that you upgrade to a current version of Oracle; if unable, you should patch your database past version 9.2.0.8.

这篇关于为什么在FOR循环中按表达式连接仅执行一次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！