【PL/SQL】バルク処理完全ガイド｜BULK COLLECT＋FORALLで10〜100倍高速化・LIMIT分割・3構文・RETURNING・実務10パターン

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE;
  v_ids t_ids;
BEGIN
  -- ① SELECTで一括取得
  SELECT id BULK COLLECT INTO v_ids
  FROM users WHERE status = 'pending';

  -- ② FORALLで一括DML
  FORALL i IN 1..v_ids.COUNT
    UPDATE users SET status = 'active' WHERE id = v_ids(i);

  COMMIT;
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' WHERE id = v_ids(i);

    COMMIT;   -- 1000件ごとにCOMMIT（undo縮小）
  END LOOP;
  CLOSE cur;
END;

-- ❌ 遅い：Cursor FOR Loop内で1行ずつUPDATE
FOR rec IN (SELECT id FROM users WHERE status = 'pending') LOOP
  UPDATE users SET status = 'active' WHERE id = rec.id;
  --      ↑ PL/SQL→SQLの切替（コンテキストスイッチ）
END LOOP;

-- 1万件なら1万回のコンテキストスイッチ
-- 1回のスイッチは数μsだが、1万回では数秒〜数十秒のオーバーヘッド

-- ⭕ 速い：FORALLで一括
FORALL i IN 1..v_ids.COUNT
  UPDATE users SET status = 'active' WHERE id = v_ids(i);

-- コンテキストスイッチは1回のみ
-- 1万件のDMLがSQLエンジン側で一括処理される

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT id BULK COLLECT INTO v_ids
  FROM users WHERE status = 'active';

  DBMS_OUTPUT.PUT_LINE('取得件数: ' || v_ids.COUNT);

  -- 普通のFOR LOOPで走査
  FOR i IN 1..v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_ids(i));
  END LOOP;
END;

DECLARE
  TYPE t_ids     IS TABLE OF users.id%TYPE;
  TYPE t_emails  IS TABLE OF users.email%TYPE;

  v_ids    t_ids;
  v_emails t_emails;
BEGIN
  SELECT id, email BULK COLLECT INTO v_ids, v_emails
  FROM users WHERE status = 'active';

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

DECLARE
  TYPE t_user_rec IS RECORD (
    id     users.id%TYPE,
    email  users.email%TYPE,
    status users.status%TYPE
  );
  TYPE t_users IS TABLE OF t_user_rec;
  v_users t_users;
BEGIN
  SELECT id, email, status BULK COLLECT INTO v_users
  FROM users WHERE status = 'active';

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

DECLARE
  TYPE t_users IS TABLE OF users%ROWTYPE;
  v_users t_users;
BEGIN
  SELECT * BULK COLLECT INTO v_users
  FROM users WHERE status = 'active';

  -- 全列にアクセス可能
  FOR i IN 1..v_users.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_users(i).id || ': ' || v_users(i).email);
  END LOOP;
END;

-- ❌ 危険：1億件を全件ロード
SELECT id BULK COLLECT INTO v_ids FROM huge_table;
-- PGAメモリ数GB消費 → ORA-04030（out of process memory）
-- Process Crashでセッション切断

DECLARE
  TYPE t_ids IS TABLE OF huge_table.id%TYPE;
  v_ids t_ids;
  CURSOR cur IS SELECT id FROM huge_table;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_ids LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;

    -- 1バッチ分の処理
    FORALL i IN 1..v_ids.COUNT
      UPDATE huge_table SET processed = 1 WHERE id = v_ids(i);

    COMMIT;   -- バッチごとにCOMMIT
  END LOOP;
  CLOSE cur;
END;

-- INSERT一括
DECLARE
  TYPE t_emails IS TABLE OF VARCHAR2(200);
  v_emails t_emails := t_emails('a@x.com', 'b@x.com', 'c@x.com');
BEGIN
  FORALL i IN 1..v_emails.COUNT
    INSERT INTO users(email, created_at)
    VALUES (v_emails(i), SYSTIMESTAMP);

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '件INSERT');
END;

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE;
  v_ids t_ids := t_ids(1, 2, 3, 4, 5);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    UPDATE users SET status = 'active' WHERE id = v_ids(i);
END;

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE;
  v_ids t_ids := t_ids(99, 100, 101);
BEGIN
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM users WHERE id = v_ids(i);
END;

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

-- 連続したインデックス（1, 2, 3, ...）を処理
-- 歯抜けがあるとエラー

DECLARE
  TYPE t_ids IS TABLE OF users.id%TYPE INDEX BY PLS_INTEGER;
  v_ids t_ids;
