PL/SQL 遊標變數

    遊標變數與遊標相似，有其共性，也有其不同點。就其共性來說兩者都是指向多行查詢的結果集中的當前行。都要經曆聲明，開啟，檢索與
關閉的過程。所不同的是遊標與遊標變數類似於常量與變數。遊標是靜態，而遊標變數是動態，因為遊標變數並不與某個特定的查詢相綁定。
所以，遊標變數可以開啟任何類型相容的查詢。其次可以將遊標變數作為參數傳遞給本地和儲存子程式。本文主要描述遊標變數的使用。

 

一、什麼是遊標變數
    顯示遊標用於命名一個工作區域，其中儲存多行查詢的資訊，而且該遊標始終指向工作區域的內容。而遊標變數類似於C 或Pascal 語言中
的指標，它指向一塊記憶體位址，而不是地址中的內容本身。所以，聲明一個遊標變數可以建立一個指標，而不是具體的內容。
    在PL/SQL 中，為建立遊標變數，首先需要申明一個REF CURSOR類型，然後聲明該類型的一個變數。
    為了執行多行查詢，Oracle 會開啟一個未命名的工作區來存放處理資訊。我們可以用顯式遊標為工作區命名然後訪問相關的資訊；或者聲
明指向工作區的一個遊標變數。無論在什麼地方使用遊標，它總是指向同一個查詢工作區，而遊標變數則可以指向不同的工作區。
    所以，遊標和遊標變數不能互動使用；也就是說，我們不能在該使用遊標的地方使用遊標變數，不能在該使用遊標變數的地方使用遊標。

 

二、遊標變數使用的情形
    PL/SQL 儲存子程式和各種用戶端之間可以使用遊標變數來傳遞查詢結果，這是遊標變數最主要的作用。PL/SQL 和其他用戶端程式都不擁有
結果集，它們只是共用一個指向存放結果集工作區的指標而已。例如，一個OCI 用戶端，一個Oracle Forms 應用程式和Oracle 伺服器可以引用
同一個工作區。只要有遊標變數指向查詢工作區，我們就可以引用它。因此，我們可以把遊標變數的值自由地從一個範圍傳遞到另一個。
    例如，我們把主遊標變數傳遞到嵌套在Pro*C 程式中的PL/SQL 塊，遊標變數指向的工作區就可以被訪問。
    如果用戶端含有PL/SQL 引擎，那麼從用戶端調用伺服器端就不會有什麼約束。假如我們在用戶端聲明遊標變數，在伺服器端開啟並取得數
據，然後把取得的結果返回給用戶端。這些操作都是在伺服器端完成，從而也減少了網路流量。

 

三、使用遊標變數的幾個關鍵步驟
1、定義和聲明遊標變數
  TYPE ref_type_name IS REF CURSOR [RETURN return_type];   --必須先定義REF CURSOR類型
        cursor_variable ref_type_name;                       --接下來再定義遊標變數
       
        ref_type_name:   指定自訂的類型名
        RETURN:          指定REF CURSOR返回結果的資料類型
        cursor_variable: 定義遊標變數的名字
        註：若指定RETURN子句，其資料類型必須是記錄類型，此外，不能在包規範中定義遊標變數。
            其次若指定RETURN子句則為強遊標類型，否則，為弱遊標類型。
            能夠把一個強型別與類型相容的查詢相關聯，而若類型可以與任何查詢相關聯。故強型別遊標變數出錯機率低，而弱類型更靈活。
           
2、開啟遊標變數
     當開啟遊標變數時，則此時遊標變數便與特定的SELECT語句關聯，執行該查詢，標識結果集。使用OPEN FOR可以為不同的查詢開啟相同的遊
  標變數。再次開啟它之前，無需關閉遊標變數，但之前的查詢會全部丟失。
  OPEN cursor_variable FOR select_statement;

  
