【PL/SQL】ループ処理完全ガイド｜4種比較・Cursor FOR Loop・BULK COLLECT+FORALL・ラベル脱出・実務10パターン

-- ① Basic LOOP（最もシンプル、必ずEXITが必要）
LOOP
  処理;
  EXIT WHEN 条件;   -- または IF 条件 THEN EXIT; END IF;
END LOOP;

-- ② WHILE LOOP（条件が真の間繰り返し、先頭評価）
WHILE 条件 LOOP
  処理;
END LOOP;

-- ③ Numeric FOR LOOP（範囲指定、自動INC/DEC）
FOR i IN 1..10 LOOP
  処理;
END LOOP;

FOR i IN REVERSE 10..1 LOOP   -- 逆順
  処理;
END LOOP;

-- ④ Cursor FOR LOOP（クエリ結果を1行ずつ処理）
FOR rec IN (SELECT id, name FROM users WHERE status = 'A') LOOP
  DBMS_OUTPUT.PUT_LINE(rec.id || ' ' || rec.name);
END LOOP;

-- EXIT WHENで終了条件（推奨）
DECLARE
  v_count NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('iter ' || v_count);
    v_count := v_count + 1;
    EXIT WHEN v_count > 5;
  END LOOP;
END;

-- IF + EXITでも同等
LOOP
  処理;
  IF 条件 THEN
    EXIT;
  END IF;
END LOOP;

DECLARE
  v_count NUMBER := 1;
BEGIN
  WHILE v_count <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('iter ' || v_count);
    v_count := v_count + 1;   -- ← 更新忘れで無限ループ注意
  END LOOP;
END;

-- 外部状態を監視するケース
DECLARE
  v_job_status VARCHAR2(20) := 'RUNNING';
  v_wait_sec   NUMBER := 0;
BEGIN
  WHILE v_job_status = 'RUNNING' AND v_wait_sec < 600 LOOP  -- 最大10分
    SELECT status INTO v_job_status FROM batch_jobs WHERE id = p_job_id;
    DBMS_LOCK.SLEEP(5);
    v_wait_sec := v_wait_sec + 5;
  END LOOP;
END;

-- 基本（1..5を順に）
FOR i IN 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE('i=' || i);
END LOOP;
-- 出力: i=1 / i=2 / i=3 / i=4 / i=5

-- REVERSE（5..1を逆順に）
FOR i IN REVERSE 1..5 LOOP     -- ※ 書き方は 1..5 のまま
  DBMS_OUTPUT.PUT_LINE('i=' || i);
END LOOP;
-- 出力: i=5 / i=4 / i=3 / i=2 / i=1

-- 変数を範囲に使える
FOR i IN v_start..v_end LOOP
  ...
END LOOP;

-- 範囲逆転ならループ実行されない（注意）
FOR i IN 5..1 LOOP   -- REVERSE無しで逆範囲 → 実行されない
  DBMS_OUTPUT.PUT_LINE('実行されない');
END LOOP;

-- ❌ PL/SQLにSTEPやBY句は存在しない
-- FOR i IN 1..10 BY 2 LOOP  -- PLS-00103 error

-- ⭕ ワークアラウンド①：計算で飛ばす
FOR i IN 1..5 LOOP
  DECLARE
    v_val NUMBER := 1 + (i - 1) * 2;   -- 1,3,5,7,9
  BEGIN
    DBMS_OUTPUT.PUT_LINE('val=' || v_val);
  END;
END LOOP;

-- ⭕ ワークアラウンド②：MODでスキップ
FOR i IN 1..10 LOOP
  CONTINUE WHEN MOD(i, 2) = 0;   -- 偶数をスキップ
  DBMS_OUTPUT.PUT_LINE('odd=' || i);
END LOOP;

BEGIN
  FOR rec IN (
    SELECT id, email, created_at
    FROM users
    WHERE status = 'active'
    ORDER BY created_at DESC
  ) LOOP
    -- rec.id, rec.email, rec.created_at で参照
    DBMS_OUTPUT.PUT_LINE(rec.id || ' / ' || rec.email);
    -- ループ内でDML可能（同じテーブルでなければ）
    INSERT INTO user_log VALUES (rec.id, SYSDATE);
  END LOOP;
  COMMIT;
END;

DECLARE
  CURSOR cur_active_users IS
    SELECT id, email
    FROM users
    WHERE status = 'active';
BEGIN
  FOR rec IN cur_active_users LOOP
    process_user(rec.id, rec.email);
  END LOOP;
END;

DECLARE
  CURSOR cur_by_status(p_status VARCHAR2) IS
    SELECT id, email FROM users WHERE status = p_status;
