examples for oracle ref cursors

CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE 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 IS

PROCEDURE child(p_return_rec OUT return_cur) IS BEGIN 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 on

exec strongly_typed.parent(1)
exec strongly_typed.parent(8)

CREATE OR REPLACE PROCEDURE child (
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
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 on

exec parent(1)
exec parent(17)

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, 'Jack Cline');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242290/，如需轉載，請註明出處，否則將追究法律責任。