How to use the oracle REF CURSOR

« AIX 5.3系统中如何设置>2G的大文件支持 | 文摘首页 | 66首经典的少儿歌曲下载链接 »

转载 by：eygle | 【恩墨学院 OCM培训传DBA成功之道】
链接：https://www.eygle.com/digest/2009/01/how_to_use_the_oracle_ref_curs.html

原文地址： http://www.psoug.org/reference/ref_cursors.html

Oracle Ref Cursors

Version 10.2

Strongly Typed

Note: A REF CURSOR that specifies a specific return type.

Package Header

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

Package Body

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

To Run The Demo

set serveroutput on

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

Weakly Typed

Note: A REF CURSOR that does not specify the return type such as SYS_REFCURSOR.

Child Procedure

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

Parent Procedure

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

To Run The Demo

set serveroutput on

exec parent(1)
exec parent(17)

Passing Ref Cursors

Ref Cursor Passing Demo

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;

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

历史上的今天...
>> 2006-01-09文章:

Volume Manager常用命令总结

By eygle on 2009-01-09 09:23 | Comments (0) | Oracle摘 | 2223 |