游标结果集.txt

oracle的课堂ppt

create or replace package pkg_findall
is
 TYPE cur is REF CURSOR;
 procedure up_pf(v_cur out cur);
 function uk_kf return cur;
end pkg_findall;


create or replace package body pkg_findall
is
 procedure up_pf(v_cur out cur)
 is
 begin
  open v_cur for select * from emp;
 end up_pf;

 function uk_kf
 return cur
 is
  v_cur cur;
  begin
   open v_cur for select * from emp;
  end uk_kf;

end pkg_findall;

declare
 my_cur pkg_findall.cur;
 v_row emp%rowtype;
begin 
 pkg_findall.up_pf(my_cur);
 loop
  fetch my_cur into v_row;
  exit when my_cur%notfound;
  dbms_output.put_line(v_row.empno);
  dbms_output.put_line(v_row.ename);
  dbms_output.put_line(v_row.job);
  dbms_output.put_line(v_row.mgr);
  dbms_output.put_line(v_row.sal);
  dbms_output.put_line(v_row.comm);
  dbms_output.put_line(v_row.deptno);
  dbms_output.put_line('--------------------------------------------------------------');
 end loop;
 close my_cur;
end;


declare
 my_cur pkg_findall.cur;
 v_row emp%rowtype;
begin 
 my_cur := pkg_findall.uk_kf;
 loop
  fetch my_cur into v_row;
  exit when my_cur%notfound;
  dbms_output.put_line(v_row.empno);
  dbms_output.put_line(v_row.ename);
  dbms_output.put_line(v_row.job);
  dbms_output.put_line(v_row.mgr);
  dbms_output.put_line(v_row.sal);
  dbms_output.put_line(v_row.comm);
  dbms_output.put_line(v_row.deptno);
  dbms_output.put_line('--------------------------------------------------------------');
 end loop;
 close my_cur;
end;