[Oracle] Cursor 與 Cursor Variable 的使用

何謂 Cursor ?

cursor 為 PL/SQL 中內建的一種 pointer，可用來擷取整個資料集合，並讓使用者可以逐筆資料的進行存取。

在 Oracle 中，cursor 分為兩種，分別是：

• implicit(內隱) cursor
  在 Oracle 所提供的功能中，可以發現許多 implicit cursor 的蹤跡，例如：PL/SQL 中的 FOR…LOOP。
  而這一類的 cursor 是沒有辦法透過程式去控制的，而是由 Oracle 本身來進行控制與管理。
  
  
• explicit(外顯) cursor
  此種 cursor 就可以透過開發 PL/SQL 的方式來進行控制與管理，讓管理者可以有很大的彈性來使用。

預設中，Oracle 提供了上限 50 個 cursor 可供使用，但若因為開發需求而導致同時需要更多的 cursor 時，可以透過調整起始參數「OPEN_CURSOR」來達成。

使用 Explicit Cursor

每個 explicit cursor 都會有以下幾個屬性作為程式控制之用：

• %NOTFOUND
  根據從資料集合中擷取的最後一筆資料，來決定回傳 TRUE(有資料) or FALSE(沒資料)。
  
• %FOUND
  與 %NOTFOUND 相反。
  
  
• %ROWCOUNT
  回傳資料集合中所包含的資料筆數。當 cursor 從資料集合擷取過資料後，就可以使用此屬性。
  
  
• %ISOPEN
  若是 cursor 狀態為 open，則回傳 TRUE；反之則回傳 FALSE。

為了暫時存放從 cursor 擷取出來的資料列，必須定義一個暫存的變數，而這個變數的型態通常為一列資料(row data)。

以下介紹 cursor 使用的語法： (更詳細的語法可以參考官方網站文件)

DECLARE
--宣告 cursor，並指定擷取資料集合的 SQL 語法(query_definition)
CURSOR cursor_name type IS query_definition;

--開啟 cursor(此時會根據宣告時中定義的 SQL 語法取得資料集合)
OPEN cursor_name
   LOOP
   --取得 cursor 目前所在的資料列(必須使用暫存變數來儲存取得的資料列)
   FETCH record INTO buffer_var;

   --當 cursor 已經將資料集合的資料巡覽完畢
   EXIT WHEN cursor_name%NOTFOUND;

   --各式各樣的處理工作
   ...;
   END LOOP;
--關閉 cursor(之後 cursor 就不能再繼續使用)
CLOSE cursor_name;

接著介紹使用範例：

--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
    --宣告暫存變數，儲存 cursor data
    emp_record  employees%ROWTYPE;
    
    --宣告變數，檢查所有員工是否需要重新評估績效
    all_evals BOOLEAN;
    
    --今日日期
    today DATE;
    
    --宣告 cursor
    CURSOR emp_cursor IS 
        SELECT * 
        FROM employees e 
        WHERE department_id = e.department_id;        
BEGIN
    --根據月份判斷是否重新計算績效
    today := SYSDATE;
    IF(EXTRACT(MONTH FROM today) < 6) THEN
        --僅針對新進員工計算績效
        all_evals := FALSE;
    ELSE
        --全部重新計算
        all_evals := TRUE;
    END IF;
    
    --開啟 cursor
    OPEN emp_cursor;
    
    DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department #' || department_id);
    
    LOOP
        --透過 cursor 取得一筆資料並存入暫存變數
        FETCH emp_cursor INTO emp_record;
        
        --若 cursor 已將資料取完，則跳出迴圈
        EXIT WHEN emp_cursor%NOTFOUND;
        
        IF all_evals THEN
            --呼叫之前宣告的 procedure
            add_eval(emp_record.employee_id, today);
        ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN
            --呼叫之前宣告的 procedure
            add_eval(emp_record.employee_id, today);
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
    --關閉 cursor
    CLOSE emp_cursor;
END eval_department;

使用 Cursor Variable：REF Cursor

從上面 cursor 的使用方式可以知道，由於 cursor 的使用必須寫在 subprogram 中，並無法在程式中動態的變更所擷取的資料，因此在使用上是靜態的。

然而，若要在程式中動態的改變 cursor 所擷取的資料，或是將 cursor 所取到的資料集合當作參數傳到另一個 subprogram 中，就必須改用 REF Cursor 了!

什麼是 cursor variable 呢? 簡單來說，就是「儲存指向實際 cursor 的指標的變數」，以下用一張簡單的圖來說明：

其中假設實際 cursor 所在的記憶體位址為 0XBC34AD，而 cursor variable 儲存的就是 0XBC34AD 這個記憶體位址，而非 cursor 本身。

也由於 cursor variable 有如此特性，因此假設同時有多個 cursor 同時開啟，就可以將不同的記憶體位址指定給 cursor variable，這樣就可以讓 cursor variable 很快速的取得不同 cursor 從資料庫所擷取到的不同資料。

