引用游标

动态游标。引用游标

declare
type RefEmpCur is ref cursor;
empCur RefEmpCur;
empRow emp%rowtype;
flag int  := 0;
begin
 flag := &flag;
 if flag = 0 then
  open empCur for select * from emp where sal > 500 and sal < 1000;
 elsif flag = 1 then  
   open empCur for select * from emp where sal > 1000;
 else
  open empCur for select * from emp;
 end if ;
 /* 由于for循环会自动打开游标，所以不能使用
 for empRow in empCur loop
 dmbs_output.put_line(empRow.empno); 
 end loop;
 */
 loop
 fetch empcur into empRow;
 dbms_output.put_line('empno  :'||empRow.empno); 
 exit when empCur%notfound;
 end loop;
 dbms_output.put_line(empCur%rowCount);
 close empCur;
end;
/