IF文のネスト地獄から抜け出せるMERGE文：SQLクリニック（11）（2/2 ページ）

» 2005年12月27日 00時00分公開

[林優，株式会社インサイトテクノロジー]

MERGE文を使ったサンプルの実行

　では、いよいよ変更文をEMP表に反映させます。

[merge.sql]
merge into scott.emp e
    using
        scott.emp_update u
    on ( e.empno = u.empno )
    when matched then
        update set
            e.ename    = u.ename,
            e.job      = u.job,
            e.mgr      = u.mgr,
            e.hiredate = u.hiredate,
            e.sal      = u.sal,
            e.comm     = u.comm,
            e.deptno   = u.deptno
    when not matched then
        insert (
            empno,
            ename,
            job,
            mgr,
            hiredate,
            sal,
            comm,
            deptno
        ) values (
            u.empno,
            u.ename,
            u.job,
            u.mgr,
            u.hiredate,
            u.sal,
            u.comm,
            u.deptno
        );
SQL> @merge.sql
2 rows merged.

リスト5　EMP_UPDATE表の変更をEMP表に反映

　では、反映されたEMP表を参照してみましょう

SQL> select * from emp where empno = 7654 or empno = 9999;
     EMPNO ENAME      JOB          MGR HIREDATE      SAL   COMM DEPTNO
---------- ---------- --------- ------ ---------- ------ ------ ------
      7654 MARTIN     ANALYST     7566 28-SEP-81    3000            20
      9999 HAYASHI    SALESMAN    7698 18-DEC-05      10            30

リスト6　EMP表の変更を確認

　ということで、EMP_UPDATE表の変更をEMP表に反映させることができました。これを使えば、SELECT文を実行してデータがあればUPDATE、なければINSERTというロジックをわざわざ書かなくてもいいですし、2度のSQL文が1回で済むのでメンテナンスも楽チンですねぇ。

　「いやいや、ちょっと待て！ EMPNOのUPDATEは？？？」と思った方は鋭いですね。では、上記と同じ文でUPDATE句の部分に「e.empno= u.empno」を追加して実行してみます。

SQL> @merge
    on ( e.empno = u.empno )
         *
行4でエラーが発生しました。:
ORA-38104: ON句で参照する列は更新できません: "E"."EMPNO"

リスト7　「e.empno = u.empno」を追加した際のエラー

　実は、ON句で指定している項目はUPDATEすることができないのです。MERGE文でUPDATEできるのは、ON句で参照していない列に限られるという制限があるためです。結合キーが変更されてしまうのは、困る！

というわけです。上記の点には注意してくださいね。

データをそのまま使う

　MERGE文では表のデータを使わなきゃダメ？と思う方も多いと思います。SQL*Plusから直接データを打ち込んでMERGE文を使うのに、ワザワザ表を作るのはちょっとコストが高いかなぁ……と思うこともあります。

　そんなときは、SubQueryを使用します。USING句の指定する行ソースを、SubQueryで抽出した表にして、抽出した結果と該当の表と結合させます。

[merge2.sql]
merge into scott.emp e
    using (select * from scott.emp where empno = 9999) u
    on ( e.empno = u.empno )
    when matched then
        update set
            job = 'Cons',
            mgr = '9999',
            sal = 20
    when not matched then
        insert (
            empno,
            ename,
            job,
            hiredate,
            sal,
            comm,
            deptno
        ) values (
            9999,
            'HAYASHI',
            'Cons',
            '9999',
            20,
            null,
            30
        );
SQL> @merge2
1行がマージされました。

リスト8　SubQueryを使用したMERGE文

　では、確認してみましょう

SQL> select * from emp where empno = 9999;
     EMPNO ENAME      JOB          MGR HIREDATE    SAL   COMM DEPTNO
---------- ---------- --------- ------ -------- ------ ------ ------
      9999 HAYASHI    Cons        9999 05-12-18     20            30

リスト9　EMP表の変更を確認

　ちゃんと、Cons部に異動して、給料も20円（ドル？）になりました。

　MERGE文にはCASE文も含めることができるので、プロシージャの中でIF文を多用する場合よりも、すっきりとしたコーディングにできると思いますよ。ただし、このMERGE文……便利なんですが、いくつかBugが報告されているようです。Patchを適用したり、オラクル社のサポート情報もチェックして十分なテストとともに実装してみてください。（次回に続く）