【附註】cursor 將資料從資料庫取出後，就存放於記憶體中，接著後續的處理就會很快速。

瞭解 cursor variable 與 ref cursor 的關係後，就先來說明使用的語法：

DECLARE
--定義 ref cursor 與回傳值的資料型態(return_type)，稱為 strongly typed
--也可不定義回傳的資料型態，稱為 weakly typed
TYPE cursor_type IS REF CURSOR RETURN return_type;

--定義 cursor variable(為 cursor pointer，指向實際的 cursor)
cursor_variable cursor_type;

--定義變數儲存 cursor 回傳的資料
single_record return_type;

--使用 cursor variable 開啟 cursor，並依照 query_definition 從資料庫取得資料 
OPEN cursor_variable FOR query_definition;
LOOP
    --取得一筆資料，暫存入 single_record 中
    FETCH record INTO single_record;
    
    --資料集合已經巡覽完畢，離開迴圈
    EXIT WHEN cursor_name%NOTFOUND;
    
    --任何其他實作置於此處
    ...;
END LOOP;

--關閉 cursor
--注意! 不是 cursor_variable 喔!
CLOSE cursor_name;

有一點需要注意到的是，在定義 ref cursor 時不一定需要同時定義回傳值(return_type)。

若是有定義回傳值，則 cursor 取回來的資料為 strongly typed；反之若是沒有定義回傳值，cursor 取回的資料則為 weakly typed。

若是 strongly typed，若是在程式撰寫中發生與定義型別不符的錯誤，就可以在編譯(compile)時期發現；而若是 weakly typed，由於沒有定義回傳型別，因此在使用時可以給定任何型別，若是使用上小心點，似乎也提供了蠻大的彈性。

REF CURSOR 使用範例

首先先將 ref cursor 與其他 subprogram 的定義放到 package specification 中：

CREATE OR REPLACE PACKAGE EMP_EVAL AS
    -- 宣告 PROCEDURE 與 FUNCTION
    PROCEDURE eval_department(department_id IN employees.department_id%type);

    --計算所有人的績效
    PROCEDURE eval_everyone;

    --定義 ref cursor
    TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
END EMP_EVAL;

接著是其他 subprogram 的實作部份：

--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
--宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
emp_cursor  emp_refcursor_type;

--目前正在處理的部門ID
department_curr departments.department_id%TYPE; 
BEGIN
    department_curr := department_id;
    FOR loop_c IN 1..3 
    LOOP
        --開啟 cursor
        OPEN emp_cursor FOR
            SELECT *
            FROM employees e
            WHERE e.department_id = department_curr;
            
            
        eval_loop_control(emp_cursor);
        
        DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' || department_curr);
        
        --關閉 cursor
        CLOSE emp_cursor;
        
        department_curr := department_curr + 10;
    END LOOP;
END eval_department;


PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE) AS
BEGIN
    --新增資料
    INSERT INTO evaluations VALUES( evaluations_seq.NEXTVAL, 
                                    emp_record.employee_id, 
                                    today, 
                                    emp_record.job_id,
                                    emp_record.manager_id, 
                                    emp_record.department_id,
                                    0);
END add_eval;


PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type) AS
    --定義暫存變數，儲存透過 cursor 擷取的資料
    emp_record  employees%ROWTYPE;

    --宣告變數，檢查所有員工是否需要重新評估績效
    all_evals   BOOLEAN;
    
    --今日日期
    today   DATE;
BEGIN
    --取得今天的日期
    today := SYSDATE;
    IF(EXTRACT(MONTH FROM today) < 6) THEN
        all_evals := FALSE;
    ELSE
        all_evals := TRUE;
    END IF;
    
    LOOP
        --透過 cursor 擷取一筆資料
        FETCH emp_cursor INTO emp_record;
        
        --當 cursor 已經將資料擷取完畢，跳離迴圈
        EXIT WHEN emp_cursor%NOTFOUND;
        
        IF all_evals THEN
            add_eval(emp_record, today);
        ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN  --呼叫其他 subprogram，先忽略這邊
            add_eval(emp_record, today);
        END IF;
    END LOOP;
END　eval_loop_control;


PROCEDURE eval_everyone AS
    --宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
    emp_cursor emp_refcursor_type;
BEGIN
    --開啟 cursor，取得資料集合
    OPEN emp_cursor FOR SELECT * FROM employees;
    
    DBMS_OUTPUT.PUT_LINE('Determining the number of necessary evaluations');
    
    --呼叫 subprogram，傳入 cursor variable
    eval_loop_control(emp_cursor);
    
    DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
    --關閉 cursor (上面呼叫的 subprogram 並未把 cursor 關閉)
    CLOSE emp_cursor;
END eval_everyone;