BEGIN
  v_ids(10)  := 100;
  v_ids(50)  := 200;
  v_ids(300) := 300;

  -- 10, 50, 300 のインデックスだけ処理
  FORALL i IN INDICES OF v_ids
    UPDATE users SET status = 'processed' WHERE id = v_ids(i);

  -- 範囲指定も可能
  FORALL i IN INDICES OF v_ids BETWEEN 10 AND 100
    UPDATE users SET status = 'batch1' WHERE id = v_ids(i);
END;

DECLARE
  TYPE t_ids  IS TABLE OF users.id%TYPE INDEX BY PLS_INTEGER;
  TYPE t_idxs IS TABLE OF PLS_INTEGER;

  v_ids  t_ids;
  v_idxs t_idxs := t_idxs(3, 7, 11, 20);  -- 処理したいインデックス番号
BEGIN
  v_ids(3)  := 300;
  v_ids(7)  := 700;
  v_ids(11) := 1100;
  v_ids(20) := 2000;
  v_ids(99) := 9900;    -- この行は処理されない

  -- v_idxs の値（3, 7, 11, 20）をインデックスとして v_ids を参照
  FORALL i IN VALUES OF v_idxs
    UPDATE users SET status = 'filtered' WHERE id = v_ids(i);
END;

DECLARE
  TYPE t_emails IS TABLE OF VARCHAR2(200);
  v_emails t_emails := t_emails('a@x.com', 'b@x.com', NULL, 'd@x.com');
                                                     -- ↑NULL → NOT NULL制約違反
  e_bulk_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_error, -24381);
BEGIN
  FORALL i IN 1..v_emails.COUNT SAVE EXCEPTIONS
    INSERT INTO users(email) VALUES (v_emails(i));

  COMMIT;
EXCEPTION
  WHEN e_bulk_error THEN
    DBMS_OUTPUT.PUT_LINE('エラー件数: ' || SQL%BULK_EXCEPTIONS.COUNT);
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'iteration=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ', ORA-' || LPAD(SQL%BULK_EXCEPTIONS(j).ERROR_CODE, 5, '0') ||
        ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
      );
    END LOOP;
    COMMIT;   -- 成功分は保存
END;

DECLARE
  TYPE t_ids IS TABLE OF orders.id%TYPE;
  v_target_ids t_ids;
  v_returned_ids t_ids;
BEGIN
  -- DELETE対象を事前取得
  SELECT id BULK COLLECT INTO v_target_ids
  FROM orders WHERE status = 'expired' AND ROWNUM <= 1000;

  -- DELETE＋削除されたIDをRETURNING
  FORALL i IN 1..v_target_ids.COUNT
    DELETE FROM orders WHERE id = v_target_ids(i)
    RETURNING id BULK COLLECT INTO v_returned_ids;

  DBMS_OUTPUT.PUT_LINE('削除完了: ' || v_returned_ids.COUNT || '件');
  COMMIT;
END;

DECLARE
  TYPE t_ids    IS TABLE OF users.id%TYPE;
  TYPE t_status IS TABLE OF users.status%TYPE;

  v_ids        t_ids := t_ids(1, 2, 3, 4);
  v_old_statuses t_status;
BEGIN
  FORALL i IN 1..v_ids.COUNT
    UPDATE users SET status = 'active' WHERE id = v_ids(i)
    RETURNING status BULK COLLECT INTO v_old_statuses;  -- 変更前の値
    -- 注：RETURNINGはUPDATE後の値（ORA-06550に注意、後述）
END;

DECLARE
  TYPE t_depts IS TABLE OF NUMBER;
  v_depts t_depts := t_depts(10, 20, 30);
BEGIN
  FORALL i IN 1..v_depts.COUNT
    UPDATE employees SET salary = salary * 1.1
    WHERE department_id = v_depts(i);

  -- 通常のSQL%ROWCOUNTは「全FORALLの合計行数」
  DBMS_OUTPUT.PUT_LINE('合計更新行数: ' || SQL%ROWCOUNT);

  -- SQL%BULK_ROWCOUNT(i)で各iの影響行数
  FOR i IN 1..v_depts.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'dept=' || v_depts(i) || 
      ' → 更新行数=' || SQL%BULK_ROWCOUNT(i)
    );
  END LOOP;
END;

-- 10g以降、このシンプルなループは内部でBULK COLLECT 100件で動く
FOR rec IN (SELECT id, email FROM users WHERE status = 'active') LOOP
  -- rec.id, rec.email で参照可能
  DBMS_OUTPUT.PUT_LINE(rec.id || ': ' || rec.email);
END LOOP;
-- 100件ずつ暗黙的にFETCH BULK COLLECT LIMIT 100されている

MERGE INTO users u
USING (
  SELECT 1 AS id, 'a@x.com' AS email FROM dual
  UNION ALL SELECT 2, 'b@x.com' FROM dual
) src ON (u.id = src.id)
WHEN MATCHED THEN UPDATE SET u.email = src.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (src.id, src.email);

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', updated_at = SYSTIMESTAMP
      WHERE id = v_ids(i);
    COMMIT;
  END LOOP;
  CLOSE cur;
