【Oracle】BULK COLLECT / FORALL 完全ガイド｜コンテキストスイッチ削減・大量データ高速処理・SAVE EXCEPTIONS まで解説

-- NG: 1行ごとに SQL エンジンと PL/SQL エンジンの間でコンテキストスイッチが発生する
-- 100,000行の場合、100,000回のコンテキストスイッチ → 深刻な性能問題

DECLARE
    CURSOR c_orders IS
        SELECT order_id, amount FROM orders WHERE status = 'PENDING';
BEGIN
    FOR rec IN c_orders LOOP
        UPDATE orders
        SET status = 'PROCESSED', updated_at = SYSDATE
        WHERE order_id = rec.order_id;         -- 1行ごとに SQL エンジンに制御を渡す
    END LOOP;
    COMMIT;
END;
/
-- 問題点: 100,000行なら 100,000回の SQL 実行 = 100,000回のコンテキストスイッチ

-- BULK COLLECT: SELECT 結果をコレクション（配列）に一括取得する
DECLARE
    TYPE order_id_t IS TABLE OF orders.order_id%TYPE;
    TYPE amount_t   IS TABLE OF orders.amount%TYPE;

    v_ids     order_id_t;
    v_amounts amount_t;
BEGIN
    -- BULK COLLECT INTO: SELECT 全件を一括でコレクションに格納する
    SELECT order_id, amount
    BULK COLLECT INTO v_ids, v_amounts
    FROM orders
    WHERE status = 'PENDING';

    -- v_ids.COUNT でコレクションの件数を確認できる
    DBMS_OUTPUT.PUT_LINE('件数: ' || v_ids.COUNT);

    -- コレクションを FORALL で一括 UPDATE する（次のセクション参照）
END;
/

-- %ROWTYPE を使ってレコード型のコレクションで一括取得する
DECLARE
    TYPE order_rec_t IS TABLE OF orders%ROWTYPE;
    v_orders order_rec_t;
BEGIN
    SELECT * BULK COLLECT INTO v_orders
    FROM orders WHERE status = 'PENDING';

    FOR i IN 1 .. v_orders.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_orders(i).order_id || ': ' || v_orders(i).amount);
    END LOOP;
END;
/

-- カーソルの FETCH ... BULK COLLECT INTO
DECLARE
    CURSOR c IS SELECT order_id FROM orders WHERE status = 'PENDING';
    TYPE id_t IS TABLE OF orders.order_id%TYPE;
    v_ids id_t;
BEGIN
    OPEN c;
    FETCH c BULK COLLECT INTO v_ids;   -- カーソルの全件を一括取得
    CLOSE c;
    DBMS_OUTPUT.PUT_LINE('取得件数: ' || v_ids.COUNT);
END;
/

-- LIMIT 句: 一度に取得する行数を制限する（大量データでのメモリ節約）
-- BULK COLLECT INTO ... LIMIT N → N行ずつ取得してループ処理する
DECLARE
    CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'PENDING';
    TYPE id_t  IS TABLE OF orders.order_id%TYPE;
    TYPE amt_t IS TABLE OF orders.amount%TYPE;
    v_ids  id_t;
    v_amts amt_t;
    c_limit CONSTANT PLS_INTEGER := 1000;   -- 1回に処理する行数
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO v_ids, v_amts LIMIT c_limit;
        EXIT WHEN v_ids.COUNT = 0;

        -- FORALL で一括 UPDATE（後述）
        FORALL i IN 1 .. v_ids.COUNT
            UPDATE orders
            SET status = 'PROCESSED', updated_at = SYSDATE
            WHERE order_id = v_ids(i);

        COMMIT;   -- 1000件ごとにコミット（UNDO 肥大化の防止）
        EXIT WHEN v_ids.COUNT < c_limit;   -- 最後のバッチ（LIMIT 未満）で終了
    END LOOP;
    CLOSE c;
END;
/
-- LIMIT のサイズ目安: 100〜10,000。大きすぎるとメモリを消費、小さすぎると効果が減る
-- 一般的に 500〜2,000 がバランスが取れた値