3、從結果集檢索資料行
     每次從結果集檢索一次。需要注意的是強型別返回的資料類型必須與FETCH 語句中INTO所使用的變數類型相容。
  其次查詢列值的數量必須等於變數的數量，如果數量不匹配，則強型別在編譯時間出錯，而弱類型則在運行時出錯。
  FETCH cursor_variable INTO variable1,...variable2 ;                           --提取單行資料，需要配合迴圈語句來使用
    FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];    --提取多行資料，collect為集合變數

4、關閉遊標變數
  CLOSE cursor_vairable;

 

四、定義REF CURSOR與聲明遊標變數樣本  

--PL/SQL塊內聲明遊標變數DECLARE   TYPE emp_cur_type IS REF CURSOR  RETURN emp%ROWTYPE;   -->定義具有傳回型別的遊標類型，此為強型別                                                                                                                     TYPE dept_cur_type IS REF CURSOR;                      -->定義無傳回型別的遊標類型，此為弱類型                                                                                                        emp_cv    emp_cur_type;                                -->接下來聲明兩個遊標變數   dept_cv   dept_cur_type;BEGIN   NULL;END;--使用%type來定義遊標變數的傳回型別DECLARE   emp_type   emp%ROWTYPE;                                    -->定義了一個隱式記錄類型   TYPE emp_cur_type IS REF CURSOR    RETURN emp_type%TYPE;   -->定義遊標類型且使用%TYPE來返回的資料類型                                    emp_cv     emp_cur_type;                                   -->聲明遊標變數BEGIN   NULL;END;--基於自訂的記錄類型作為遊標變數的傳回型別DECLARE   TYPE emp_rec_type IS RECORD                                -->定義了一個使用者自訂的記錄類型   (      empno      NUMBER( 4 )     ,ename      VARCHAR2( 10 )     ,hiredate   emp.hiredate%TYPE   );   TYPE emp_cur_type IS REF CURSOR RETURN emp_rec_type;  --定義具有傳回型別的遊標類型，且傳回型別為使用者自訂的記錄類型   emp_cv   emp_cur_type;                                --聲明遊標變數                                                                                             BEGIN   NULL;END;--遊標變數作為函數或過程的參數DECLARE   TYPE emp_cur_type IS REF CURSOR                       -->定義一個遊標類型，其傳回型別為emp的記錄類型      RETURN emp%ROWTYPE;   emp_cur   emp_cur_type;                               -->聲明遊標變數--下面的本地過程用於處理遊標變數的結果集--注，對於遊標變數返回的結果集是一次性處理，而非對返回的每一行記錄調用一次過程   PROCEDURE process_emp_cv( emp_cv IN emp_cur_type ) IS  -->形參emp_cv使用了emp_cur_type遊標類型      person   emp%ROWTYPE;   BEGIN      DBMS_OUTPUT.put_line( '-----' );      DBMS_OUTPUT.put_line( 'Here are the names from the result set:' );      LOOP         FETCH emp_cv INTO person;         EXIT WHEN emp_cv%NOTFOUND;         DBMS_OUTPUT.put_line( 'Name = ' || person.ename || ' ' || person.hiredate );      END LOOP;   END;BEGIN   OPEN emp_cur FOR   SELECT * FROM   emp  WHERE  deptno = 10;   -->使用遊標變數開啟遊標   process_emp_cv( emp_cur );      -->調用本地過程處理開啟的遊標變數   CLOSE emp_cur;                               -->顯示關閉遊標變數   OPEN emp_cur FOR  SELECT *  FROM   emp    WHERE  job LIKE 'CLERK';   -->再次開啟遊標變數且返回了不同的結果集   process_emp_cv( emp_cur );    -->調用本地過程處理開啟的遊標變數   CLOSE emp_cur;                              -->顯示關閉遊標變數END;

五、遊標變數使用樣本

