oracle 获取后 %NOTFOUND 可以返回 null 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11273185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can %NOTFOUND return null after a fetch?
提问by Ben
This questionraised a very interesting point; there seems to be a contradiction in the Oracle documentation on whether it's possible for %NOTFOUNDto be null after a fetch. Is it?
这个问题提出了一个非常有趣的观点；Oracle 文档中关于%NOTFOUND获取后是否可能为空似乎存在矛盾。是吗？
To quote from the 11g documentation
引用11g 文档
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);
注意：在示例 6-16 中，如果 FETCH 从不获取行，则 c1%NOTFOUND 始终为 NULL，并且永远不会退出循环。为防止无限循环，请改用此 EXIT 语句： EXIT WHEN c1%NOTFOUND OR (c1%NOTFOUND IS NULL);
The documentation seems to directly contradict itself as it also says the following, which implies that after a fetch %NOTFOUNDcannotbe null.
该文档似乎直接自相矛盾，因为它还说明了以下内容，这意味着在 fetch 之后%NOTFOUND不能为空。
%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
%NOTFOUND（%FOUND 的逻辑相反）返回：
NULL 在显式游标打开之后但在第一次提取之前
FALSE 如果最近从显式游标中提取返回一行
TRUE 否则
The 10g documentationhas 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.
该10克文件也有类似的警告，因为它警告说，可能会取不为了成功执行这种行为将要展出这不是必然，直接的矛盾。
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:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
在第一次获取之前，%NOTFOUND 评估为 NULL。如果 FETCH 永远不会成功执行，则 EXIT WHEN 条件永远不会为 TRUE，并且永远不会退出循环。为安全起见，您可能希望改用以下 EXIT 语句：
当 c1%NOTFOUND 或 c1%NOTFOUND 为空时退出；
In what situations might a fetch either "fail" or might %NOTFOUNDreturn null after a fetch has been executed?
在什么情况下 fetch 可能“失败”或%NOTFOUND在执行 fetch 后可能返回 null？
回答by A.B.Cade
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;
But this only makes your question stronger since it will evaluate to null, neither in 10g nor in 11g ...
但这只会使您的问题变得更强大，因为它会评估为空，无论是在 10g 还是在 11g ...
回答by Adam Hawkes
I think the part that's tripping you up is this:
我认为让你绊倒的部分是：
If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited.
如果 FETCH 永远不会成功执行，则 EXIT WHEN 条件永远不会为 TRUE，并且永远不会退出循环。
Somewhere in the past there must have been a code example which looked like this:
在过去的某个地方一定有一个代码示例，它看起来像这样：
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND;
-- Do stuff
END LOOP;
Given this chunk of code, then the statement rings true. If the fetch never executes (fails), then %NOTFOUND will be null. The EXIT WHENcondition will not evaluate to TRUE (null evaluates to false). Then, indeed, the loop will continue forever.
给定这块代码，那么该语句是正确的。如果提取从未执行（失败），则 %NOTFOUND 将为空。该EXIT WHEN条件将不计算为TRUE（空的计算结果为假）。然后，确实，循环将永远持续下去。
回答by Cheran Shunmugavel
This is a situation that is easily tested:
这是一种很容易测试的情况：
SET SERVEROUT ON;
DECLARE
-- this cursor returns a single row
CURSOR c1 IS
SELECT 1 FROM dual WHERE rownum = 1;
-- this cursor returns no rows
CURSOR c2 IS
SELECT 1 FROM dual WHERE 1=0;
v1 number;
BEGIN
OPEN c1;
FETCH c1 INTO v1; -- this returns a record
FETCH c1 INTO v1; -- this does not return a record
IF c1%NOTFOUND THEN
dbms_output.put_line('c1%NOTFOUND: TRUE');
ELSIF c1%NOTFOUND IS NULL THEN
dbms_output.put_line('c1%NOTFOUND: NULL');
ELSE
dbms_output.put_line('c1%NOTFOUND: FALSE');
END IF;
CLOSE c1;
OPEN c2;
FETCH c2 INTO v1; -- this does not return a record
IF c2%NOTFOUND THEN
dbms_output.put_line('c2%NOTFOUND: TRUE');
ELSIF c2%NOTFOUND IS NULL THEN
dbms_output.put_line('c2%NOTFOUND: NULL');
ELSE
dbms_output.put_line('c2%NOTFOUND: FALSE');
END IF;
CLOSE c2;
END;
/
The output of the script on Oracle APEX 4.1 is (I think APEX is running Oracle 11gR2, but you can easily run the script on any version):
Oracle APEX 4.1 上的脚本输出是（我认为 APEX 运行的是 Oracle 11gR2，但您可以轻松地在任何版本上运行该脚本）：
c1%NOTFOUND: TRUE
c2%NOTFOUND: TRUE
Based on this test, %NOTFOUNDwill not be NULL after a fetch has been executed. This matches what the 10g and 11g documentation says in the initial description of the %NOTFOUNDattribute. The note about the loop never exiting must be from an old version of the example. Since it's just a note, I'd says it's safe to trust the initial description and disregard the note.
基于此测试，%NOTFOUND执行提取后不会为 NULL。这与 10g 和 11g 文档在%NOTFOUND属性的初始描述中所说的一致。关于永不退出循环的注释必须来自示例的旧版本。由于它只是一个便笺，我认为可以相信最初的描述而忽略该便笺是安全的。
