【PL/SQL】マルチテーブルINSERTの使い方 | コーディングライフスタイル

OracleのマルチテーブルINSERTは、1つの SELECT 結果をもとに複数の表へINSERTできる構文です。INSERT ALL なら条件に合う挿入先へ複数投入でき、INSERT FIRST なら最初に一致した挿入先だけへ投入できます。ETL、ステージング表からの仕分け、アーカイブ表への同時保存、集計用テーブルへの派生投入などで便利です。

ただし、マルチテーブルINSERTは便利な反面、シーケンス、RETURNING句、エラーログ、並列化まわりに制約があります。この記事では、PL/SQLから安全に使うための基本形、避けるべき書き方、親子登録の現実的な設計までまとめます。通常のINSERT文全体は SQLのINSERT文の使い方、INSERT INTO ... SELECT の基礎は INSERT INTO…SELECT完全ガイドも参考にしてください。

この記事のポイント

INSERT ALL と INSERT FIRST の違い
PL/SQLブロックから実行するときのトランザクション設計
マルチテーブルINSERTで避けたいシーケンス利用
親子テーブルへ登録する安全な代替パターン
DBMS_ERRLOG を使う場合の考え方
FORALLやMERGEと使い分ける判断基準

マルチテーブルINSERTとは
INSERT ALLの基本形
INSERT FIRSTで条件分岐する
PL/SQLから実行する基本形
シーケンスをINSERT ALL内で使わない
親子登録は事前採番してからINSERT ALLする
DBMS_ERRLOGを使うときの考え方
FORALLやMERGEとの使い分け
制約と注意点
バッチ処理では処理済みフラグまで設計する
投入後の確認SQL
よくある質問
まとめ

マルチテーブルINSERTとは

マルチテーブルINSERTは、1つのソースSELECTから複数の INTO 句へ行を流す構文です。アプリ側やPL/SQL側で1行ずつループして複数INSERTするより、SQLとしてまとめて処理できるため、大量データの移送や仕分け処理では読みやすさと性能の両方で有利になることがあります。

INSERT ALL条件に合うすべてのINTO句を実行します。同じ行を複数表へ入れたい場合に使います。

INSERT FIRST上から順に条件を評価し、最初に一致したINTO句だけを実行します。分類や優先順位付きの仕分けに向いています。

PL/SQLからの扱い通常の静的SQLとしてBEGIN-END内に書けます。COMMIT/ROLLBACKはバッチ単位で設計します。

INSERT ALLの基本形

INSERT ALL は、同じ入力行を複数の挿入先へ入れるときに使います。以下は、ステージング表から本番表とアーカイブ表へ同じ売上データを同時投入する例です。

insert-all-basic.sql

