Using of oracle ref cursor

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.