1、强类型游标： create or replace package strongly_typed istype return_cur is ref cursor return all_tables%rowtype;procedure child(p_return_rec out return_cur);procedure parent(p_numrecs pls_integer);end strongly_typed;/ create or replace
1、强类型游标：
create or replace package strongly_typed istype return_cur is ref cursor return all_tables%rowtype;procedure child(p_return_rec out return_cur);procedure parent(p_numrecs pls_integer);end strongly_typed;/
create or replace package body strongly_typed isprocedure child(p_return_rec out return_cur) isbegin open p_return_rec for select * from all_tables; end child;--==================================================procedure parent (p_numrecs pls_integer) is p_retcur return_cur; at_rec all_tables%rowtype;begin child(p_retcur); for i in 1 .. p_numrecs loop fetch p_retcur into at_rec; dbms_output.put_line(at_rec.table_name || ' - ' || at_rec.tablespace_name || ' - ' || to_char(at_rec.initial_extent) || ' - ' || to_char(at_rec.next_extent)); end loop;end parent;end strongly_typed;/
set serveroutput onexec strongly_typed.parent(1);exec strongly_typed.parent(8);
2、弱类型游标：
create or replace procedure child ( p_numrecs in pls_integer, p_return_cur out sys_refcursor)isbegin open p_return_cur for 'select * from all_tables where rownum <= ' || p_numrecs ;end child;/create or replace procedure parent (pnumrecs varchar2) is p_retcur sys_refcursor; at_rec all_tables%rowtype;begin child(pnumrecs, p_retcur); for i in 1 .. pnumrecs loop fetch p_retcur into at_rec; dbms_output.put_line(at_rec.table_name || ' - ' || at_rec.tablespace_name || ' - ' || to_char(at_rec.initial_extent) || ' - ' || to_char(at_rec.next_extent)); end loop;end parent;/set serveroutput onexec parent(1);exec parent(17);
3、预定义游标变量：
create table employees (empid number(5),empname varchar2(30));insert into employees (empid, empname) values (1, 'dan morgan');insert into employees (empid, empname) values (2, 'hans forbrich');insert into employees (empid, empname) values (3, 'caleb small');commit;create or replace procedure pass_ref_cur(p_cursor sys_refcursor) istype array_t is table of varchar2(4000)index by binary_integer;rec_array array_t;begin fetch p_cursor bulk collect into rec_array; for i in rec_array.first .. rec_array.last loop dbms_output.put_line(rec_array(i)); end loop;end pass_ref_cur;/set serveroutput ondeclare rec_array sys_refcursor;begin open rec_array for 'select empname from employees'; pass_ref_cur(rec_array); close rec_array;end;/
-----------------------------------------------------
dylan presents.