ref游标oracle,使用REF CURSOR处理Oracle的结果集

使用REF CURSOR处理Oracle的结果集

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.

-The End-

By eygle on 2007-11-06 16:54 |

Comments (6) |

SQL.PLSQL | 1657 |

6 Comments

如何以上语句写在packbody之中，1_refc作为返回值返回,那在外部环境中，比如说pro*c中，该如何处理这个返回值,这样吗？

CURSOR mycursor;

call packbody.func() into :mycursor;??????????

接下来可以把mycursor看作一个普通的游标进行fetch操作了吗？

--盖老师，我觉得用循环取的效率有点低

我是这样做的，效率因该能够高点

--利用9i出来的sys_refcursor

create or REPLACE PROCEDURE proc_get(p_rc out sys_refcursor) as

BEGIN

open p_rc for select table_name,tablespace_name,status from user_table_t;

END;

/

VARIABLE p_rc refcursor;

EXEC proc_get(:p_rc);

print;

============

我有加你msn的，回头有空多向您请教。

--盖老师，我觉得用循环取的效率有点低

我是这样做的，效率因该能够高点

--利用9i出来的sys_refcursor

create or REPLACE PROCEDURE proc_get(p_rc out sys_refcursor) as

BEGIN

open p_rc for select table_name,tablespace_name,status from user_table_t;

END;

/

VARIABLE p_rc refcursor;

EXEC proc_get(:p_rc);

print;

============

我有加你msn的，回头有空多向您请教。

--盖老师，我觉得用循环取的效率有点低

我是这样做的，效率因该能够高点

--利用9i出来的sys_refcursor

create or REPLACE PROCEDURE proc_get(p_rc out sys_refcursor) as

BEGIN

open p_rc for select table_name,tablespace_name,status from user_table_t;

END;

/

VARIABLE p_rc refcursor;

EXEC proc_get(:p_rc);

print;

============

我有加你msn的，回头有空多向您请教。

不用refcursor，用cursor也可以实现的啊