【PL/SQL】カーソルFORループ vs 明示的カーソル完全ガイド｜10軸比較・暗黙BULK・判断フロー・実務10シナリオ

BEGIN
  FOR rec IN (SELECT id, name FROM users WHERE status = 'active') LOOP
    DBMS_OUTPUT.PUT_LINE(rec.id || ': ' || rec.name);
  END LOOP;
END;
-- OPEN/CLOSE/FETCH/%NOTFOUND が自動管理される
-- 10g以降は内部で暗黙BULK COLLECT 100件

DECLARE
  CURSOR cur_users IS
    SELECT id, name FROM users WHERE status = 'active';
BEGIN
  FOR rec IN cur_users LOOP
    DBMS_OUTPUT.PUT_LINE(rec.id || ': ' || rec.name);
  END LOOP;
END;

DECLARE
  CURSOR cur_users IS
    SELECT id, name FROM users WHERE status = 'active';
  v_rec cur_users%ROWTYPE;
BEGIN
  OPEN cur_users;
  LOOP
    FETCH cur_users INTO v_rec;
    EXIT WHEN cur_users%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.id || ': ' || v_rec.name);
  END LOOP;
  CLOSE cur_users;

EXCEPTION
  WHEN OTHERS THEN
    IF cur_users%ISOPEN THEN CLOSE cur_users; END IF;
    RAISE;
END;

-- 見た目
FOR rec IN (SELECT id FROM huge_table) LOOP
  process(rec.id);
END LOOP;

-- 内部動作（10g+）
-- OPEN cur;
-- LOOP
--   FETCH cur BULK COLLECT INTO v_batch LIMIT 100;  ← 暗黙BULK
--   EXIT WHEN v_batch.COUNT = 0;
--   FOR i IN 1..v_batch.COUNT LOOP
--     process(v_batch(i).id);
--   END LOOP;
-- END LOOP;
-- CLOSE cur;

DECLARE
  CURSOR cur IS SELECT id FROM huge_table;
  v_rec cur%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_rec;   -- 1件ずつ（ラウンドトリップ多い）
    EXIT WHEN cur%NOTFOUND;
    process(v_rec.id);
  END LOOP;
  CLOSE cur;
END;
-- Oracle 9i以前はこの書き方しか選択肢がなく、性能の観点で必要だった
-- 10g+では暗黙BULKで自動最適化されるのでFORループで十分

Q1: 結果セットを1行ずつ処理したい？
  NO  → 集合演算（SELECTのみ）で済むか検討
  YES → Q2

Q2: ループ内でINSERT/UPDATE/DELETEする？
  NO  → Q3
  YES → Q4

Q3（読み取りのみ）: LIMIT制御が必要？
  NO  → 【カーソルFORループ】（最シンプル）
  YES → 【明示的カーソル＋BULK COLLECT LIMIT】

Q4（DMLあり）: 件数は1000件以上？
  NO  → カーソルFORループ内でDMLでもOK（遅くても許容）
  YES → 【BULK COLLECT+FORALL】で10〜100倍速

Q5（FOR UPDATE使いたい）: 行ロック＋ROWID更新？
  YES → 【明示的カーソル＋WHERE CURRENT OF】 or
         【カーソルFORループで宣言したCURSORに FOR UPDATE】

Q6（結果セット返却・動的SQL）: ストアドから返す／動的SQL？
  YES → 【REF CURSOR (SYS_REFCURSOR)】

DECLARE
  CURSOR cur IS SELECT id, amount FROM orders WHERE status = 'pending'
                FOR UPDATE;
  v_rec cur%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_rec;
    EXIT WHEN cur%NOTFOUND;

    IF v_rec.amount > 10000 THEN
      UPDATE orders SET status = 'hold' WHERE CURRENT OF cur;
    ELSE
      UPDATE orders SET status = 'approved' WHERE CURRENT OF cur;
    END IF;
  END LOOP;
  CLOSE cur;
  COMMIT;
END;

DECLARE
  CURSOR cur IS SELECT id, amount FROM orders WHERE status = 'pending'
                FOR UPDATE;
BEGIN
  FOR rec IN cur LOOP
    IF rec.amount > 10000 THEN
      UPDATE orders SET status = 'hold' WHERE CURRENT OF cur;
    ELSE
      UPDATE orders SET status = 'approved' WHERE CURRENT OF cur;
    END IF;
  END LOOP;
  COMMIT;
END;

FOR rec IN (SELECT id, amount FROM orders WHERE order_date = TRUNC(SYSDATE)) LOOP
  process_report_row(rec.id, rec.amount);
END LOOP;
-- 暗黙BULK 100件で十分高速、コードもシンプル

DECLARE
  TYPE t_ids IS TABLE OF orders.id%TYPE;
  v_ids t_ids;
  CURSOR cur IS SELECT id FROM orders WHERE status = 'pending';
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_ids LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;
    FORALL i IN 1..v_ids.COUNT
      UPDATE orders SET status = 'processing' WHERE id = v_ids(i);
    COMMIT;
  END LOOP;
  CLOSE cur;
END;

-- 1行ずつのロジックが不要なら単一SQLで処理
UPDATE orders SET status = 'closed'
 WHERE status = 'pending'
   AND order_date < SYSDATE - 90;

-- パーティション指定で絞り込み
UPDATE orders PARTITION (p2026_04) SET status = 'closed'
 WHERE status = 'pending';

-- PL/SQLを使うとしてもパーティション単位で
FOR rec IN (SELECT DISTINCT partition_name
            FROM user_tab_partitions WHERE table_name = 'ORDERS') LOOP
  EXECUTE IMMEDIATE
    'UPDATE orders PARTITION (' || rec.partition_name || 
    ') SET status = ''closed'' WHERE ...';
  COMMIT;
END LOOP;

BEGIN
  FOR rec IN cur_users LOOP
    IF rec.age < 0 THEN
      RAISE_APPLICATION_ERROR(-20001, '不正な年齢');
    END IF;
    process(rec.id);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM);
    RAISE;    -- カーソルは自動CLOSE済み
END;

DECLARE
  CURSOR cur IS SELECT id FROM users;
  v_rec cur%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_rec;
    EXIT WHEN cur%NOTFOUND;
    process(v_rec.id);   -- ここで例外
  END LOOP;
  CLOSE cur;
EXCEPTION
  WHEN OTHERS THEN
    IF cur%ISOPEN THEN   -- ← 手動CLOSE必須
      CLOSE cur;
    END IF;
    log_error(SQLCODE, SQLERRM);
    RAISE;
END;

-- ❌ これはコンパイルエラー
-- FOR rec IN ('SELECT id FROM ' || p_table_name) LOOP ... END LOOP;

-- ⭕ SYS_REFCURSORで動的SQL対応
DECLARE
  v_rc SYS_REFCURSOR;
  v_id NUMBER;
BEGIN
  OPEN v_rc FOR 'SELECT id FROM ' || p_table_name || ' WHERE status = :s' 
    USING 'active';
  LOOP
    FETCH v_rc INTO v_id;
    EXIT WHEN v_rc%NOTFOUND;
    process(v_id);
  END LOOP;
  CLOSE v_rc;
END;