Oracle ref cursor和sys_refcursor，refsys_refcursor

Oracle ref cursor和sys_refcursor，refsys_refcursor

1. 自定義 ref cursor 和 sys_refcursor;
2. sys_refcursor 做為參數傳遞結果集;
3. ref cursor 做為參數傳遞結果集;

1. 自定義 ref cursor 和 sys_refcursor:

  declare
  type df_ref is ref cursor; --定義 ref cursor
  rf df_ref; --聲明 rf 是df_ref
  ename varchar2(30);
 begin
  open rf for 'select ename from emp';
  loop
   fetch rf into ename;
   dbms_output.put_line(ename);
  exit when rf%notfound;
 end loop;
 close rf; 
end;
/

sys_refcursor 不需要聲明可以直接使用:

 declare
 reft sys_refcursor;
  begin
   open reft for 'select * from emp';
  close  reft;  
 end;

sqlplus 中可以使用refcursor:

OPS$SYWU@sydb%11GR2>variable r refcursor;
OPS$SYWU@sydb%11GR2>exec open :r for 'select * from emp';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
OPS$SYWU@sydb%11GR2>print :r;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.

2. sys_refcursor 做為參數傳遞結果集:

 create or replace procedure pro_getEmp(ref_rs out sys_refcursor)
 is
  begin
  open ref_rs for 'select ename,empno from emp';
  ---不能在這裡關閉
 end;
/

declare
refc sys_refcursor;
ename varchar2(30);
empno number;
begin
 pro_getEmp(ref_rs=>refc);
 loop
    fetch refc into ename,empno;
  dbms_output.put_line(ename||' '||empno);
 exit when refc%notfound;
 end loop;
end;
/ 

3. ref cursor 做為參數傳遞結果集:
   在包頭定義 ref cursor:

create or replace package pk_cur 
  as 
  type df_cursor is ref cursor;
  
  function fun_emp return df_cursor;
 end;
/
 
 create or replace package body pk_cur
  is
  
   function fun_emp return df_cursor
     is
	fn_cursor df_cursor;
     begin
       open fn_cursor for 'select * from emp';
     return fn_cursor;
   end;
end;
/
   
OPS$SYWU@sydb%11GR2>  select pk_cur.fun_emp from dual;
FUN_EMP
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected. 

oracle ref cursor問題

可以的
不過你的那個類型錯了
如果你是在包裡面只是申請一個
type c_cursor is ref cursor 這樣的話
那麼你 過程裡面 就直接
v_a package.c_cursor
就好了
不需要後面的%rowtype
 

怎使用REF CURSOR處理Oracle的結果集

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.（責任編輯：盧兆林）