获取后 %NOTFOUND 可以返回 null 吗?
这个问题提出了一个非常有趣的观点；Oracle 文档中关于 %NOTFOUND 在获取后是否可能为空似乎存在矛盾.是吗?
This question raised a very interesting point; there seems to be a contradiction in the Oracle documentation on whether it's possible for %NOTFOUND to be null after a fetch. Is it?
引用 11g 文档
注意:在例 6-16 中，如果 FETCH 从不获取一行，则 c1%NOTFOUND始终为 NULL，并且永远不会退出循环.防止无限循环，请改用此 EXIT 语句:EXIT WHEN c1%NOTFOUND OR(c1%NOTFOUND 为空);
Note: In Example 6-16, if FETCH never fetches a row, then c1%NOTFOUND is always NULL and the loop is never exited. To prevent infinite looping, use this EXIT statement instead: EXIT WHEN c1%NOTFOUND OR (c1%NOTFOUND IS NULL);
文档似乎直接自相矛盾，因为它还说明了以下内容，这意味着在获取后 %NOTFOUND cannot 为 null.
The documentation seems to directly contradict itself as it also says the following, which implies that after a fetch %NOTFOUND cannot be null.
%NOTFOUND(%FOUND 的逻辑相反)返回:
在显式游标打开之后但在第一次获取之前为 NULL
如果最近从显式游标中获取的数据返回了一行，则为 FALSE
否则为真
%NOTFOUND (the logical opposite of %FOUND) returns:
NULL after the explicit cursor is opened but before the first fetch
FALSE if the most recent fetch from the explicit cursor returned a row
TRUE otherwise
10g 文档类似的警告，这不一定是直接矛盾，因为它警告可能无法成功执行获取以显示此行为.
The 10g documentation has a similar warning, which isn't, necessarily, a direct contradiction as it warns that a fetch might not execute successfully in order for this behaviour to be exhibited.
在第一次获取之前，%NOTFOUND 评估为 NULL.如果 FETCH 从不成功执行，EXIT WHEN 条件从不为 TRUE，并且循环永远不会退出.为了安全起见，您可能需要使用以下内容改为退出语句:
Before the first fetch, %NOTFOUND evaluates to NULL. If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited. To be safe, you might want to use the following EXIT statement instead:
当 c1%NOTFOUND 或 c1%NOTFOUND 为空时退出；
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
在什么情况下获取可能失败"或在执行获取后 %NOTFOUND 可能返回 null?
In what situations might a fetch either "fail" or might %NOTFOUND return null after a fetch has been executed?
推荐答案
我可以找到一个 fetch 可能失败的情况:
I can find a situation where a fetch can fail:
declare
i integer;
cursor c is
select 1 / 0 from dual;
begin
open c;
begin
fetch c
into i;
exception
when others then
dbms_output.put_line('ex');
end;
if c%notfound is null then
dbms_output.put_line('null');
elsif c%notfound then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
close c;
end;
但这只会让你的问题更强烈，因为它会评估为空，无论是在 10g 还是 11g ...
But this only makes your question stronger since it will evaluate to null, neither in 10g nor in 11g ...
相关文章