1、包中使用遊標變數CREATE PACKAGE emp_data AS   TYPE empcurtyp IS REF CURSOR      RETURN emp%ROWTYPE;   PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp );END emp_data;CREATE OR REPLACE PACKAGE BODY emp_data AS   PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp ) IS      each_emp   emp%ROWTYPE;   BEGIN      OPEN emp_cv FOR         SELECT *         FROM   emp         WHERE  ename LIKE 'A%';   END open_emp_cv;END emp_data;2、遊標變數綁定到不同的傳回型別(弱類型)CREATE PACKAGE get_data AS   TYPE ref_cur_type IS REF CURSOR;   PROCEDURE open_cv( ref_cv IN OUT ref_cur_type, choice INT );END get_data;CREATE PACKAGE BODY get_data AS   PROCEDURE open_cv (ref_cv IN OUT ref_cur_type, choice INT) IS   BEGIN      IF choice = 1 THEN         OPEN ref_cv FOR SELECT * FROM emp;      ELSIF choice = 2 THEN         OPEN ref_cv FOR SELECT * FROM dept;      ELSIF choice = 3 THEN         OPEN ref_cv FOR SELECT * FROM bonus;      END IF;   END;END get_data;3、強型別DECLARE   TYPE emp_rec_type IS RECORD          -->定義一個記錄類型   (      empno      emp.empno%TYPE     ,ename      emp.ename%TYPE     ,hiredate   emp.hiredate%TYPE   );   TYPE ref_cur_type IS REF CURSOR    -->定義了一個遊標變數且傳回型別為emp_rec_type的記錄類型      RETURN emp_rec_type;   emp_cv    ref_cur_type;                      -->聲明遊標變數   emp_rec   emp_rec_type;                      -->聲明記錄類型變數BEGIN   OPEN emp_cv FOR      SELECT empno, ename, hiredate      FROM   emp      WHERE  ename LIKE 'A%';   LOOP      FETCH emp_cv INTO emp_rec;   -->將遊標變數的結果儲存到記錄變數中      EXIT WHEN emp_cv%NOTFOUND;      DBMS_OUTPUT.put_line( 'Name = ' || emp_rec.ename || '; ' || 'Hire Date = ' || emp_rec.hiredate );   END LOOP;   CLOSE emp_cv;END;4、綁定遊標變數的結果到集合 DECLARE   TYPE emp_rec_type IS RECORD                                 -->定義一個記錄類型   (      empno      emp.empno%TYPE     ,ename      emp.ename%TYPE     ,hiredate   emp.hiredate%TYPE   );   TYPE emp_nst_type IS TABLE OF emp_rec_type                  -->定義基於記錄類型的聯合數組                           INDEX BY PLS_INTEGER;   TYPE ref_cur_type IS REF CURSOR                             -->定義遊標變數並返回記錄類型        RETURN emp_rec_type;                                     -->此處如果使用emp_nst_type會收到錯誤   emp_cv        ref_cur_type;                                 -->聲明遊標變數   emp_collect   emp_nst_type;                                 -->聲明複合資料型別變數BEGIN   OPEN emp_cv FOR      SELECT empno, ename, hiredate      FROM   emp      WHERE  ename LIKE 'A%';   FETCH emp_cv   BULK   COLLECT INTO emp_collect;                            -->使用bulk collect into將遊標記錄批量提取到複合變數中   CLOSE emp_cv;   FOR i IN emp_collect.FIRST .. emp_collect.LAST              -->輸出複合變數中的結果   LOOP      DBMS_OUTPUT.put_line( 'Name = ' || emp_collect( i ).ename || ', hiredate = ' || emp_collect( i ).hiredate );   END LOOP;END;5、SQL*Plus中操作遊標變數--下面基於前面定義的包get_data，我們在SQL*Plus中來調用包中的遊標變數並返回資料scott@CNMMBO> variable lv_ref_cv refcursor;scott@CNMMBO> variable lv_choice number;scott@CNMMBO> exec :lv_choice:=2;PL/SQL procedure successfully completed.scott@CNMMBO> exec get_data.open_cv(:lv_ref_cv,:lv_choice);PL/SQL procedure successfully completed.scott@CNMMBO> print lv_ref_cv    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON6、PL/SQL中調用包中的遊標變數DECLARE   v_ref_cv            get_data.ref_cur_type; -->聲明一個基於包的弱類型遊標變數   v_ref_cv_rec        dept%ROWTYPE;          -->由於為弱類型，所以我們使用了對應表的記錄類型作為傳回型別來聲明記錄變數                                              -->如果包中定義的為強型別遊標變數，則上面的聲明可以直接寫為return_type%rowtype   v_choice            PLS_INTEGER := 2;      BEGIN   get_data.open_cv( v_ref_cv, v_choice );   LOOP      FETCH v_ref_cv INTO v_ref_cv_rec;      EXIT WHEN v_ref_cv%NOTFOUND;      DBMS_OUTPUT.put_line( 'current rec is ' || v_ref_cv_rec.dname || ',' || v_ref_cv_rec.loc );   END LOOP;END;-->Author : Robinson Cheng-->Blog   : http://blog.csdn.net/robinson_0612current rec is ACCOUNTING,NEW YORKcurrent rec is RESEARCH,DALLAScurrent rec is SALES,CHICAGOcurrent rec is OPERATIONS,BOSTONPL/SQL procedure successfully completed.7、基於弱類型定義傳回型別導致異常DECLARE   TYPE weak_ref_cur_type IS REF CURSOR;   weak_ref_cur        weak_ref_cur_type;   weak_ref_rec        weak_ref_cur%ROWTYPE; -->產生一個 PL/SQL 320 錯誤--   weak_ref_rec        dept%ROWTYPE;       --> 正確，使用自訂的傳回型別--   weak_ref_rec        emp%ROWTYPE;     -->如果定義了與傳回型別不相容的類型則在運行時出現異常                                           -->ORA-06504: PL/SQL: Return types of Result Set variables or query do not matchBEGIN                                              OPEN weak_ref_cur FOR SELECT * FROM dept;   FETCH weak_ref_cur INTO weak_ref_rec;   DBMS_OUTPUT.put_line( 'Current Rec is ' || weak_ref_rec.dname || ',' || weak_ref_rec.loc );   CLOSE weak_ref_cur;END;ERROR at line 5:ORA-06550: line 5, column 24:PLS-00320: the declaration of the type of this expression is incomplete or malformedORA-06550: line 5, column 24:PL/SQL: Item ignoredORA-06550: line 10, column 28:PLS-00320: the declaration of the type of this expression is incomplete or malformed8、強型別編譯時間異常-->下面定義的強型別遊標變數中傳回型別不相容，則編譯時間拋出異常DECLARE   TYPE strong_ref_cur_type IS REF CURSOR      RETURN emp%ROWTYPE;   strong_ref_cur      strong_ref_cur_type;   strong_ref_rec      emp%ROWTYPE;BEGIN   OPEN strong_ref_cur FOR SELECT * FROM dept;   -->定義的傳回型別為emp%ROWTYPE，而此時的查詢為dept表類型   FETCH strong_ref_cur INTO strong_ref_rec;   CLOSE strong_ref_cur;END;ERROR at line 8:ORA-06550: line 8, column 28:PLS-00382: expression is of wrong typeORA-06550: line 8, column 4:PL/SQL: SQL Statement ignored

六、使用遊標變數注意事項
1、不能在包規範中定義遊標變數
2、不能在其它伺服器的遠程子程式中使用遊標變數，不能把遊標變數傳給通過資料庫連接被調用的過程
3、當處理遊標變數時，不要一起使用FOR UPDATE和OPEN FOR
4、不能使用比較子來測試遊標變數的等價性、不等價性或者非空性
5、遊標變數不能被賦予NULL值
6、REF CURSOR類型不能在CREATE TABLE或者VIEW語句中使用，因為不存在資料庫列的等價資料類型
7、使用遊標變數的預存程序只能被用作查詢塊資料來源，它不能用於DML塊資料來源。REF CURSOR適合於只依賴於SQL語句中(不是PL/SQL語句中)變
  量的查詢
8、不能在聯合數組、巢狀表格、或者變長數組中儲存遊標變數
9、如果向PL/SQL傳遞主機遊標變數，不能在伺服器檢索它，除非是在相同伺服器調用中開啟它
  

七、更多參考

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