Oracle 顯式與隱式游標完整使用指南：四大迴圈模式與最佳實踐

游標分類速覽

顯式游標標準四步驟

1. 宣告：CURSOR c_name IS SELECT ...
2. 開啟：OPEN c_name;
3. 提取：FETCH c_name INTO variables | record;
4. 關閉：CLOSE c_name;

測試環境建立

CREATE TABLE student (
    stuname VARCHAR2(10),
    stuno   VARCHAR2(4),
    age     NUMBER,
    gender  VARCHAR2(2)
);

四種迴圈處理模式詳細範例

模式一：WHILE 迴圈（傳統教學式）

CREATE OR REPLACE PROCEDURE proc_stu1 AS
BEGIN
    DECLARE
        CURSOR cur_stu IS SELECT stuno, stuname FROM student ORDER BY stuno;
        v_stuno   VARCHAR2(4);
        v_stuname VARCHAR2(20);
    BEGIN
        OPEN cur_stu;
        FETCH cur_stu INTO v_stuno, v_stuname;
        WHILE cur_stu%FOUND LOOP
            DBMS_OUTPUT.PUT_LINE(v_stuno || '->' || v_stuname);
            FETCH cur_stu INTO v_stuno, v_stuname;
        END LOOP;
        CLOSE cur_stu;
    END;
END;

模式二：IF-ELSE 替代 WHILE（邏輯等價）

CREATE OR REPLACE PROCEDURE proc_stu2 AS
BEGIN
    DECLARE
        CURSOR cur_stu IS SELECT stuno, stuname FROM student ORDER BY stuno;
        v_stuno   VARCHAR2(4);
        v_stuname VARCHAR2(20);
    BEGIN
        OPEN cur_stu;
        FETCH cur_stu INTO v_stuno, v_stuname;
        LOOP
            IF cur_stu%FOUND THEN
                DBMS_OUTPUT.PUT_LINE(v_stuno || '->' || v_stuname);
                FETCH cur_stu INTO v_stuno, v_stuname;
            ELSE
                EXIT;
            END IF;
        END LOOP;
        CLOSE cur_stu;
    END;
END;

模式三：FOR 迴圈（最推薦，隱式管理）

CREATE OR REPLACE PROCEDURE proc_stu3 AS
BEGIN
    DECLARE
        CURSOR cur_stu IS SELECT stuno, stuname FROM student ORDER BY stuno;
    BEGIN
        FOR stu_rec IN cur_stu LOOP
            DBMS_OUTPUT.PUT_LINE(stu_rec.stuno || '->' || stu_rec.stuname);
            -- 隱式檢測 %NOTFOUND，自動 OPEN/CLOSE
        END LOOP;
    END;
END;

優勢：程式碼最簡潔、不易忘記關閉、記錄變數自動匹配欄位。

模式四：EXIT WHEN（實務最常用，效能最佳）

CREATE OR REPLACE PROCEDURE proc_stu4 AS
BEGIN
    DECLARE
        CURSOR cur_stu IS SELECT stuno, stuname FROM student ORDER BY stuno;
        v_stuno   VARCHAR2(4);
        v_stuname VARCHAR2(20);
    BEGIN
        OPEN cur_stu;
        LOOP
            FETCH cur_stu INTO v_stuno, v_stuname;
            EXIT WHEN cur_stu%NOTFOUND;  -- 提取後立即判斷，避免多餘判斷
            DBMS_OUTPUT.PUT_LINE(v_stuno || '->' || v_stuname);
        END LOOP;
        CLOSE cur_stu;
    END;
END;

關鍵差異：FETCH 放在迴圈開頭，EXIT WHEN 緊隨其後，邏輯清晰且僅一次條件判斷。

隱式游標實戰：UPSERT 模式

CREATE OR REPLACE PROCEDURE proc_upsert AS
BEGIN
    UPDATE student SET stuname = '張燕廣' WHERE stuno = '1104';
    IF SQL%NOTFOUND THEN  -- 隱式游標屬性檢測 UPDATE 影響行數
        INSERT INTO student (stuno, stuname, age, gender)
        VALUES ('1104', '張燕廣', 18, '男');
    END IF;
END;

隱式游標屬性（SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN）僅在最近一條 DML 執行後有效。

最佳實踐總結

• 優先使用 FOR 迴圈：除非需複雜控制邏輯，否則最安全簡潔。
• 大數據量採用 BULK COLLECT + FORALL：減少上下文切換，效能提升數量級。
• 嚴禁在迴圈中 COMMIT：導致 ORA-01555 快照過舊風險，應批次提交。
• 參數化游標：CURSOR c(p_deptno NUMBER) IS SELECT ... WHERE deptno = p_deptno; 促進重用與軟解析。
• 游標變數：SYS_REFCURSOR 實現動態查詢與跨程序傳遞。