一、问题起源:
在看马老师的Oracle - PL/SQL视频的"游标"部分时, 讲到如何用遍历输出一个结果集时,有个这样的代码例子.
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
并且强调: fetch c into v_emp; 一定要放在 exit when (c%notfound); 前面才行. 不然会把最后一条记录多输出一遍.
二 、我的疑惑,
（1）为什么exit when放在fetch前面会多输出一条记录？
exit when在fetch前面的话，不是在最后一次fetch到有效记录后，游标自动跳到下一个位置，没有指向一个有效记录，此时notfound的值为true，在下次进行exit when（(c%notfound）判断时不是因为true自动退出循环吗？怎么会继续去执行循环体下面的输出代码呢？
（2）notfound属性的值真的是取决于游标的当前是否指向一条有效记录吗？还是取决于fetch数据的结果？
（3）同时对游标这个概念的也不知如何理解？
三、分析问题找疑惑根源并做针对性的相关测试解惑
发现就这个问题而言，最核心的疑惑来自于对notfound这个游标的属性值到底是怎么取决于什么没弄清楚，是fetch前后产生变化还是和游标状态什么的相关？决定写一些辅助的输出notfound在fetch前后的值。测试代码
（1）exit-when在fetch前面
declare
cursor c is
select * from emp;
v_emp c%rowtype;
v_tmp number(6) :=0;
begin
open c;
loop
v_tmp := v_tmp+1;
dbms_output.put_line('_________________'||v_tmp);
exit when (c%notfound);
if((c%notfound)=true) then
dbms_output.put_line('exit-when后-fetch前,notfound值为-真');
elsif((c%notfound)=false) then
dbms_output.put_line('exit-when后-fetch前,notfound值为-假');
else
dbms_output.put_line('exit-when后-fetch前,notfound值为-空');
end if;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename||'********');
if((c%notfound)=true) then
dbms_output.put_line('fetch后notfound值为-真');
elsif((c%notfound)=false) then
dbms_output.put_line('fetch后notfound值为-假');
else
dbms_output.put_line('fetch后notfound值为-空');
end if;
dbms_output.put_line('_________________'||v_tmp);
end loop;
close c;
end;运行结果是
_________________1
exit-when后-fetch前,notfound值为-空
SMITH********
fetch后notfound值为-假
_________________1
_________________2
exit-when后-fetch前,notfound值为-假
ALLEN********
fetch后notfound值为-假
_________________2
_________________3
exit-when后-fetch前,notfound值为-假
WARD********
fetch后notfound值为-假
_________________3
_________________4
exit-when后-fetch前,notfound值为-假
JONES********
fetch后notfound值为-假
_________________4
_________________5
exit-when后-fetch前,notfound值为-假
MARTIN********
fetch后notfound值为-假
_________________5
_________________6
exit-when后-fetch前,notfound值为-假
BLAKE********
fetch后notfound值为-假
_________________6
_________________7
exit-when后-fetch前,notfound值为-假
CLARK********
fetch后notfound值为-假
_________________7
_________________8
exit-when后-fetch前,notfound值为-假
SCOTT********
fetch后notfound值为-假
_________________8
_________________9
exit-when后-fetch前,notfound值为-假
KING********
fetch后notfound值为-假
_________________9
_________________10
exit-when后-fetch前,notfound值为-假
TURNER********
fetch后notfound值为-假
_________________10
_________________11
exit-when后-fetch前,notfound值为-假
ADAMS********
fetch后notfound值为-假
_________________11
_________________12
exit-when后-fetch前,notfound值为-假
JAMES********
fetch后notfound值为-假
_________________12
_________________13
exit-when后-fetch前,notfound值为-假
FORD********
fetch后notfound值为-假
_________________13
_________________14
exit-when后-fetch前,notfound值为-假
MILLER********
fetch后notfound值为-假
_________________14
_________________15
exit-when后-fetch前,notfound值为-假
MILLER********
fetch后notfound值为-真
_________________15
_________________16（2）fetch在exit when前面
declare
cursor c is
select * from emp;
v_emp c%rowtype;
v_tmp number(6) :=0;
begin
open c;
loop
v_tmp := v_tmp+1;
dbms_output.put_line('___________'||v_tmp||'___________');
if((c%notfound)=true) then
dbms_output.put_line('notfound值为--真___fetch前');
elsif((c%notfound)=false) then
dbms_output.put_line('notfound值为--假___fetch前');
else
dbms_output.put_line('notfound值为--空___fetch前');
end if;
fetch c into v_emp;
if((c%notfound)=true) then
dbms_output.put_line('notfound值为--真___fetch后exit-when前');
elsif((c%notfound)=false) then
dbms_output.put_line('notfound值为--假___fetch后exit-when前');
else
dbms_output.put_line('notfound值为--空___fetch后exit-when前');
end if;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename||'********');
if((c%notfound)=true) then
dbms_output.put_line('notfound值为--真___exit-when判定后');
elsif((c%notfound)=false) then
dbms_output.put_line('notfound值为--假___exit-when判定后');
else
dbms_output.put_line('notfound值为--空___exit-when判定后');
end if;
dbms_output.put_line('___________'||v_tmp||'___________');
end loop;
close c;
end;运行结果是
___________1___________
notfound值为--空___fetch前
notfound值为--假___fetch后exit-when前
SMITH********
notfound值为--假___exit-when判定后
___________1___________
___________2___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
ALLEN********
notfound值为--假___exit-when判定后
___________2___________
___________3___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
WARD********
notfound值为--假___exit-when判定后
___________3___________
___________4___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
JONES********
notfound值为--假___exit-when判定后
___________4___________
___________5___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
MARTIN********
notfound值为--假___exit-when判定后
___________5___________
___________6___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
BLAKE********
notfound值为--假___exit-when判定后
___________6___________
___________7___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
CLARK********
notfound值为--假___exit-when判定后
___________7___________
___________8___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
SCOTT********
notfound值为--假___exit-when判定后
___________8___________
___________9___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
KING********
notfound值为--假___exit-when判定后
___________9___________
___________10___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
TURNER********
notfound值为--假___exit-when判定后
___________10___________
___________11___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
ADAMS********
notfound值为--假___exit-when判定后
___________11___________
___________12___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
JAMES********
notfound值为--假___exit-when判定后
___________12___________
___________13___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
FORD********
notfound值为--假___exit-when判定后
___________13___________
___________14___________
notfound值为--假___fetch前
notfound值为--假___fetch后exit-when前
MILLER********
notfound值为--假___exit-when判定后
___________14___________
___________15___________
notfound值为--假___fetch前
notfound值为--真___fetch后exit-when前
四、解惑总结
（1）游标的notfound属性值取决于fetch的结果，而非取决于游标当前是否指向一条有效记录。notfound并不是表示当前游标是不是指向一条有效记录。
（2）对于显式游标而言，open后第一次fetch之前cursor%notfound的值为null。中间所有能fetch到记录的情况的fetch前后cursor%notfound的值均为false。直到最后一次fetch但是fetch不到记录了值就为true了。
（3）注意的一点，pl/sql中的boolean类型变量还有个null值,并且还是默认的.除此之外还有true,false.
扫一扫
3660
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