BEGIN
  FOR rec IN cur_by_status('active') LOOP
    ...
  END LOOP;

  FOR rec IN cur_by_status('pending') LOOP
    ...
  END LOOP;
END;

LOOP
  処理;
  
  -- 形式①：条件付きEXIT
  EXIT WHEN 条件;
  
  -- 形式②：IF + EXIT
  IF 条件 THEN EXIT; END IF;
END LOOP;

-- 特定条件で以降の処理をスキップし次ループへ
FOR rec IN (SELECT id, amount FROM orders) LOOP
  CONTINUE WHEN rec.amount IS NULL OR rec.amount <= 0;
  
  -- 正常な行だけ処理
  process_order(rec.id);
END LOOP;

-- IF + CONTINUEでも同等
FOR rec IN cur_orders LOOP
  IF rec.status = 'cancelled' THEN
    CONTINUE;
  END IF;
  
  process_order(rec.id);
END LOOP;

<<outer_loop>>
FOR i IN 1..10 LOOP
  <<inner_loop>>
  FOR j IN 1..10 LOOP
    IF i * j > 50 THEN
      -- 内側だけ脱出
      EXIT inner_loop;
    END IF;
    
    IF some_condition THEN
      -- 外側まで一気に脱出
      EXIT outer_loop;
    END IF;
    
    IF another_condition THEN
      -- 外側の次イテレーションへ
      CONTINUE outer_loop;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(i || ',' || j);
  END LOOP inner_loop;
END LOOP outer_loop;

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids := t_ids(10, 20, 30, 40, 50);
BEGIN
  -- 空チェック必須
  IF v_ids IS NULL OR v_ids.COUNT = 0 THEN
    RETURN;
  END IF;

  FOR i IN 1..v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': ' || v_ids(i));
  END LOOP;
END;

DECLARE
  TYPE t_map IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_map t_map;
  v_idx PLS_INTEGER;
BEGIN
  v_map(10) := 'A';
  v_map(25) := 'B';
  v_map(100) := 'C';
  v_map.DELETE(10);    -- 歯抜け作成

  -- ❌ COUNTベースは使えない（インデックスが連続しない）
  -- FOR i IN 1..v_map.COUNT LOOP ...  -- NG

  -- ⭕ FIRST/NEXTで疎配列を辿る
  v_idx := v_map.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_idx || ' => ' || v_map(v_idx));
    v_idx := v_map.NEXT(v_idx);
  END LOOP;
END;

-- Oracle 10g以降：INDICES OF で疎配列も自然に走査
DECLARE
  TYPE t_map IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  v_map t_map;
BEGIN
  v_map(10) := 'A';
  v_map(25) := 'B';

  FORALL i IN INDICES OF v_map
    INSERT INTO dump_table VALUES (i, v_map(i));
  -- ↑ FORALLはバルク処理、FORではないが疎配列指定パターンとして
END;

BEGIN
  FOR rec IN (SELECT id FROM orders WHERE status = 'pending') LOOP
    UPDATE orders SET status = 'processing' WHERE id = rec.id;
    -- 1回のUPDATE＝1ラウンドトリップ、1万行なら1万回
  END LOOP;
  COMMIT;
END;

DECLARE
  TYPE t_ids IS TABLE OF orders.id%TYPE;
  v_ids t_ids;
BEGIN
  -- 1回のSELECTで全ID取得
  SELECT id BULK COLLECT INTO v_ids
  FROM orders WHERE status = 'pending';

  -- 1回のFORALLで全UPDATE
  FORALL i IN 1..v_ids.COUNT
    UPDATE orders SET status = 'processing' WHERE id = v_ids(i);

  COMMIT;
END;

-- メモリ枯渇を避けるため1000件ずつ処理
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 1000;
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1..v_ids.COUNT
      UPDATE orders SET status = 'processing' WHERE id = v_ids(i);

    COMMIT;    -- 定期的にコミットしてundo縮小
  END LOOP;
  CLOSE cur;
END;

DECLARE
  v_max_iter CONSTANT NUMBER := 100000;
  v_iter     NUMBER := 0;
BEGIN
  LOOP
    -- 本来の終了条件
    EXIT WHEN 本来条件;

    -- 安全ネット：最大回数
    v_iter := v_iter + 1;
    IF v_iter > v_max_iter THEN
      RAISE_APPLICATION_ERROR(-20001, 'ループ最大回数超過: ' || v_max_iter);
    END IF;

    -- 処理
    process();
  END LOOP;
END;

DECLARE
  v_start TIMESTAMP := SYSTIMESTAMP;
  v_elapsed_sec NUMBER;
