How to Return Result-Set via PLSQL Table or REF Cursor

How to Return Result-Set via PLSQL Table or REF Cursor

如何通过PLSQL 表 或者 REF Cursor 返回结果集

create or replace package test_pack is

type mycur is ref cursor;

type myrectype is record (loc varchar2(100));

type mytabtype is table of myrectype index by binary_integer;

end;

/

create or replace procedure return_many_rows_ref(rset in out test_pack.mycur)

is

begin

open rset for select * from dept;

end;

/

create or replace procedure return_many_rows_tabrec(rset in out test_pack.mytabtype)

is

cursor dept_cur is select loc from dept;

ctr number := 0;

begin

for x in dept_cur loop

rset(ctr).loc := x.loc;

ctr := ctr + 1;

end loop;

end;

/

set serveroutput on;

declare

result1 test_pack.mycur;

dep_rec dept%rowtype;

begin

return_many_rows_ref(result1);

loop

fetch result1 into dept_rec;

exit when result1%notfound;

dbms_output.put_line(dep_rec.deptno);

end loop;

close result1;

end;

/

declare

result2 test_pack.mytabtype;

begin

return_many_rows_tabrec(result2);

for x in result2.first..result2.last loop

dbms_output.put_line(result2(x).loc);

end loop;

end;

/