Oracle技术之使用REF CURSOR处理Oracle的结果集

gdx

　　Oracle提供REF CURSOR，通过该功能可以实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。
　　使用scott用户的emp表实现以下测试案例：
　　SQL> desc emp
　　Name Null? Type
　　----------------------------------------- -------- ----------------------------
　　EMPNO NOT NULL NUMBER(4)
　　ENAME VARCHAR2(10)
　　JOB VARCHAR2(9)
　　MGR NUMBER(4)
　　HIREDATE DATE
　　SAL NUMBER(7,2)
　　COMM NUMBER(7,2)
　　DEPTNO NUMBER(2)
　　使用ref cursor获得结果集输出：
　　SQL> set serveroutput on
　　SQL> DECLARE
　　2 TYPE mytable IS TABLE OF emp%ROWTYPE;
　　3 l_data mytable;
　　4 l_refc sys_refcursor;
　　5 BEGIN
　　6 OPEN l_refc FOR
　　7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
　　8
　　9 FETCH l_refc BULK COLLECT INTO l_data;
　　10
　　11 CLOSE l_refc;
　　12
　　13 FOR i IN 1 .. l_data.COUNT
　　14 LOOP
　　15 DBMS_OUTPUT.put_line ( l_data (i).ename
　　16 || ' was hired since '
　　17 || l_data (i).hiredate
　　18 );
　　19 END LOOP;
　　20 END;
　　21 /
　　SMITH was hired since 17-DEC-80
　　ALLEN was hired since 20-FEB-81
　　WARD was hired since 22-FEB-81
　　JONES was hired since 02-APR-81
　　MARTIN was hired since 28-SEP-81
　　BLAKE was hired since 01-MAY-81
　　CLARK was hired since 09-JUN-81
　　SCOTT was hired since 19-APR-87
　　KING was hired since 17-NOV-81
　　TURNER was hired since 08-SEP-81
　　ADAMS was hired since 23-MAY-87
　　JAMES was hired since 03-DEC-81
　　FORD was hired since 03-DEC-81
　　MILLER was hired since 23-JAN-82
　　PL/SQL procedure successfully completed.
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html