Using of oracle ref cursor
程序员文章站
2022-04-26 09:54:56
...
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 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);
2、弱类型游标:
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 rownum3、预定义游标变量:
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; /-----------------------------------------------------
Dylan presents.
推荐阅读
-
oracle下巧用bulk collect实现cursor批量fetch的sql语句
-
Oracle 12CR2查询转换教程之cursor-duration临时表详解
-
PLSQL中显式Cursor、隐式Cursor、动态Ref Cursor
-
PLSQL中显式Cursor、隐式Cursor、动态Ref Cursor
-
oracle数据库的游标Cursor和存储过程 PL/SQL
-
Oracle 优化篇+STS+输入源(1/5)Cursor Cache
-
Oracle游标 CURSOR实例详解
-
oracle学习笔记(二十二) REF 动态游标
-
Oracle中游标Cursor基本用法详解
-
Oracle Cursor详解与实例