2008年12月18日 星期四

[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;

沒有留言:

張貼留言