Oracle PL/SQL | Day 4 | Cursor ( 游標 ) - Rororo

Oracle 中 Cursor 是一個很好用的工具，提供了對表單進行檢所進行靈活操作的手段。就本質而言，游標實際上是一種能夠從包括多條資訊紀錄的結果每次提取出一條紀錄的機制。

Cursor 來處理數據紀錄的優點如下:

允許應用程序對查詢語句 select 返回的行結果進行進階的操作

提供對基於 Cursor 的資料進行 Update 和 Delete 的能力

Cursor 能夠把作為集合的資料庫管理系統和對於行的程式設計進行串接，進行有效的處理與溝通

Cursor 類型主要分成下面三種 :

顯示游標 : 在使用前會明確定義 Cursor的類型才會進行下一步得操作

隱示游標 : 不需要定義 Cursor 類型，基本上都是透過資料庫自動管理的

REF 游標 : REF 游標主要是在運行的時候才能確認游標使用的查詢

Cursor 使用須遵循以下步驟 :

使用 DECLARE 宣告

OPEN 語句開啟游標

FETCH … into … 語句讀取游標中的資料

CLOSE 關閉游標

練習範例

實際練習才有辦法在後續應付各種靈活的需求，以下為練習運算表達式的範例

Oracle 顯示游標

所用的table 可以參考 (link) 進行建立。

範例 1 – 讀取單筆資料

CURSOR cursor_fruits : 宣告一個游標將表的資料存進游標中
cur_fruits cursor_fruits%ROWTYPE : 定義一個游標變量 ( 用於後續存取單筆資料用 )
FETCH cursor_fruits into cur_fruits : fetch … into … 語句可以把游標的資料取出來

DECLARE
CURSOR cursor_fruits IS SELECT f_id, f_name from fruits;
cur_fruits cursor_fruits%ROWTYPE;
BEGIN
    OPEN cursor_fruits;
        FETCH cursor_fruits into cur_fruits;
        dbms_output.put_line(cur_fruits.f_id || '.' || cur_fruits.f_name);
    CLOSE cursor_fruits;
END;

範例 2 – 讀取多筆資料

cur_id fruits.f_id%type : 宣告變量類型與表中的column name 一致
EXIT WHEN fruits_loop_cur%NOTFOUND : 利用游標屬性實現沒有紀錄就跳出迴圈

DECLARE
CURSOR fruits_loop_cur IS SELECT f_id, f_name, f_price from fruits where f_price < 10;
cur_id fruits.f_id%type;
cur_name fruits.f_name%type;
cur_price fruits.f_price%TYPE;
BEGIN
    OPEN fruits_loop_cur;
        LOOP
            FETCH fruits_loop_cur into cur_id, cur_name, cur_price;
            EXIT WHEN fruits_loop_cur%NOTFOUND;
            dbms_output.put_line(cur_id || '.' || cur_name || '.' || cur_price);        
        END LOOP;
    
    CLOSE fruits_loop_cur;
END;

範例 3 – 批量讀取資料

使用 FETCH….into… 語法只能夠讀取當筆資料。如果資料量過大執行的效率就會變低，因此為了解決這個問題可以使用 FETCH… BULK COLLECT INTO… 和 FOR 語法來批量讀取資料。

type frt_tab is table of fruits%rowtype : 宣告一個 type 是來至於某個table的
FETCH fruits_collect_cur BULK COLLECT INTO fruits_rd LIMIT 2 : 每次提取兩筆資料( 有點像 python pandas DataFrame 批次提取用法 )
FOR i in 1.. fruits_rd.count LOOP … END LOOP; : 將匯入的兩筆資料遞迴秀出結果

DECLARE
CURSOR fruits_collect_cur is select * from fruits where f_price < 10;
type frt_tab is table of fruits%rowtype;
fruits_rd frt_tab;
BEGIN
    OPEN fruits_collect_cur;
        LOOP
            FETCH fruits_collect_cur BULK COLLECT INTO fruits_rd LIMIT 2;
            FOR i in 1.. fruits_rd.count LOOP
                dbms_output.put_line(fruits_rd(i).f_id || fruits_rd(i).f_name || fruits_rd(i).f_price);
            END LOOP;
            EXIT WHEN fruits_collect_cur%NOTFOUND;
        END LOOP;
    CLOSE fruits_collect_cur;
END;

範例 4 – 遞迴游標提取資料

Oracle PL/SQL可以通過使用 CURSOR… FOR… LOOP 語法可以在不聲明變數的情況下提取資料，從而簡短程式長度。

FOR curfruit in fruit LOOP : 類似程式語言的foreach 用法, 把資料的值放置 curfruit 中

DECLARE
CURSOR fruit is select * from fruits where f_price <10;
BEGIN
    FOR curfruit in fruit LOOP
        dbms_output.put_line(curfruit.f_id || '.' || curfruit.f_name || '.' || curfruit.f_price);
    END LOOP;
END;

備註:

Oracle 特殊屬性

%ISOPEN : 判斷游標是否打開
%FOUND : 檢查是否有資料
%NOTFOUND : 檢查是否沒有資料
%ROWCOUNT : 判斷游標內有多少筆資料