INSERT ALL
  INTO sales_main    (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
  INTO sales_archive (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
SELECT sale_id, amount, sale_dt
FROM staging_sales
WHERE load_status = 'READY';

ポイントは、SELECT 側で必要な列をそろえ、各 VALUES 句ではSELECT結果の列名を参照することです。複雑な式を各INTO句へ直接書き散らすより、SELECT側で別名を付けておくと読みやすくなります。

insert-all-with-alias.sql

INSERT ALL
  INTO sales_main    (sale_id, amount, sale_day) VALUES (sid, amt, sale_day)
  INTO sales_summary (sale_day, amount)          VALUES (sale_day, amt)
SELECT
  s.sale_id AS sid,
  s.amount  AS amt,
  TRUNC(s.sale_time) AS sale_day
FROM staging_sales s
WHERE s.load_status = 'READY';

INSERT FIRSTで条件分岐する

INSERT FIRST は、最初に一致した条件だけを実行します。金額帯やステータスで1つの投入先へ振り分けたい場合に使いやすい構文です。条件の順序がそのまま優先順位になるため、大きい条件・例外条件を先に置くのが基本です。

insert-first-conditional.sql

INSERT FIRST
  WHEN amount >= 100000 THEN
    INTO sales_large (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
  WHEN amount >= 10000 THEN
    INTO sales_middle(sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
  ELSE
    INTO sales_small (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
SELECT sale_id, amount, sale_dt
FROM staging_sales
WHERE load_status = 'READY';

順序に注意: INSERT FIRST では、先に一致した条件で処理が終わります。amount >= 10000 を先に書くと、amount >= 100000 の行も中額扱いになります。

PL/SQLから実行する基本形

PL/SQLからは、通常のINSERT文と同じように静的SQLとして実行できます。バッチIDや処理対象日などはバインド変数・PL/SQL変数として使い、コミットは処理単位で明示します。

plsql-multitable-insert.sql

DECLARE
  v_batch_id staging_sales.load_batch_id%TYPE := :batch_id;
BEGIN
  INSERT ALL
    INTO sales_main    (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
    INTO sales_archive (sale_id, amount, sale_dt) VALUES (sale_id, amount, sale_dt)
  SELECT sale_id, amount, sale_dt
  FROM staging_sales
  WHERE load_batch_id = v_batch_id
    AND load_status = 'READY';

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

大量データを扱う場合は、1回の巨大トランザクションにせず、バッチID、日付範囲、パーティションキーなどで単位を切ります。ただし、細かくコミットしすぎると途中失敗時の再実行設計が難しくなるため、業務上の再実行単位と合わせるのが現実的です。

シーケンスをINSERT ALL内で使わない

マルチテーブルINSERTで特に危ないのが、NEXTVAL / CURRVAL を使った親子同時登録です。OracleのSQLリファレンスでは、マルチテーブルINSERTにはシーケンス利用に関する制約があります。そのため、次のような書き方を「親と子を一気に作れる便利技」として載せるのは避けたほうが安全です。

avoid-sequence-in-insert-all.sql

-- 避けたい例: INSERT ALL内でNEXTVAL/CURRVALに依存する
INSERT ALL
  INTO orders(order_id, customer_id, order_dt)
       VALUES (orders_seq.NEXTVAL, customer_id, order_dt)
  INTO order_items(order_id, item_id, qty)
       VALUES (orders_seq.CURRVAL, item_id, qty)
SELECT customer_id, order_dt, item_id, qty
FROM order_staging
WHERE load_status = 'READY';

親子関係を扱う場合は、マルチテーブルINSERTの前にキーを確定しておく設計にします。シーケンスの基本は Oracleシーケンスの作成方法にもまとめています。

親子登録は事前採番してからINSERT ALLする

親子テーブルを扱う実務では、まず作業表に親キーを採番し、そのキーを使って親行・子行を作る形が安定します。以下は、注文番号単位で order_id を事前に採番する例です。

prepare-order-keys.sql

-- 1. 注文単位のキー作業表を用意する
CREATE GLOBAL TEMPORARY TABLE order_key_work (
  temp_order_no VARCHAR2(50) PRIMARY KEY,
  order_id      NUMBER NOT NULL
) ON COMMIT DELETE ROWS;

-- 2. マルチテーブルINSERTの外側で採番する
INSERT INTO order_key_work(temp_order_no, order_id)
SELECT temp_order_no, orders_seq.NEXTVAL
FROM (
  SELECT DISTINCT temp_order_no
  FROM order_staging
  WHERE load_batch_id = :batch_id
    AND load_status = 'READY'
);

採番済みの作業表を使えば、マルチテーブルINSERTの中ではただの列値として order_id を扱えます。親行と子行を同じSELECT結果に整形してから投入するのがコツです。

parent-child-safe-insert-all.sql

INSERT ALL
  WHEN row_kind = 'ORDER' THEN
    INTO orders(order_id, customer_id, order_dt)
    VALUES(order_id, customer_id, order_dt)
  WHEN row_kind = 'ITEM' THEN
    INTO order_items(order_id, item_id, qty, price)
    VALUES(order_id, item_id, qty, price)
SELECT *
FROM (
  SELECT
    'ORDER' AS row_kind,
    k.order_id,
    MAX(s.customer_id) AS customer_id,
    MAX(s.order_dt) AS order_dt,
    CAST(NULL AS NUMBER) AS item_id,
    CAST(NULL AS NUMBER) AS qty,
    CAST(NULL AS NUMBER) AS price
  FROM order_staging s
  JOIN order_key_work k ON k.temp_order_no = s.temp_order_no
  WHERE s.load_batch_id = :batch_id
  GROUP BY k.order_id

  UNION ALL

  SELECT
    'ITEM' AS row_kind,
    k.order_id,
    NULL AS customer_id,
    NULL AS order_dt,
    s.item_id,
    s.qty,
    s.price
  FROM order_staging s
  JOIN order_key_work k ON k.temp_order_no = s.temp_order_no
  WHERE s.load_batch_id = :batch_id
);

実務メモ: この例は「キーを先に確定する」考え方を示すためのものです。実際には、親の一意性、子明細の重複、再実行時の削除・スキップ条件、制約チェックの順序まで合わせて設計してください。

DBMS_ERRLOGを使うときの考え方

元記事では各 INTO 句の末尾に LOG ERRORS を付ける例がありましたが、これは避けたほうがよい書き方です。エラーログを重視する場合は、まず単一テーブルINSERTで DBMS_ERRLOG を使う形を基本にし、表ごとに失敗行を分けて追跡したい処理は、無理に1つのINSERT ALLへまとめず分割するほうが運用しやすくなります。詳しくは DBMS_ERRLOGの使い方も参照してください。

split-for-error-logging.sql

BEGIN
  -- 本番表への投入とエラーログ
  INSERT INTO sales_main(sale_id, amount, sale_dt)
  SELECT sale_id, amount, sale_dt
  FROM staging_sales
  WHERE load_batch_id = :batch_id
  LOG ERRORS INTO err$_sales_main('LOAD_MAIN') REJECT LIMIT UNLIMITED;

  -- アーカイブ表への投入とエラーログ
  INSERT INTO sales_archive(sale_id, amount, sale_dt)
  SELECT sale_id, amount, sale_dt
  FROM staging_sales
  WHERE load_batch_id = :batch_id
  LOG ERRORS INTO err$_sales_archive('LOAD_ARCHIVE') REJECT LIMIT UNLIMITED;

  COMMIT;
END;
/

マルチテーブルINSERTの利点は「1回のSELECTで複数表へ流せること」ですが、失敗行の追跡、再実行、部分成功の扱いが重要なバッチでは、あえて分割したほうが保守しやすいことがあります。

FORALLやMERGEとの使い分け

マルチテーブルINSERTは万能ではありません。すでにPL/SQLコレクションへデータを持っているなら FORALL、既存行があれば更新・なければ登録したいなら MERGE、単純な一括投入なら INSERT INTO ... SELECT が向くこともあります。大量処理全体の考え方は BULK COLLECTとFORALLによる一括処理や PL/SQLバルク処理完全ガイドも参考になります。

INSERT ALL / FIRST1つのSELECT結果を複数表へ仕分けるときに向いています。

FORALLPL/SQL配列を使って同種のDMLを大量実行するときに向いています。

MERGE登録と更新を同時に扱うUPSERTに向いています。

通常のINSERT SELECT投入先が1つで十分なら、こちらのほうがシンプルです。

制約と注意点

OracleのマルチテーブルINSERTには、通常のINSERTとは違う制約があります。代表的な注意点を実務目線で整理します。

RETURNING句は使わない投入後のキーを返してほしい処理には向きません。キーが必要なら事前採番します。

シーケンス利用に注意INSERT ALL内でNEXTVAL/CURRVALに依存する親子登録は避けます。

ビューやリモート表に注意対象にできるオブジェクトには制限があります。DBリンク越しの投入にも注意が必要です。

並列化は条件次第対象表の種類や索引構成によっては並列化されません。

エラー処理を設計する失敗行の追跡が重要なら、INSERT ALLより分割INSERTのほうが扱いやすい場合があります。

バッチ処理では処理済みフラグまで設計する

PL/SQLからマルチテーブルINSERTを使う場合、INSERT文だけで終わらせず、ステージング表の状態管理まで決めておくと運用しやすくなります。代表的には、処理対象を READY、処理中を PROCESSING、成功を DONE、失敗を ERROR のように分けます。これにより、途中失敗したバッチをどこから再実行するか判断しやすくなります。

mark-processing-before-insert.sql

-- 先に今回処理する行を固定する
UPDATE staging_sales
SET load_status = 'PROCESSING',
    process_started_at = SYSTIMESTAMP
WHERE load_batch_id = :batch_id
  AND load_status = 'READY';

-- この後のINSERT ALLではPROCESSINGだけを対象にする
INSERT ALL
  INTO sales_main    (sale_id, amount, sale_dt, load_batch_id)
  VALUES (sale_id, amount, sale_dt, load_batch_id)
  INTO sales_archive (sale_id, amount, sale_dt, load_batch_id)
  VALUES (sale_id, amount, sale_dt, load_batch_id)
SELECT sale_id, amount, sale_dt, load_batch_id
FROM staging_sales
WHERE load_batch_id = :batch_id
  AND load_status = 'PROCESSING';

INSERT後は、投入件数を確認してからステージング表を DONE に更新します。単純にCOMMITするだけだと、後から「どの行まで処理済みか」が追いづらくなります。

mark-done-after-insert.sql

-- 件数検証などが終わってから成功扱いにする
UPDATE staging_sales
SET load_status = 'DONE',
    process_finished_at = SYSTIMESTAMP
WHERE load_batch_id = :batch_id
  AND load_status = 'PROCESSING';

COMMIT;

再実行の考え方: 失敗時に PROCESSING のまま残った行を、そのまま再投入するのか、READY に戻すのか、投入済みデータを削除してからやり直すのかを決めておきます。本番バッチでは、INSERT文の正しさと同じくらい再実行手順が重要です。

投入後の確認SQL

バッチ処理では、投入件数を表ごとに確認できるようにしておくと、再実行や障害調査が楽になります。ステージング表に load_batch_id を持たせ、投入先にも追跡用のバッチIDを残す設計がおすすめです。

verify-insert-results.sql

SELECT 'SALES_MAIN' AS table_name, COUNT(*) AS row_count
FROM sales_main
WHERE load_batch_id = :batch_id
UNION ALL
SELECT 'SALES_ARCHIVE' AS table_name, COUNT(*) AS row_count
FROM sales_archive
WHERE load_batch_id = :batch_id
UNION ALL
SELECT 'ERR$_SALES_MAIN' AS table_name, COUNT(*) AS row_count
FROM err$_sales_main
WHERE ora_err_tag$ = 'LOAD_MAIN';