END;

DECLARE
  TYPE t_emails IS TABLE OF VARCHAR2(200);
  v_emails t_emails;
BEGIN
  -- 外部から取得したデータを配列に
  v_emails := get_emails_from_external();

  FORALL i IN 1..v_emails.COUNT SAVE EXCEPTIONS
    INSERT INTO users(email) VALUES (v_emails(i));
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      log_error(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX,
                SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
    END LOOP;
END;

DECLARE
  TYPE t_ids IS TABLE OF logs.id%TYPE;
  v_ids t_ids;
  CURSOR cur IS SELECT id FROM logs WHERE created_at < SYSDATE - 365;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_ids LIMIT 10000;
    EXIT WHEN v_ids.COUNT = 0;
    FORALL i IN 1..v_ids.COUNT
      DELETE FROM logs WHERE id = v_ids(i);
    COMMIT;
  END LOOP;
  CLOSE cur;
END;

DECLARE
  TYPE t_summary IS RECORD (
    user_id     NUMBER,
    order_count NUMBER,
    total_amt   NUMBER
  );
  TYPE t_summaries IS TABLE OF t_summary;
  v_summaries t_summaries;
BEGIN
  SELECT user_id, COUNT(*), SUM(amount)
    BULK COLLECT INTO v_summaries
    FROM orders
   WHERE order_date >= TRUNC(SYSDATE) - 30
   GROUP BY user_id;

  FORALL i IN 1..v_summaries.COUNT
    UPDATE users
    SET order_count_30d = v_summaries(i).order_count,
        total_amt_30d   = v_summaries(i).total_amt
    WHERE id = v_summaries(i).user_id;
  COMMIT;
END;

DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(100);
  TYPE t_ids   IS TABLE OF NUMBER;
  v_names t_names := t_names('Alice', 'Bob', 'Charlie');
  v_ids   t_ids;
BEGIN
  FORALL i IN 1..v_names.COUNT
    INSERT INTO users(id, name)
    VALUES (user_seq.NEXTVAL, v_names(i))
    RETURNING id BULK COLLECT INTO v_ids;

  -- v_ids に新規採番されたIDが入っている
  FOR i IN 1..v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_names(i) || ' → id=' || v_ids(i));
  END LOOP;
END;

DECLARE
  TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_ids t_ids;
BEGIN
  -- 歯抜けで格納
  v_ids(10) := 100; v_ids(20) := 200; v_ids(50) := 500;

  FORALL i IN INDICES OF v_ids
    UPDATE orders SET priority = i WHERE id = v_ids(i);
END;

DECLARE
  TYPE t_ids  IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE t_idxs IS TABLE OF PLS_INTEGER;

  v_all  t_ids;
  v_even t_idxs := t_idxs();
BEGIN
  FOR i IN 1..100 LOOP
    v_all(i) := i * 10;
  END LOOP;

  -- 偶数インデックスだけ抽出
  FOR i IN 1..100 LOOP
    IF MOD(i, 2) = 0 THEN
      v_even.EXTEND;
      v_even(v_even.COUNT) := i;
    END IF;
  END LOOP;

  FORALL i IN VALUES OF v_even
    UPDATE t SET col = v_all(i) WHERE id = i;
END;

DECLARE
  TYPE t_users IS TABLE OF users%ROWTYPE;
  v_users t_users;
BEGIN
  v_users := get_users_from_api();

  -- 存在チェックなしで一括MERGE
  FORALL i IN 1..v_users.COUNT
    MERGE INTO users u
    USING (SELECT v_users(i).id AS id, v_users(i).email AS email FROM dual) src
    ON (u.id = src.id)
    WHEN MATCHED THEN UPDATE SET u.email = src.email
    WHEN NOT MATCHED THEN INSERT (id, email) VALUES (src.id, src.email);
END;

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids;
BEGIN
  -- 特定パーティションのデータだけ取得
  SELECT id BULK COLLECT INTO v_ids
  FROM orders PARTITION (p2026_01)
  WHERE status = 'pending';

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

DECLARE
  TYPE t_records IS TABLE OF old_users%ROWTYPE;
  v_records t_records;
  CURSOR cur IS SELECT * FROM old_users;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_records LIMIT 10000;
    EXIT WHEN v_records.COUNT = 0;

    FORALL i IN 1..v_records.COUNT SAVE EXCEPTIONS
      INSERT INTO new_users(id, email, name, created_at)
      VALUES (v_records(i).id,
              LOWER(v_records(i).email),   -- 正規化
              UPPER(v_records(i).name),
              NVL(v_records(i).created_at, SYSTIMESTAMP));

    COMMIT;
  END LOOP;
  CLOSE cur;
END;