隱式游標兩大類型
| 類型 | 觸發時機 | 存取前綴 | 典型用途 |
|---|
| DML 隱式游標 | INSERT/UPDATE/DELETE/MERGE | SQL% | 檢查影響列數、錯誤處理 |
| FOR 迴圈隱式游標 | FOR rec IN (SELECT ...) LOOP | 無（直接用迴圈變數） | 簡單查詢遍歷 |
DML 隱式游標屬性
| 屬性 | 說明 | 資料型別 |
|---|
SQL%FOUND | 最近 DML 影響至少 1 列 | BOOLEAN |
SQL%NOTFOUND | 最近 DML 未影響任何列 | BOOLEAN |
SQL%ROWCOUNT | 最近 DML 影響列數 | NUMBER |
SQL%ISOPEN | 永遠為 FALSE（隱式游標自動關閉） | BOOLEAN |
實戰範例 1：UPDATE 後檢查影響列數
SET SERVEROUTPUT ON
DECLARE
BEGIN
-- 更新所有部門名稱（示範用，實務需 WHERE 條件）
UPDATE departments
SET department_name = department_name || '_UPDATED';
-- 檢查影響列數
DBMS_OUTPUT.PUT_LINE('更新列數: ' || SQL%ROWCOUNT);
-- 條件判斷範例
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('有資料被更新');
ELSE
DBMS_OUTPUT.PUT_LINE('無資料符合條件');
END IF;
-- 故意不符合條件
UPDATE departments
SET department_name = 'TEST'
WHERE 1 = 2; -- 永不成立
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('第二次更新無影響列數: ' || SQL%ROWCOUNT);
END IF;
END;
/
實戰範例 2：MERGE 操作後檢查
DECLARE
v_updated NUMBER;
v_inserted NUMBER;
BEGIN
MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
-- MERGE 後 SQL%ROWCOUNT = 更新列數 + 插入列數
DBMS_OUTPUT.PUT_LINE('MERGE 總影響列數: ' || SQL%ROWCOUNT);
-- 若需分開統計，需改用 SQL%BULK_ROWCOUNT（FORALL）或拆分語句
END;
/
FOR 迴圈隱式游標（Cursor FOR Loop）
語法糖：自動 OPEN、FETCH、CLOSE，最適合唯讀遍歷。
基本語法
FOR record_variable IN (SELECT_statement) LOOP
-- 直接使用 record_variable.column_name
END LOOP;
範例：遍歷部門表
SET SERVEROUTPUT ON
BEGIN
FOR dept_rec IN (SELECT department_id, department_name FROM departments) LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.department_id || ': ' || dept_rec.department_name);
END LOOP;
-- 迴圈結束自動關閉游標
END;
/
進階：參數化查詢（需包裝在函數/程序中）
CREATE OR REPLACE PROCEDURE list_dept_emps(p_dept_id NUMBER) IS
BEGIN
FOR emp_rec IN (SELECT employee_id, first_name, salary
FROM employees WHERE department_id = p_dept_id) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name || ' - ' || emp_rec.salary);
END LOOP;
END;
/
-- 呼叫
EXEC list_dept_emps(30);
隱式 vs 顯式：選擇決策樹
開始
│
├─ 需要 DML (INSERT/UPDATE/DELETE) → 使用隱式游標 (SQL%ROWCOUNT 等)
│
├─ 單純查詢遍歷，邏輯簡單 → 使用 FOR 迴圈隱式游標
│
├─ 查詢需參數化、重複開啟、複雜控制流 → 使用顯式游標
│
├─ 大量資料批次處理 → 顯式游標 + BULK COLLECT + FORALL
│
└─ 動態 SQL (執行時決定查詢) → 顯式游標變數 (SYS_REFCURSOR) + OPEN FOR
常見陷阱
| 陷阱 | 說明 | 正確做法 |
|---|
| 在 FOR 迴圈中修改迴圈變數 | FOR rec IN ... LOOP rec.sal := 100; END LOOP; 編譯錯誤（唯讀） | 改用顯式游標 FETCH INTO 變數再修改 |
| FOR 迴圈中執行 COMMIT | 導致游標失效（ORA-01002） | 避免迴圈中 COMMIT；或用 FOR UPDATE + WHERE CURRENT OF |
| SQL%ROWCOUNT 在 SELECT 後使用 | SELECT 不設定 SQL%ROWCOUNT | 僅在 DML 後使用；查詢用顯式游標 %ROWCOUNT |
| 巢狀 FOR 迴圈變數衝突 | 外層內層同名變數 | 使用區塊標籤 <> FOR ... 區分 |
效能對比：隱式 FOR 迴圈 vs 顯式 BULK COLLECT
-- 隱式 FOR 迴圈：逐列 Context Switch
FOR r IN (SELECT * FROM big_table) LOOP
process(r);
END LOOP;
-- 顯式 BULK COLLECT：批次 1000 列
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_tab LIMIT 1000;
EXIT WHEN l_tab.COUNT = 0;
FORALL i IN 1..l_tab.COUNT
process(l_tab(i));
END LOOP;
CLOSE c;
-- 效能提升 10x-100x（視處理邏輯）
總結：隱式游標最佳實務
- ✅ DML 後立即檢查
SQL%ROWCOUNT/%FOUND
- ✅ 簡單唯讀遍歷用
FOR rec IN (SELECT ...)
- ✅ 避免在 FOR 迴圈中 COMMIT 或修改迴圈變數
- ✅ 大量資料、複雜邏輯、需參數化 → 改用顯式游標
- ✅ 例外處理區塊中檢查
SQL%ROWCOUNT 判斷 DML 是否執行
