1,什麼是 REF遊標 ?
動態關連接果集的臨時對象。即在啟動並執行時候動態決定執行查詢。
2,REF 遊標 有什麼作用?
實現在程式間傳遞結果集的功能,利用REF CURSOR也可以實現BULK SQL,從而提高SQL效能。
3,靜態資料指標和REF 遊標的區別是什嗎?
①靜態資料指標是靜態定義,REF 遊標是動態關聯;
②使用REF 遊標需REF 遊標變數。
③REF 遊標能做為參數進行傳遞,而靜態資料指標是不可能的。
4,什麼是REF 遊標變數?
REF遊標變數是一種 引用 REF遊標類型 的變數,指向動態關聯的結果集。
5,怎麼使用 REF遊標 ?
①聲明REF 遊標類型,確定REF 遊標類型;
⑴強型別REF遊標:指定retrun type,REF 遊標變數的類型必須和return type一致。
文法:Type REF遊標名 IS Ref Cursor Return 結果集返回記錄類型;
⑵弱類型REF遊標:不指定return type,能和任何類型的CURSOR變數匹配,用於擷取任何結果集。
文法:Type REF遊標名 IS Ref Cursor;
②聲明Ref 遊標類型變數;
文法:變數名 已聲明Ref 遊標類型;
③開啟REF遊標,關連接果集 ;
文法:Open Ref 遊標類型變數 For 查詢語句返回結果集;
④擷取記錄,操作記錄;
文法:Fatch REF遊標名 InTo 臨時記錄類型變數或屬性類型變數列表;
⑤關閉遊標,完全釋放資源;
文法:Close REF遊標名;
例子:強型別REF遊標
代碼如下 |
複製代碼 |
/*conn scott/tiger*/ Declare Type MyRefCurA IS REF CURSOR RETURN emp%RowType; Type MyRefCurB IS REF CURSOR RETURN emp.ename%Type; vRefCurA MyRefCurA; vRefCurB MyRefCurB; vTempA vRefCurA%RowType; vTempB vRefCurB.ename%Type; Begin Open vRefCurA For Select * from emp Where SAL > 2000; Loop Fatch vRefCurA InTo vTempA; Exit When vRefCurA%NotFound; DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal) End Loop; Close vRefCurA; DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------'); Open vRefCurB For Select ename from emp Where SAL > 2000; Loop Fatch vRefCurB InTo vTempB; Exit When vRefCurB%NotFound; DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||' '||vTempB) End Loop; Close vRefCurB; DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------'); Open vRefCurA For Select * from emp Where JOB = 'CLERK'; Loop Fatch vRefCurA InTo vTempA; Exit When vRefCurA%NotFound; DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal) End Loop; Close vRefCurA; End; |
例子:弱類型REF遊標
代碼如下 |
複製代碼 |
/*conn scott/tiger*/ Declare Type MyRefCur IS Ref Cursor; vRefCur MyRefCur; vtemp vRefCur%RowType; Begin Case(&n) When 1 Then Open vRefCur For Select * from emp; When 2 Then Open vRefCur For Select * from dept; Else Open vRefCur For Select eno, ename from emp Where JOB = 'CLERK'; End Case; Close vRefCur; End; |
6,怎樣讓REF遊標作為參數傳遞?
代碼如下 |
複製代碼 |
--作為函數傳回值 create or replace function returnacursor return sys_refcursor is v_csr sys_refcursor; begin open v_csr for select a1 from test3; return v_csr; end; / declare c sys_refcursor; a1 char(2); begin c:=returnacursor; loop fetch c into a1; exit when c%notfound; dbms_output.put_line(a1); end loop; close c; end; / --作為參數 create or replace procedure proc_ref_cursor (rc in sys_refcursor) as v_a number; v_b varchar2(10); begin loop fetch rc into v_a, v_b; exit when rc%notfound; dbms_output.put_line(v_a || ' ' || v_b); end loop; end; / declare v_rc sys_refcursor; begin open v_rc for select a1,a2 from test3; proc_ref_cursor(v_rc); close v_rc; end; / |
REF CURSOR 樣本包括下列三個 Visual Basic 樣本,示範如何使用 REF CURSOR。
樣本 說明
在 OracleDataReader 中檢索 REF CURSOR 參數
此樣本執行一個 PL/SQL 預存程序,返回 REF CURSOR 參數,並將值作為 OracleDataReader 讀取。
使用 OracleDataReader 從多個 REF CURSOR 檢索資料
此樣本執行一個 PL/SQL 預存程序,返回兩個 REF CURSOR 參數,並使用 OracleDataReader 讀取值。
使用一個或多個 REF CURSOR 填充 DataSet
此樣本執行一個 PL/SQL 預存程序,返回兩個 REF CURSOR 參數,並使用返回的行填充 DataSet。
要使用這些樣本,可能需要建立 Oracle 表,並且必須建立 PL/SQL 包和包本文。
建立 Oracle 表
這些樣本使用 Oracle Scott/Tiger 架構中定義的表。大多數 Oracle 安裝均包括 Oracle Scott/Tiger 架構。如果此架構不存在,可以使用 {OracleHome}rdbmsadminscott.sql 中的 SQL 命令檔案建立供這些樣本使用的表和索引。
建立 Oracle 包和包本文
這些樣本要求伺服器上存在以下 PL/SQL 包和包本文。在 Oracle 伺服器上建立以下 Oracle 包
代碼如下 |
複製代碼 |
CREATE OR REPLACE PACKAGE BODY CURSPKG AS PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN IF N_EMPNO <> 0 THEN OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO; ELSE OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; END IF; IO_CURSOR := V_CURSOR; END OPEN_ONE_CURSOR; PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT; EMPCURSOR := V_CURSOR1; DEPTCURSOR := V_CURSOR2; END OPEN_TWO_CURSORS; END CURSPKG; /
|
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-
|