oracle 如果集合为空，FETCH INTO 不会引发异常，是吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4881073/
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
FETCH INTO doesn't raise an exception if the set is empty, does it?
提问by Cade Roux
Here is some actual code I'm trying to debug:
这是我尝试调试的一些实际代码：
BEGIN
OPEN bservice (coservice.prod_id);
FETCH bservice
INTO v_billing_alias_id, v_billing_service_uom_id, v_summary_remarks;
CLOSE bservice;
v_service_found := 1;
-- An empty fetch is expected for some services.
EXCEPTION
WHEN OTHERS THEN
v_service_found := 0;
END;
When the parametrized cursor bservice(prod_id) is empty, it fetches NULL into the three variables and does not throw an exception.
当参数化游标 bservice(prod_id) 为空时，它将 NULL 提取到三个变量中并且不抛出异常。
So whoever wrote this code expecting it to throw an exception was wrong, right? The comment seems to imply that and empty fetch is expected and then it sets a flag for later handling, but I think this code cannot possibly have been tested with empty sets either.
所以写这段代码并期望它抛出异常的人是错误的，对吧？该评论似乎暗示需要空提取，然后它设置了一个标志供以后处理，但我认为这段代码也不可能用空集进行测试。
Obviously, it should use bservice%NOTFOUND or bservice%FOUND or similar.
显然，它应该使用 bservice%NOTFOUND 或 bservice%FOUND 或类似的。
回答by jachguate
If you want to know if the cursor returned any result, use the %FOUND cursor attribute:
如果您想知道游标是否返回任何结果，请使用 %FOUND 游标属性：
OPEN bservice (coservice.prod_id);
FETCH bservice
INTO v_billing_alias_id, v_billing_service_uom_id, v_summary_remarks;
-- An empty fetch is expected for some services.
IF (bservice%FOUND) THEN
v_service_found := 1;
ELSE
v_service_found := 0;
END IF
CLOSE bservice;
from cursor attributes
从游标属性
%FOUND
- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns NULL if cursor is open, but fetch has not been executed
- Returns TRUE if a successful fetch has been executed
- Returns FALSE if no row was returned.
％成立
- 如果游标已声明但未打开，则返回 INVALID_CURSOR；或者如果游标已关闭。
- 如果游标已打开，但尚未执行 fetch，则返回 NULL
- 如果执行成功，则返回 TRUE
- 如果没有返回任何行，则返回 FALSE。
回答by René Nyffenegger
When the parametrized cursor bservice(prod_id) is empty, it fetches NULL into the three variables and does not throw an exception.
当参数化游标 bservice(prod_id) 为空时，它将 NULL 提取到三个变量中并且不抛出异常。
Wrong
错误的
When t is empty, it fetches nothing, and does not overwrite any value.
当 t 为空时，它什么也不取，也不会覆盖任何值。
declare
cursor c(dt in date) is
select dummy from dual
where dt > sysdate;
dummy_ dual.dummy%type;
begin
open c(sysdate + 2);
fetch c into dummy_;
close c;
dbms_output.put_line('1: ' || dummy_);
open c(sysdate - 2);
fetch c into dummy_;
close c;
dbms_output.put_line('2: ' || dummy_);
end;
/
prints
印刷
1: X
2: X
So whoever wrote this code expecting it to throw an exception was wrong, right? Yes
所以写这段代码并期望它抛出异常的人是错误的，对吧？是的
Obviously, it should use bservice%NOTFOUND or bservice%FOUND or similar. Yes
显然，它应该使用 bservice%NOTFOUND 或 bservice%FOUND 或类似的。是的
