cursors - %notfound is true even when row is returned
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 ;)
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:
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:
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.
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.
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;
精彩评论