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

SET SERVEROUTPUT ON;
DECLARE

  TYPE REC IS RECORD(
    EMPNO     VARCHAR2(80 BYTE),
    ENAME     VARCHAR2(80 BYTE)
  );
  TYPE TB IS TABLE OF REC;
  
  MTB         TB;
  REF_C       SYS_REFCURSOR;
  L_SQL       VARCHAR2(100 BYTE) := 'SELECT EMPNO,ENAME FROM EMP';
  
BEGIN
  
  OPEN REF_C FOR L_SQL;
  FETCH REF_C BULK COLLECT INTO MTB;
  CLOSE REF_C;
  
  FOR I IN 1..MTB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPNO='|| MTB(I).EMPNO || ',ENAME='||MTB(I).ENAME);
  END LOOP;
  
END;