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;
/

/* Procedure to Return resultset using REF Cursor */
create or replace procedure return_many_rows_ref(rset in out test_pack.mycur)
is
begin
open rset for select * from dept;
end;
/

/* Procedure to Return resultset using PL/SQL table of Records */
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;
/

/* PL/SQL block to test procedure that returns many rows using REF cursor */

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;
/

/* PL/SQL block to test procedure that returns many rows using PL/SQL table of records*/

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;
/