注意exit when mycur%notfound; 放置的位置不同,输出结果也不同。
第一个例子中循环在 it is over 提示后,又重复输出了 owner is SYS,vid is 3;这是因为当提取到游标结果集的最后一行之后,不再提取其他的行。所以变量 vid ,vowner 不会被改变,当再次输出这些变量的语句时,就会重复输出了
declare
vid t.object_id%type;
vowner t.owner%type;
vnum integer;
cursor mycur is select object_id ,owner from t where rownum begin
-- select object_id ,owner into vid,vowner from t where rownum =2;
open mycur;
loop
fetch mycur into vid,vowner;
if vnum = 6 then
dbms_output.put_line('it is over');
vnum :=vnum+1;
end if;
vnum :=mycur%rowcount;
dbms_output.put_line(vnum||' owner is '||vowner||','||'vid is '||vid);
if mycur%notfound then
dbms_output.put_line('there is no data finded!');
end if;
exit when mycur%notfound;
end loop;
close mycur;
end;
输出为:
owner is SYS,vid is 1
owner is SYS,vid is 46
owner is SYS,vid is 28
owner is SYS,vid is 15
owner is SYS,vid is 29
owner is SYS,vid is 3
it is over
owner is SYS,vid is 3
there is no data finded!
======================================================================
此例子与上一例子的区别是 exit when 语句的位置。放在了fetch 之后。
结果集被取出完毕之后,便退出loop。下面的语句不再执行。
declare
vid t.object_id%type;
vowner t.owner%type;
vnum integer;
cursor mycur is select object_id ,owner from t where rownum begin
-- select object_id ,owner into vid,vowner from t where rownum =2;
open mycur;
loop
fetch mycur into vid,vowner;
exit when mycur%notfound;
if vnum = 6 then
dbms_output.put_line('it is over');
vnum :=vnum+1;
end if;
vnum :=mycur%rowcount;
dbms_output.put_line(vnum||'owner is '||vowner||','||'vid is '||vid);
if mycur%notfound then
dbms_output.put_line('there is no data finded!');
end if;
end loop;
close mycur;
end;
输出为:
1 owner is SYS,vid is 1
2 owner is SYS,vid is 46
3 owner is SYS,vid is 28
4 owner is SYS,vid is 15
5 owner is SYS,vid is 29
6 owner is SYS,vid is 3