Oracle PL/SQL顯式游標(Explicit Cursor)透過LOOP提取資料

前言

顯式游標由開發者自訂與控制，包含游標的宣告、開啟、提取以及關閉等操作，常用屬性與隱式游標一樣，如下所示。

隱式游標由Oracle自動建立管理，進行如INSERT、UPDATE、DELETE以及SELECT INTO等動作皆會建立隱式游標，並由Oracle自動開啟、提取以及關閉。

開發者可透過以下常用屬性取得游標相關資訊。

%FOUND：進行如INSERT、UPDATE、DELETE以及SELECT INTO等動作時，若影響一或多筆資料，就會回傳 TRUE，否則為 FALSE。
%NOTFOUND：與 %FOUND 相反，進行如 INSERT、UPDATE、DELETE 以及SELECT INTO等動作時，若沒有影響任何資料，就會回傳TRUE，否則為FALSE。
%ISOPEN：游標是否為開啟狀態，若使用隱式游標時，則總是回傳 FALSE，因資料庫進行完SQL敘述後，會自斷關閉。
%ROWCOUNT：進行如INSERT、UPDATE、DELETE 以及 SELECT INTO等動作時，所影響的筆數。

*使用以上屬性前面須加上宣告的游標名稱，如：游標名稱%FOUND。

顯式游標的操作大致可分為以下四個步驟。

步驟一：宣告游標

顯式游標是由開發者自行操作使用，因此必須自行在「DECLARE」區塊進行宣告，宣告語法如下。

1
CURSOR 游標名稱 IS SELECT 敘述;

步驟二：開啟游標

於第一步驟中，宣告的游標給予了SELECT敘述語句，在此時還僅是屬於單純的宣告，並未實際執行SELECT敘述語句。當開啟游標時，才會實際執行宣告中的SELECT敘述語句，將結果集返回，並將內部的邏輯指標只向第一筆資料列，等待後續使用FETCH來提取。開啟游標語法如下。

1
OPEN 游標名稱;

步驟三：提取游標

此步驟則是使用FETCH提取資料，提取資料包含以下兩個步驟。

讀取目前資料列。
移動至下一個資料列。

提取資料可以使已將欄位存放至不同的變數內，亦可將資料存放至RECORD當中，再進行處理。

將資料提取至變數方式如下。

1
FETCH 游標名稱 INTO 變數1, 變數2, 變數3, .....;

步驟四：關閉游標

當游標使用完之後，應該立即關閉，進行資源的釋放，關閉游標語法如下。

1
CLOSE 游標名稱;

一般提取資料會透過迴圈協助逐一處理，以下使用LOOP迴圈進行相關範例。

使用變數方式提取

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
DECLARE
    -- 宣告變數
    ID ARTICLE.ID%TYPE;
    TITLE ARTICLE.TITLE%TYPE;
    CREATE_DATE ARTICLE.CREATE_DATE%TYPE;
    MODIFY_DATE ARTICLE.MODIFY_DATE%TYPE;
    -- 宣告游標
    CURSOR C_ARTICLE IS SELECT * FROM ARTICLE;
BEGIN
    -- 開啟游標
    OPEN C_ARTICLE;

    -- 查看游標是否開啟
    IF  C_ARTICLE%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游標已開啟');
    END IF;

    LOOP
        -- 提取資料
        FETCH C_ARTICLE INTO ID, TITLE, CREATE_DATE, MODIFY_DATE;
        -- 判斷是否有讀取到資料
        EXIT WHEN C_ARTICLE%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || ID || ', TITLE: ' || TITLE || ', CREATE_DATE: ' || CREATE_DATE || ', MODIFY_DATE: ' || NVL(TO_CHAR(MODIFY_DATE), '無'));
    END LOOP;

    -- 關閉游標
    CLOSE C_ARTICLE;

     -- 查看游標是否關閉
    IF NOT  C_ARTICLE%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游標已關閉');
    END IF;
END;

使用RECORD方式提取

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
DECLARE
    -- 宣告 RECORD
    R_ROW ARTICLE%ROWTYPE;
    -- 宣告游標
    CURSOR C_ARTICLE IS SELECT * FROM ARTICLE;
BEGIN
    -- 開啟游標
    OPEN C_ARTICLE;

    -- 查看游標是否開啟
    IF  C_ARTICLE%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游標已開啟');
    END IF;

    LOOP
        -- 提取資料
        FETCH C_ARTICLE INTO R_ROW;
        -- 判斷是否有讀取到資料
        EXIT WHEN C_ARTICLE%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || R_ROW.ID || ', TITLE: ' || R_ROW.TITLE || ', CREATE_DATE: ' || R_ROW.CREATE_DATE || ', MODIFY_DATE: ' || NVL(TO_CHAR(R_ROW.MODIFY_DATE), '無'));
    END LOOP;

    -- 關閉游標
    CLOSE C_ARTICLE;

     -- 查看游標是否關閉
    IF NOT  C_ARTICLE%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游標已關閉');
    END IF;
END;

執行結果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
游標已開啟
ID: 7C5E3A5AD847F0D3E050A8C0FD894690, TITLE: 我的第一篇 PL/SQL 筆記, CREATE_DATE: 2018-12-07 10:59:37, MODIFY_DATE: 無
ID: 7C5E3A5AD848F0D3E050A8C0FD894690, TITLE: 我的 JAVA 學習歷程, CREATE_DATE: 2018-12-07 12:13:23, MODIFY_DATE: 無
ID: 7C5E3A5AD849F0D3E050A8C0FD894690, TITLE: C# 程式架構, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84AF0D3E050A8C0FD894690, TITLE: Eclipse 程式碼提示設定, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84BF0D3E050A8C0FD894690, TITLE: TightVNC 檔案傳輸, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84CF0D3E050A8C0FD894690, TITLE: Apache Struts 2 檔案上傳, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84DF0D3E050A8C0FD894690, TITLE: Apache Struts 2 表單驗證 (驗證框架), CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84EF0D3E050A8C0FD894690, TITLE: Apache Struts 2 攔截器設定與使用, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD84FF0D3E050A8C0FD894690, TITLE: MySQL 外部 IP 連線 (Ubuntu Server), CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
ID: 7C5E3A5AD850F0D3E050A8C0FD894690, TITLE: CentOS 切換 root 免密碼設定, CREATE_DATE: 2018-12-07 12:16:20, MODIFY_DATE: 無
游標已關閉