BEGIN
  LOOP
    EXIT WHEN 本来条件;

    -- 10分経過で強制終了
    v_elapsed_sec := EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start))
                   + EXTRACT(MINUTE FROM (SYSTIMESTAMP - v_start)) * 60;
    IF v_elapsed_sec > 600 THEN
      RAISE_APPLICATION_ERROR(-20002, 'タイムアウト(10分)');
    END IF;

    process();
  END LOOP;
END;

DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT id, amount FROM orders
    WHERE order_date >= TRUNC(SYSDATE) - 30
  ) LOOP
    v_total := v_total + rec.amount;
    process_order(rec.id);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('合計: ' || v_total);
END;

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE;
  v_ids t_ids;
  CURSOR cur IS SELECT id FROM users WHERE status = 'pending';
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_ids LIMIT 1000;
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1..v_ids.COUNT
      UPDATE users SET status = 'active', activated_at = SYSDATE
      WHERE id = v_ids(i);
    COMMIT;
  END LOOP;
  CLOSE cur;
END;

DECLARE
  v_retry NUMBER := 0;
  v_max_retry CONSTANT NUMBER := 5;
  v_success BOOLEAN := FALSE;
BEGIN
  WHILE v_retry < v_max_retry AND NOT v_success LOOP
    BEGIN
      call_api(p_id);
      v_success := TRUE;
    EXCEPTION
      WHEN OTHERS THEN
        v_retry := v_retry + 1;
        DBMS_OUTPUT.PUT_LINE('retry ' || v_retry || ': ' || SQLERRM);
        DBMS_LOCK.SLEEP(2 ** v_retry);  -- 2, 4, 8, 16, 32秒
    END;
  END LOOP;

  IF NOT v_success THEN
    RAISE_APPLICATION_ERROR(-20001, 'リトライ上限到達');
  END IF;
END;

DECLARE
  v_start DATE := TRUNC(SYSDATE) - 30;
  v_count NUMBER;
BEGIN
  FOR i IN 0..29 LOOP
    SELECT COUNT(*) INTO v_count
    FROM orders
    WHERE TRUNC(order_date) = v_start + i;

    DBMS_OUTPUT.PUT_LINE((v_start + i) || ': ' || v_count);
  END LOOP;
END;

DECLARE
  TYPE t_depts IS TABLE OF NUMBER;
  v_depts t_depts := t_depts(10, 20, 30);
BEGIN
  FOR i IN 1..v_depts.COUNT LOOP
    FOR m IN 1..12 LOOP
      compute_monthly(v_depts(i), m);
    END LOOP;
  END LOOP;
END;

FOR rec IN (SELECT id, amount, status FROM transactions) LOOP
  CONTINUE WHEN rec.amount IS NULL;
  CONTINUE WHEN rec.status IN ('cancelled', 'void');
  CONTINUE WHEN rec.amount <= 0;

  -- ここに来た行だけが有効
  process_valid_tx(rec.id);
END LOOP;

DECLARE
  v_success NUMBER := 0;
  v_error   NUMBER := 0;
BEGIN
  FOR rec IN (SELECT id, data FROM import_queue) LOOP
    SAVEPOINT sp_row;
    BEGIN
      process_item(rec.id, rec.data);
      v_success := v_success + 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO sp_row;
        v_error := v_error + 1;
        log_error(SQLCODE, SQLERRM, 'id=' || rec.id);
    END;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('OK: ' || v_success || ' / NG: ' || v_error);
END;

DECLARE
  TYPE t_attrs IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
  v_attrs t_attrs;
  v_key VARCHAR2(50);
BEGIN
  v_attrs('color') := 'red';
  v_attrs('size') := 'M';
  v_attrs('price') := '1000';

  v_key := v_attrs.FIRST;
  WHILE v_key IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_key || ' = ' || v_attrs(v_key));
    v_key := v_attrs.NEXT(v_key);
  END LOOP;
END;

<<outer>>
FOR dept IN (SELECT id FROM departments) LOOP
  FOR emp IN (SELECT id FROM employees WHERE dept_id = dept.id) LOOP
    IF is_critical_error(emp.id) THEN
      EXIT outer;    -- 外側ループも抜ける
    END IF;
  END LOOP;
END LOOP outer;

DECLARE
  v_status VARCHAR2(20);
  v_waited NUMBER := 0;
  v_timeout CONSTANT NUMBER := 300;  -- 5分
BEGIN
  LOOP
    SELECT status INTO v_status FROM jobs WHERE id = p_job_id;
    EXIT WHEN v_status IN ('completed', 'failed');

    IF v_waited >= v_timeout THEN
      RAISE_APPLICATION_ERROR(-20001, 'タイムアウト');
    END IF;

    DBMS_LOCK.SLEEP(5);
    v_waited := v_waited + 5;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('終了状態: ' || v_status);
END;