網頁

2017/9/5

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 CURSORSYS_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廣告,感恩。


3 則留言:

  1. 作者已經移除這則留言。

    回覆刪除
  2. 您好:

    在第一段程式碼中
    TYPE cursor_type1 IS RE CURSORF; -- 宣告Ref Cursor資料型態, weak type
    'IS RE CURSORF'這一段是否打錯了

    回覆刪除
  3. @C.H. Hsieh
    對阿,不好意思,我應該是貼上的時候貼錯位置了,謝謝您的糾正。

    回覆刪除