Oracle 如何使用Cursor變數REF CURSOR

Cursor變數(Cursor variable)與Cursor一樣，是指向一個查詢結果(result set)的指標(pointer)。

Cursor與Ref Cursor的差異：

• Cursor指向固定的查詢結果，是靜態不可改變的，但Cursor變數可指向不同的查詢結果，是動態可改變的。
• Cursor不能作為參數傳遞，或回傳給呼叫Procedure的client端（例如Java應用程式），而Cursor變數可作為參數在子程式中傳遞，且可回傳給client端。
• Cursor作用域可為global，而Cursor變數只能定義在子程式中。

Cursor variable可用REF CURSOR或SYS_REFCURSOR定義。

宣告data type REF CURSOR的語法如下。

TYPE type_name IS REF CURSOR

type name為型態名稱。

宣告Ref Cursor語法如下：

DECLARE
    TYPE weak_rc_type IS REF CURSOR; -- 宣告Ref Cursor型態, weak type
    v_rc_1 weak_rc_type; -- 宣告變數v_rc1的型態為weak_rc_type
      
    TYPE strong_rc_type IS REF CURSOR RETURN employee%ROWTYPE -- 宣告Ref Cursor型態, strong type
    v_rc_2 strong_rc_type; -- 宣告變數v_rc2的型態為strong_rc_type
BEGIN
    -- ...
END;

在宣告Ref Cursor時，REF CURSOR如果有定義回傳資料型態(return type)為strong type，如沒定義return type則為weak type。記得要先宣告好REF CURSOR型態後才宣告變數並指定型態為宣告的REF CURSOR型態。

Strong type的Ref Cursor執行時錯誤較少，因為PL/SQL編譯器在編譯時若發現查詢結果的資料型態與Ref Cursor定義的回傳形態不同就會先報錯。而weak type的Ref Cursor比較彈性可以是任何型態的查詢結果，因為weak type的Cursor變數在編譯時不會檢查型態，所以weak type的Cursor變數的資料型態可以改變，你可以將一個weak Cursor變數的值分配到另一個weak Cursor變數。

除了自己建立新的cursor type，也可使用定義好的SYS_REFCURSOR，這樣就不用先宣告cursor type了。SYS_REFCURSOR和weak type的Ref Cursor是一樣的東西。

DECLARE
    v_src SYS_REFCURSOR;
BEGIN
    -- ...
END;

在Package中使用時，你可以在Package spec中宣告Ref Cursor的資料型態來避免在Package子程式(就是Package中的Procedure或Function)中重複宣告相同型態的Ref Cursor。然後才在Package body的子程式中定義Cursor變數。

Ref Cursor範例，使用STUDENT資料表如下

  STUDENT_ID   STUDENT_NAME   STUDENT_HEIGHT  
 ------------ -------------- ---------------- 
  A0000001     黃小美                    145  
  A0000002     王大明                    168  
  A0000003     李志明                    172  
  A0000004     陳火旺                    178  
  A0000005     林明美                    153  

DECLARE
    TYPE t_rc IS REF CURSOR; -- 宣告REF CURSOR型態t_rc
    v_rc t_rc;  --宣告v_rc變數，型態為t_rc
    
    -- 宣告一個Procedure
    PROCEDURE SP_PRINT_STUDNET(
        i_student IN t_rc -- 傳入參數v_student型態為t_rc
    ) 
    AS 
        r_student STUDENT%ROWTYPE; -- 宣告一個Record變數，型態同STUDENT，用來裝載從Cursor變數中取出的資料
    BEGIN
        LOOP
            FETCH i_student INTO r_student; -- 抓取v_student中的一列資料放入r_student
            EXIT WHEN i_student%NOTFOUND; -- 當讀取不到資料時跳離LOOP迴圈
            DBMS_OUTPUT.PUT_LINE(
                r_student.student_id ||','|| 
                r_student.student_name ||','|| 
                r_student.student_height
            );
        END LOOP;
    END;
  
BEGIN

    OPEN v_rc FOR 
        SELECT * FROM STUDENT 
        WHERE student_height > 170; -- 將v_rc指到一個查詢結果
  
    DBMS_OUTPUT.PUT_LINE('----- 高的學生 students -----');
    SP_PRINT_STUDNET(v_rc); -- 呼叫Procedure並傳入Cursor變數v_rc
    CLOSE v_rc; -- 關閉Cursor變數

    OPEN v_rc FOR 
        SELECT * FROM STUDENT 
        WHERE student_height < 150;
  
    DBMS_OUTPUT.PUT_LINE('----- 矮的學生 students -----');
    SP_PRINT_STUDNET(v_rc); -- 呼叫Procedure並傳入Cursor變數
    CLOSE v_rc;
  
END;

印出結果如下

----- 高的學生 -----
A0000003,李志明,172
A0000004,陳火旺,178
----- 矮的學生 -----
A0000001,黃小美,145

如果覺得文章有幫助的話還幫忙點個Google廣告，感恩。

• Using Cursor Variables (REF CURSORs)
• Creating Nested Subprograms that Invoke Each Other
• Difference between cursor and a ref cursor
• [Oracle] Cursor 與 Cursor Variable 的使用