嵌套游标

嵌套游标

declare
c_emp_sub sys_refcursor;
v_empno number;
v_empno_sub number;
cursor c_emp is select empno,cursor(select empno from emp where a.empno=mgr ) substaff from emp a;
begin
open c_emp ;
loop
fetch c_emp into v_empno,c_emp_sub;
exit when c_emp%notfound;
dbms_output.put_line(v_empno);
        loop
        fetch c_emp_sub into v_empno_sub;      --此处直接fetch  不用open，因为c_emp已经打开了
        exit when c_emp_sub%notfound;
        dbms_output.put_line('--'||v_empno_sub);
        end loop;
end loop;
close c_emp;
end;


等价实现如下：
declare
c_emp_sub sys_refcursor;
v_empno number;
v_empno_sub number;
cursor c_emp is select empno from emp a;
begin
open c_emp ;
loop
fetch c_emp into v_empno;
exit when c_emp%notfound;
dbms_output.put_line(v_empno);
        open c_emp_sub for select empno from emp where mgr=v_empno;
        loop
        fetch c_emp_sub into v_empno_sub;
        exit when c_emp_sub%notfound;
        dbms_output.put_line('--'||v_empno_sub);
        end loop;
        close c_emp_sub;
end loop;
close c_emp;
end;