不思議な呪文MERGE
Oracle DatabaseにはMERGEというDMLがあります、DMLなのでINSERTやDELETEなんかの仲間です。 処理をごちゃまぜ(マージ)にできるというDMLです。
四の五の言わず、実例を挙げましょう。 こんな[商品TBL]を用意します(*が主キー)
│品名* │価格│
│りんご│100 │
│みかん│80 │
│ばなな│90 │
これに、下のような[新価格TBL]の情報を加えたいとしましょう。 新価格は価格変動があった品物もしくは、新しく扱う品物の情報が入ってくるとします。
│品名 │新価格│
│りんご│110 │
│めろん│980 │
この例だと「りんご」は価格更新(UPDATE)、「めろん」は新商品(INSERT)として扱いたいと思います。 テーブルを結合させた無名カーソルを用いて、旧価格が取れた(NOT NULL)なら更新、そうでなければ追加…というロジックでPL/SQLで素直にコーディングしてみました。
FOR r価格比較 IN
(SELECT N.品名,N.新価格,S.価格 元価格 FROM 新価格TBL N
LEFT JOIN 商品TBL S
ON S.品名 = N.品名
)
LOOP
IF r価格比較.元価格 IS NOT NULL THEN
UPDATE 商品TBL
SET 価格 = r価格比較.新価格
WHERE 品名 = r価格比較.品名
ELSE
INSERT INTO 商品TBL (品名,価格)
VALUES (r価格比較.品名,r価格比較.新価格)
END IF;
END LOOP;
これをMERGEで書き換えると…
MERGE INTO 商品TBL S …1
USING 新価格TBL N …2
ON ( S.品名 = N.品名) …3
WHEN MATCHED THEN …4
UPDATE SET 価格 = N.新価格
WHEN NOT MATCHED THEN …5
INSERT (品名,価格)
VALUES (N.品名,N.新価格);
となります。 なんとなく似ていますね、順番に説明します。
1.MERGE INTO [対象テーブル]…で、処理を適用するテーブルを指示します。
2.USING [比較テーブル(副問い合わせも可)]…で、対象テーブルと比較するデータ群を用意します。
3.ON (比較内容) で、真か偽を返す条件を書きます。
4.と5.はPL/SQLケースと同じDMLがWHEN句に続いて2パターン書かれています。
※DMLでさえあればいいので、INSERTでもDELETEでもUPDATEでもいけるはずです。 ここでは3の条件(S.品名 = N.品名)と品名が一致するWHEN句が呼ばれますのでMATCHEDで、UPDATEを、一致しなければNOT MATCHEDでINSERTされるわけです。
もちろん、どちらも結果は
│品名* │価格│
│りんご│110 │←新価格
│めろん│980 │←新商品
│みかん│80 │
│ばなな│90 │
になりますが、MERGEは単一DMLですので、処理としては最速になるはずです。 PL/SQLはOracle Databaseの中では万能ですが、いろんな手段(武器)を持っておくことは良いことだと思います。 頭の片隅にでも、MERGEってあったなぁと憶えておくといつか役に立つかもしれませんね。
プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応) (Oracle現場主義)
- 作者: アシスト教育部
- 出版社/メーカー: SBクリエイティブ
- 発売日: 2014/05/26
- メディア: 大型本
- この商品を含むブログ (2件) を見る