-- FORALL: コレクションの各要素に対して DML を一括実行する
-- PL/SQL ループ → SQL エンジン への移送を1回にまとめる
DECLARE
    TYPE id_t     IS TABLE OF orders.order_id%TYPE;
    TYPE status_t IS TABLE OF orders.status%TYPE;

    v_ids    id_t;
    v_status status_t;
BEGIN
    -- 対象データを BULK COLLECT で取得
    SELECT order_id, 'PROCESSED'
    BULK COLLECT INTO v_ids, v_status
    FROM orders
    WHERE status = 'PENDING' AND order_date < SYSDATE - 7;

    -- FORALL で一括 UPDATE（1回の SQL 実行で全件処理）
    FORALL i IN 1 .. v_ids.COUNT
        UPDATE orders
        SET status = v_status(i), updated_at = SYSDATE
        WHERE order_id = v_ids(i);

    -- 影響行数の確認: SQL%ROWCOUNT は FORALL 全体の合計影響行数
    DBMS_OUTPUT.PUT_LINE('更新件数（合計）: ' || SQL%ROWCOUNT);

    COMMIT;
END;
/

-- FORALL で INSERT する
DECLARE
    TYPE id_t  IS TABLE OF orders.order_id%TYPE;
    v_ids id_t := id_t(1001, 1002, 1003);   -- 直接初期化する
BEGIN
    FORALL i IN 1 .. v_ids.COUNT
        INSERT INTO order_archive (order_id, archived_at)
        VALUES (v_ids(i), SYSDATE);
    COMMIT;
END;
/

-- FORALL で DELETE する
DECLARE
    TYPE id_t IS TABLE OF orders.order_id%TYPE;
    v_ids id_t;
BEGIN
    SELECT order_id BULK COLLECT INTO v_ids
    FROM orders WHERE status = 'CANCELLED' AND order_date < SYSDATE - 365;

    FORALL i IN 1 .. v_ids.COUNT
        DELETE FROM orders WHERE order_id = v_ids(i);

    DBMS_OUTPUT.PUT_LINE('削除件数: ' || SQL%ROWCOUNT);
    COMMIT;
END;
/

-- SQL%BULK_ROWCOUNT(i): FORALL の i 番目のバインド値が影響した行数
DECLARE
    TYPE id_t IS TABLE OF orders.order_id%TYPE;
    v_ids id_t;
    v_updated NUMBER := 0;
BEGIN
    SELECT order_id BULK COLLECT INTO v_ids
    FROM orders WHERE status = 'PENDING';

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

    -- 各バインド値の影響行数を確認する
    FOR i IN 1 .. v_ids.COUNT LOOP
        v_updated := v_updated + SQL%BULK_ROWCOUNT(i);
        IF SQL%BULK_ROWCOUNT(i) = 0 THEN
            DBMS_OUTPUT.PUT_LINE('order_id=' || v_ids(i) || ' は更新されなかった（存在しない？）');
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('合計更新件数: ' || v_updated);
    COMMIT;
END;
/

DECLARE
    TYPE id_t IS TABLE OF orders.order_id%TYPE;
    v_ids id_t;

    -- SAVE EXCEPTIONS 用の例外宣言
    bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(bulk_errors, -24381);  -- ORA-24381: error(s) in array DML
BEGIN
    SELECT order_id BULK COLLECT INTO v_ids
    FROM orders WHERE status = 'PENDING';

    -- SAVE EXCEPTIONS: エラーが発生してもスキップして続行する
    FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
        UPDATE orders
        SET status = 'PROCESSED'
        WHERE order_id = v_ids(i);

    COMMIT;

EXCEPTION
    WHEN bulk_errors THEN
        -- SQL%BULK_EXCEPTIONS.COUNT: エラーが発生した件数
        DBMS_OUTPUT.PUT_LINE('エラー件数: ' || SQL%BULK_EXCEPTIONS.COUNT);

        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            -- SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: 何番目のバインド値でエラーが発生したか
            -- SQL%BULK_EXCEPTIONS(i).ERROR_CODE: Oracle のエラーコード（ORA-番号から 20000 引いた値）
            DBMS_OUTPUT.PUT_LINE(
                'エラー位置: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                ', order_id: ' || v_ids(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX) ||
                ', エラーコード: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
            );
        END LOOP;

        -- エラーがあっても正常に処理できた行はコミットする
        COMMIT;
END;
/