How to Return Result-Set via PLSQL Table or REF Cursor 博客分类： PLSQL， plsqlrefcursor

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