Oracle中遍历Ref Cursor示例

示例编写环境

数据库：Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

登陆用户：Scott

Oracle IDE：Oracle SQL Developer Version 4.0.0.12

 1 SET SERVEROUTPUT ON;
 2 DECLARE
 3 
 4   TYPE REC IS RECORD(
 5     EMPNO     VARCHAR2(80 BYTE),
 6     ENAME     VARCHAR2(80 BYTE)
 7   );
 8   TYPE TB IS TABLE OF REC;
 9   
10   MTB         TB;
11   REF_C       SYS_REFCURSOR;
12   L_SQL       VARCHAR2(100 BYTE) := 'SELECT EMPNO,ENAME FROM EMP';
13   
14 BEGIN
15   
16   OPEN REF_C FOR L_SQL;
17   FETCH REF_C BULK COLLECT INTO MTB;
18   CLOSE REF_C;
19   
20   FOR I IN 1..MTB.COUNT
21   LOOP
22     DBMS_OUTPUT.PUT_LINE('EMPNO='|| MTB(I).EMPNO || ',ENAME='||MTB(I).ENAME);
23   END LOOP;
24   
25 END;