PL/SQLでテーブルを同期する

他システムなど外部からデータを取り込み加工して本テーブルに取り込みたい場合というのは非常に多い。非常に多いにも関わらず、SQLでこのような処理を書くのは意外と難しい。いろいろと試行錯誤した結果、FULL JOINを使う方法に落ち着いた。

FOR cur_rec IN (
    SELECT
        S.キー１,
        S.キー２,
        S.値１,
        S.値２,
        S.値３,
        D.UPDATE_ROWID,
        (CASE
            WHEN D.キー１ IS NULL THEN 'I' -- 登録
            WHEN S.キー１ IS NULL THEN 'D' -- 削除
            WHEN S.値１ || 'X' <> D.値１ || 'X'
                OR S.値２ || 'X' <> D.値２ || 'X'
                OR S.値３ || 'X' <> D.値３ || 'X'
                THEN 'U' -- 更新
        END) AS UPDATE_STATE
    FROM
        (
            -- 同期したいデータセットを取得するSQLを書く
            SELECT
                DMT1.キー１,
                DMT2.キー２,
                DMT1.値１,
                DMT1.値２,
                DMT2.値３
            FROM
               同期元テーブル１ DMT1
               INNER JOIN 同期元テーブル２ DMT2
                  ON ...
            WHERE
               ...
        ) S
        FULL JOIN (
            -- 同期するデータセットを取得するSQLを書く
            SELECT
                DST.ROWID AS UPDATE_ROWID,
                DST.キー１,
                DST.キー２,
                DST.値１,
                DST.値２,
                DST.値３
            FROM
                同期先テーブル DST
        ) D
            ON D.キー１ = S.キー１
            AND D.キー２ = S.キー２
) LOOP
    IF cur_rec.UPDATE_STATE = 'I' THEN
        INSERT INTO 同期先テーブル (
            ...
        ) VALUE (
            ...
        );
    ELSIF cur_rec.UPDATE_STATE = 'U' THEN
        UPDATE 同期先テーブル
        SET
            ...
        WHERE
            ROWID = cur_rec.UPDATE_ROWID;
    ELSIF cur_rec.UPDATE_STATE = 'D' THEN
        DELETE FROM 同期先テーブル
        WHERE
            ROWID = cur_rec.UPDATE_ROWID;
    END IF;
END LOOP;

この手の処理では、SELECT INSERT や MERGE 句が使われることが多いが、ログ出力や存在チェックなどを考えるとこちらの書き方の方が柔軟だと思う。