我有一个光标,用于获取关于某个类的所有组的一些信息以及其他处理。
支持游标的查询可能不返回任何行,
因为没有组或类id不存在。
在这些情况下,我想引发一个异常。
问题是,即使我插入一个不存在的类id,函数也直接转到'else' .
每个人都知道为什么吗?
create or replace function ReturnGroupsOfClass(class_id_in in integer) return sys_refcursor
is
FunctionResult sys_refcursor;
exception_no_class_found exception;
pragma
exception_init(exception_no_class_found,-2001);
begin
--a cursor of the group for class 'class_id_in'
open FunctionResult for
select class_ID, group_day, group_startHour, count(*) as num_of_participants
from Participant_In_Group
where class_id=class_id_in
group by class_ID, group_day, group_startHour
order by class_ID;
if(FunctionResult%notfound)
then
raise exception_no_class_found;
else
dbms_output.put_line('you can view the cersor in the test window');
end if;
return(FunctionResult);
end ReturnGroupsOfClass;
%notfound
游标属性仅在从游标获取后设置。因为你的函数只打开FunctionResult
游标,它的found/notfound状态仍然是未定义的。
如果您在检查之前获取一行(仅用于测试目的,因为如果您这样做,游标对调用者没有用处),那么您将得到ORA-02001异常:
create or replace function returngroupsofclass
( class_id_in in integer )
return sys_refcursor
as
functionresult sys_refcursor;
exception_no_class_found exception;
pragma exception_init(exception_no_class_found, -2001);
begin
-- dummy cursor for testing:
open functionresult for
select dummy from dual where 1=2;
declare
testval dual.dummy%type;
begin
fetch functionresult into testval;
end;
if functionresult%notfound then
raise exception_no_class_found;
else
dbms_output.put_line('you can view the cursor in the test window');
end if;
return functionresult;
end returngroupsofclass;
test in PL/SQL Developer:
begin
:result := returngroupsofclass(class_id_in => :class_id_in);
end;
为
ORA-02001: user SYS is not permitted to create indexes with freelist groups
ORA-06512: at "WILLIAM.RETURNGROUPSOFCLASS", line 23
ORA-06512: at line 2
我猜2001可能不是你想链接到这个的错误代码。