您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 游標完全指南：顯式/隱式、四種循環模式、屬性與最佳實踐

編輯：Oracle 資料庫基礎

核心概念

游標：指向 SQL 查詢結果集（Context Area）的指標，讓 PL/SQL 逐行處理多行結果。
顯式游標：開發者在宣告區定義、命名、控制完整生命週期（OPEN/FETCH/CLOSE）。
隱式游標：Oracle 自動為 DML（INSERT/UPDATE/DELETE）與 SELECT INTO 建立，名稱為 SQL，自動開啟關閉。

顯式游標四大循環模式實戰

假設測試表：

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

模式一：WHILE 循環（傳統教科書寫法）

CREATE OR REPLACE PROCEDURE proc_stu_while AS
    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;
/
EXEC proc_stu_while;

模式二：IF-ELSE + LOOP（等效 WHILE）

CREATE OR REPLACE PROCEDURE proc_stu_if AS
    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;
/

模式三：FOR 循環（最簡潔、自動 OPEN/FETCH/CLOSE）

CREATE OR REPLACE PROCEDURE proc_stu_for AS
    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);
    END LOOP;
    -- 隱含 %NOTFOUND 檢查，自動關閉
END;
/
-- 甚至可直接內聯游標定義：
BEGIN
    FOR stu_rec IN (SELECT stuno, stuname FROM student ORDER BY stuno) LOOP
        DBMS_OUTPUT.PUT_LINE(stu_rec.stuno || '->' || stu_rec.stuname);
    END LOOP;
END;
/

模式四：EXIT WHEN（實務最推薦，邏輯清晰、效能最佳）

CREATE OR REPLACE PROCEDURE proc_stu_exit AS
    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;
/
-- 關鍵優勢：FETCH 在循環首行，避免重複程式碼，符合「讀取-檢查-處理」自然流程

游標屬性速查表

屬性	顯式游標	隱式游標 (SQL)	含義
%FOUND	cur_name%FOUND	SQL%FOUND	最近 FETCH/DML 影響至少一行
%NOTFOUND	cur_name%NOTFOUND	SQL%NOTFOUND	最近 FETCH/DML 未影響任何行
%ROWCOUNT	cur_name%ROWCOUNT	SQL%ROWCOUNT	已 FETCH/DML 影響的累計行數
%ISOPEN	cur_name%ISOPEN	SQL%ISOPEN (永遠 FALSE)	游標是否開啟中

隱式游標實戰：UPSERT 模式

CREATE OR REPLACE PROCEDURE proc_upsert_stu (
    p_stuno   IN student.stuno%TYPE,
    p_stuname IN student.stuname%TYPE
) AS
BEGIN
    UPDATE student SET stuname = p_stuname WHERE stuno = p_stuno;
    IF SQL%NOTFOUND THEN
        INSERT INTO student (stuno, stuname) VALUES (p_stuno, p_stuname);
    END IF;
END;
/
-- 執行
EXEC proc_upsert_stu('1104', '張燕廣');

進階主題

參數化游標

CURSOR cur_emp (p_deptno NUMBER) IS
    SELECT * FROM emp WHERE deptno = p_deptno;

BEGIN
    FOR rec IN cur_emp(10) LOOP ... END LOOP;
    FOR rec IN cur_emp(20) LOOP ... END LOOP;

REF CURSOR (弱/強型別) - 返回結果集給客戶端

-- 弱型別（動態查詢）
TYPE ref_cur IS REF CURSOR;
PROCEDURE get_data(p_id NUMBER, p_rc OUT ref_cur) AS
BEGIN
    IF p_id = 0 THEN
        OPEN p_rc FOR SELECT * FROM student;
    ELSE
        OPEN p_rc FOR SELECT * FROM student WHERE stuno = p_id;
    END IF;
END;

-- 強型別（固定回傳結構，編譯期檢查）
TYPE strong_ref_cur IS REF CURSOR RETURN student%ROWTYPE;

BULK COLLECT / FORALL - 大量資料高效處理

DECLARE
    TYPE num_arr IS TABLE OF NUMBER;
    v_ids num_arr;
BEGIN
    SELECT empno BULK COLLECT INTO v_ids FROM emp WHERE deptno = 10;
    -- 單次上下文切換取回所有資料
    
    FORALL i IN v_ids.FIRST .. v_ids.LAST
        UPDATE emp SET sal = sal * 1.1 WHERE empno = v_ids(i);
    -- 單次上下文切換執行所有 DML
END;
/
-- 關鍵：大幅減少 PL/SQL ↔ SQL 引擎切換開銷