游标循环并继续语句:意外行为 [英] cursor loop and continue statement : unexpected behaviour
问题描述
由于最后期限的压力,我可能忽略了某些内容.但是这种行为令我惊讶. 好像游标缓存了100行,而continue语句刷新了缓存 并从获取新缓存的第一条记录开始.
I might be overlooking something due to deadline stress. But this behaviour amazes me. It looks as if the cursor caches 100 rows and the continue statement flushes the cache and begins with the first record of a new cache fetch.
我将其范围缩小为以下脚本:
I narrowed it down to the following script:
drop table test1;
create table test1 (test1_id NUMBER);
begin
for i in 1..300
loop
insert into test1 values (i);
end loop;
end;
/
declare
cursor c_test1 is
select *
from test1;
begin
for c in c_test1
loop
if mod(c.test1_id,10) = 0
then
dbms_output.put_line(c_test1%ROWCOUNT||' '||c.test1_id||' Continue');
continue;
end if;
dbms_output.put_line(c_test1%ROWCOUNT||' '||c.test1_id||' Process');
end loop;
end;
/
1 1 Process
2 2 Process
3 3 Process
4 4 Process
5 5 Process
6 6 Process
7 7 Process
8 8 Process
9 9 Process
10 10 Continue **Where are tes1_id's 11 to 100?**
11 101 Process
12 102 Process
13 103 Process
14 104 Process
15 105 Process
16 106 Process
17 107 Process
18 108 Process
19 109 Process
20 110 Continue **Where are tes1_id's 111 to 200?**
21 201 Process
22 202 Process
23 203 Process
24 204 Process
25 205 Process
26 206 Process
27 207 Process
28 208 Process
29 209 Process
30 210 Continue **Where are tes1_id's 211 to 300?**
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
redhat release 5
2 node RAC
推荐答案
这是一个错误:7306422
It's a bug: 7306422
Pawel Barut写道: http://pbarut.blogspot.com /2009/04/caution-for-loop-and-continue-in-oracle.html
Pawel Barut wrote: http://pbarut.blogspot.com/2009/04/caution-for-loop-and-continue-in-oracle.html
解决方法: SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
Workaround : SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
关于, 罗布
这篇关于游标循环并继续语句:意外行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!