oracle 游标 - 即使返回行，%notfound 也为真
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1709099/
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
cursors - %notfound is true even when row is returned
提问by FrustratedWithFormsDesigner
I have a cursor that is used to get some preliminary information for some other processing. It is possible that the query backing the cursor may not return any rows, and in these rare cases, we want to raise a special exception (handled and logged elsewhere so processing is not compeltely halted) so that the user knows about what is most likely bad input. Here's what it looks like:
我有一个游标，用于获取一些其他处理的初步信息。支持游标的查询可能不会返回任何行，在这些罕见的情况下，我们想要引发一个特殊的异常（在其他地方处理和记录，因此处理不会完全停止），以便用户知道什么是最有可能的输入错误。这是它的样子：
open c_getPrs(in_pnum);
loop
fetch c_getPrs
into r_rpmRecord;
if c_getPrs%NOTFOUND then
raise X_INVALID_PNUM;
end if;
exit when c_getPrs%rowcount > 1 /*or c_getPrs%NOTFOUND*/;
end loop;
close c_getPrs;
The problem is that the if-statement ALWAYS executes so the exception is always raised, even when a row is returned. I'm not sure why. If there's a better way to handle this kind of logic, I'm open to that too ;)
问题在于 if 语句始终执行，因此即使返回一行，也始终会引发异常。我不知道为什么。如果有更好的方法来处理这种逻辑，我也愿意接受；)
回答by Tony Andrews
Your code always goes round the loop twice, and so fails if there are less than 2 rows returned by the cursor. You probably don't need the loop at all:
您的代码总是循环两次，因此如果游标返回的行少于 2 行，则会失败。您可能根本不需要循环：
open c_getPrms(in_pnum);
fetch c_getPrms
into r_prmRecord;
if c_getPrms%NOTFOUND then
raise X_INVALID_PNUM;
end if;
close c_getPrms;
I would prefer to avoid the cursor altogether, and use "select into" instead:
我宁愿完全避免使用光标，而是使用“select into”：
begin
select ...
into r_prmRecord
from ...
where ...
exception
when no_data_found then
raise X_INVALID_PNUM;
end;
This will raise TOO_MANY_ROWS if the select returns more than 1 row. If you don't want that to happen, i.e. more than 1 row is OK, you could just add "AND ROWNUM = 1" to the query.
如果选择返回超过 1 行，这将引发 TOO_MANY_ROWS。如果您不希望发生这种情况，即超过 1 行是可以的，您可以将“AND ROWNUM = 1”添加到查询中。
回答by Vincent Malgrat
your problem lies with your exit condition: on the first pass c_getPrms%rowcount is 1, so you get another pass which raises the exception.
您的问题在于您的退出条件：在第一次传递时，c_getPrms%rowcount 为 1，因此您会得到另一个引发异常的传递。
Since you want only one fetch I would suggest the following construct:
由于您只想要一次提取，我建议使用以下结构：
OPEN c_getPrms(l_input);
FETCH c_getPrms
INTO r_prmRecord;
IF c_getPrms%NOTFOUND THEN
RAISE X_INVALID_PNUM;
END IF;
CLOSE c_getPrms;
I don't like explicit cursor much, so I will also suggest this synthax:
我不太喜欢显式光标，所以我也建议使用这个合成器：
BEGIN
SELECT ...
INTO r_prmRecord
FROM ...
WHERE ... AND rownum = 1; -- your cursor query
EXCEPTION
WHEN no_data_found THEN
RAISE X_INVALID_PNUM;
END;
