Oracle 游標完整教學：隱式/顯式游標、FOR 迴圈、參數化游標、游標變數與 BULK COLLECT

為什麼需要游標？

SELECT ... INTO 僅支援**單行**結果。多行結果需用游標逐行處理，避免 TOO_MANY_ROWS 異常。

隱式游標

• DML (INSERT/UPDATE/DELETE) 或單行查詢時自動建立，名為 SQL。
• 屬性：SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN (永遠 FALSE)。

DELETE FROM t WHERE id = 1;
IF SQL%ROWCOUNT > 0 THEN COMMIT; END IF;

顯式游標：四步驟標準流程

1. 宣告： CURSOR c_name IS SELECT ...;
2. 開啟： OPEN c_name;
3. 擷取： FETCH c_name INTO var1, var2 ...;（每次移至下一行）
4. 關閉： CLOSE c_name;（釋放資源，防洩漏導致資料庫掛點）

屬性：c_name%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN。

游標 FOR 迴圈：隱式開啟/關閉/擷取

FOR rec IN (SELECT * FROM emp WHERE deptno = 10) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.ename);
END LOOP;
-- 或使用已宣告游標
FOR rec IN c_emp LOOP ... END LOOP;

自動處理 OPEN/FETCH/CLOSE，rec 為 %ROWTYPE 記錄變數，程式碼最簡潔。

參數化游標：動態條件

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

OPEN c_emp(20);
-- 或 FOR 迴圈
FOR rec IN c_emp(30) LOOP ... END LOOP;

參數僅用於查詢條件，**不可用於表名/欄位名**。

游標變數：REF CURSOR（動態游標）

靜態游標固定查詢；游標變數為指標，可指向不同查詢。

1. 定義型別：TYPE rc_t IS REF CURSOR [RETURN rec_type];（強/弱型別）
2. 宣告變數：v_cur rc_t;
3. 開啟：OPEN v_cur FOR SELECT ...; 或 OPEN v_cur FOR v_sql USING ...;
4. 擷取/關閉：同靜態游標。

PROCEDURE get_data (p_sql VARCHAR2, p_refcur OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_refcur FOR p_sql;
END;

SYS_REFCURSOR 為內建弱型別 REF CURSOR，跨 Schema 傳遞最通用。

BULK COLLECT：批次擷取大幅降低 Context Switch

TYPE t_tab IS TABLE OF emp%ROWTYPE;
v_arr t_tab;

SELECT * BULK COLLECT INTO v_arr FROM emp WHERE deptno = 10;
-- 或游標
FETCH c_emp BULK COLLECT INTO v_arr LIMIT 1000;  -- 分批，防記憶體暴增

• 所有 INTO 變數必須為集合型別。
• 搭配 LIMIT 分批處理百萬行，平衡記憶體與效能。
• FORALL 批次 DML 為寫入對應優化。

實戰範例：動態 SQL + 參數化游標變數

CREATE OR REPLACE PROCEDURE query_dynamic (p_deptno NUMBER, p_rc OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(1000) := 'SELECT * FROM emp WHERE deptno = :d';
BEGIN
  OPEN p_rc FOR v_sql USING p_deptno;
END;

總結

游標 = 多行結果集的迭代器。優先使用 **FOR 迴圈**（簡潔安全），動態條件用 **參數化游標**，跨邊界傳遞用 **SYS_REFCURSOR**，大量資料用 **BULK COLLECT LIMIT + FORALL**。嚴守 **OPEN → FETCH → CLOSE** 生命週期，杜絕游標洩漏。