« Varchar2(4000)能存多少数据? | Blog首页 | wmiprvse.exe进程是什么? »
使用REF CURSOR处理Oracle的结果集
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2007/11/oracle_ref_cursor.html
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-82PL/SQL procedure successfully completed.
-The End-
历史上的今天...
>> 2008-11-06文章:
>> 2006-11-06文章:
>> 2005-11-06文章:
>> 2004-11-06文章:
------
这篇 【使用REF CURSOR处理Oracle的结果集】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2007-11-06 16:54 | Comments (5) | Posted to SQL.PLSQL | Edit |
留言 (5)
ref cursor翻译成中文，是不是叫“引用游标”？
Posted by: edeed at November 7, 2007 8:56 PM
如何以上语句写在packbody之中，1_refc作为返回值返回,那在外部环境中，比如说pro*c中，该如何处理这个返回值,这样吗？
CURSOR mycursor;
call packbody.func() into :mycursor;??????????
接下来可以把mycursor看作一个普通的游标进行fetch操作了吗？
Posted by: cuixiaoqian at November 16, 2007 11:52 AM
--盖老师，我觉得用循环取的效率有点低
我是这样做的，效率因该能够高点
--利用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的，回头有空多向您请教。
Posted by: kt563 at July 22, 2008 5:44 PM
--盖老师，我觉得用循环取的效率有点低
我是这样做的，效率因该能够高点
--利用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的，回头有空多向您请教。
Posted by: kt563 at July 22, 2008 5:45 PM
--盖老师，我觉得用循环取的效率有点低
我是这样做的，效率因该能够高点
--利用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的，回头有空多向您请教。
Posted by: kt563 at July 22, 2008 5:47 PM