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、强类型游标:
1 2 3 4 5 6 7 8 | 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;
/
|
Copier après la connexion
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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;
/
|
Copier après la connexion
1 2 3 4 | set serveroutput on
exec strongly_typed.parent(1);
exec strongly_typed.parent(8);
|
Copier après la connexion
2、弱类型游标:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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);
|
Copier après la connexion
3、预定义游标变量:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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) IS
TYPE 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 on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees' ;
pass_ref_cur(rec_array);
CLOSE rec_array;
END ;
/
|
Copier après la connexion
-----------------------------------------------------
Dylan presents.