Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。

————————————————-

今回はORACLE MASTER Silver SQLの試験トピック「DML文を使用した表の管理 – Merge文の実行 -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。

Oracle Database SQL Exam Number: 1Z0-071

URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071

————————————————-

本日はDML操作であるMerge文に関する問題を確認していきます。Merge文は少し構文が複雑なこともあり苦手としている人もいるのではないでしょうか。今回の問題を通してしっかり学習しておきましょう。

(解説)

それでは問題の解説をしたいと思いますが、そもそもMERGE文って何？という方もいらっしゃると思いますので先に文章で簡単に説明します。

  • 表のデータに対して条件により、更新、挿入または削除処理ができる機能
  • 条件が一致してデータが存在する場合は更新処理(更新データの削除も可)を実行し、それ以外は新規データを挿入する

文章の説明のみだと分かりにくいですが、まずは「MERGE文は条件分岐で複数の処理が出来るんだな」と理解してください。では次に、必ず押さえておく基本的な構文を確認してみます。

いやぁ、、難しいですね。この内容だけで理解しろというのは酷な話なので、問題の選択肢を解説する前に、MERGE文を実行する必要があるシナリオを想定しながらさらに詳しく説明をしていきましょう。

(シナリオの概要)

従業員情報を管理する「従業員表」があります。従業員に対して臨時ボーナスを支給することが決定し、支給額を管理する「ボーナス表」を構築する必要が出てきました。この「ボーナス表」の更新処理をMERGE文を使って実施します。

※実際に動作が出来るように本記事の最後にテーブル定義やデータを載せておきます。

(シナリオの詳細説明)

まずは普通に以下のような従業員表(emp_table)があります。

従業員に対して、通常の給与(SALARY)以外に臨時ボーナスを支給することになりました。臨時ボーナスをどれだけ支給するか管理する「ボーナス表」であるEMP_BONUS表があります。今回は特別に部門(DEPT_ID) 30と50の従業員は優秀な成績を上げたので通常の臨時支給額にプラスして$1,000が上乗せされるとします。

EMP_BONUS表は、現状は以下のように部門30と50に該当する従業員に対して上乗せされる金額だけがBONUS列に挿入されている状態です。

はい、ここからが本番です。この2つ表データを用いて、以下の条件を満たした臨時ボーナスを管理するEMP_BONUS表データを更新するMERGE文を考えてみます。

・ 部門(DEPT_ID) 30と50の従業員については特別に$1,000を追加支給　(すでにデータ格納済み)

・ 臨時ボーナスの額は給与(SALARY)の10％とする

・ ただし、給与額が$15,000 以上の従業員は臨時ボーナスは支給しない

MERGE文を少しずつ作成していこうと思います。

まず更新または挿入対象となる表は「EMP_BONUS」表になりますので・・

から始まります。この後のSQL文が分かりやすくなるようにEMP_BONUS表には表別名「D」を指定しておきます。このEMP_BONUS表に対する更新or 挿入の元となる表データはEMP_TABLE表になるので、この表を以下のようにUSING句で指定します。表別名を「E」としておきます。

次に指定するON句では、更新操作なのか挿入操作なのかを分岐するための条件を指定します。

今回はEMP_TABLE表のEMP_ID列とEMP_BONUS表のEMP_ID列を結合条件として指定します。

この条件でデータが一致(WHEN MATCHED)するということは、EMP_BONUS列にデータがある、つまり「優秀な成績を上げた部署の従業員」ということになります。この従業員はベースの$1,000 に加えSALARYの10％を臨時ボーナスとして支給するわけですから、EMP_BONUS表の既存データをUPDATEする必要があります。

WHEN MATCHED THEN を使用して以下のように記載できます。

残りの従業員は条件でデータが一致しなかった方(WHEN NOT MATCHED)が該当する、つまりEMP_BONUS列にはデータが存在しないので新規でデータを挿入する必要があります。

以下のように記載できます。

最後の条件として「給与額が$15,000 以上の従業員は臨時ボーナスは支給しない」とありましたのでINSERT文のところにWHERE句で条件をつければMERGE文の完成です。

いかがでしょうか？ MERGE文の構文が見えてきたのではないでしょうか。

今、説明した内容について実際に実行した結果も記載しておきましょう。

MERGE文の説明が長くなりましたが問題の選択肢を見ていきましょう。

選択肢1はもう大丈夫ですよね。WHEN MATCHEとWHEN NOT MATCHED を同時に指定しても、もちろん問題ありませんから誤りになります。

選択肢2は少し追加説明が必要かと思います。MERGE文にはWHEN MATCHED句のところにDELETE句を追加することも可能です。先に説明したMERGE文に追加するのであれば以下のような感じです。

ただしDELETE句は必須ではありませんのでこの選択肢の内容も誤りです。

選択肢3は「WHEN NOT MATCHEDはUPDATE句を記述する必要・・」とありますがWHEN NOT MATCHEDはINSERT句を記述するので誤りです。

選択肢4はON句に関する説明をしていますが、更新、挿入するターゲット表の指定はMERGE INTOの後に指定しますよね。これも誤りです。

消去法で残った選択肢5が正しい内容になりますが、問題文のMERGE文が実行できるサンプルも後で記載しておきますので実際に実行してみてください。

補足説明として、今回の問題文のUSING句には「USING (select * from products) p」という記載となっていますが指定方法に問題はありません。SELECT文の結果を表データと見立てて、表別名「P」としています。USING句には表だけではなくビューや副問合せも指定できることを合わせて押さえておきましょう。

以上の内容より、正解は5になります。

MERGE文の説明の際に使用したテーブル定義、サンプルデータ、実行したMERGE文を記載しておきます。

実際に動作確認を行って理解を深めていただければ幸いです。

— emp_table表作成 とsample dataの挿入–
drop table emp_table;
create table emp_table(
        emp_id          number primary key,
        emp_name        varchar2(50),
        salary          number not null,
        dept_id         number not null);

insert into emp_table values(100, ‘Steven King’, 24000, 10);
insert into emp_table values(101, ‘Neena Kochhar’, 17000, 20);
insert into emp_table values(102, ‘Alexander Hunold’, 9000, 30);
insert into emp_table values(103, ‘Diana Lorentz’, 8000, 30);
insert into emp_table values(104, ‘Shelli Baida’, 5500, 40);
insert into emp_table values(105, ‘John Chen’, 3400, 50);
insert into emp_table values(106, ‘Daniel Faviet’, 3000, 50);
insert into emp_table values(108, ‘David Austin’, 6000, 60);
insert into emp_table values(109, ‘Kelly Chung’, 6500, 60);
insert into emp_table values(110, ‘Donald Grant’, 2100, 100);

commit;

 

— emp_bonus表作成 とsample dataの挿入–
drop table emp_bonus;

create table emp_bonus(
        emp_id  number,
        bonus   number default 1000);

insert into emp_bonus(emp_id)
        select emp_id from emp_table
        where dept_id = 30 or dept_id = 50;

commit;

 

— merge文の実行 (delete句なし)–
merge into emp_bonus D
  using emp_table E
  on (D.emp_id = E.emp_id)
when matched then
  update set D.bonus = D.bonus + E.salary * 0.1
when not matched then
  insert (D.emp_id, D.bonus) values (E.emp_id, E.salary * 0.1) where E.salary < 15000;

commit;


— merge文の実行(delete句あり) —
merge into emp_bonus D
  using emp_table E
  on (D.emp_id = E.emp_id)
when matched then
  update set D.bonus = D.bonus + E.salary * 0.1
  delete where emp_id=106
when not matched then
  insert (D.emp_id, D.bonus) values (E.emp_id, E.salary * 0.1) where E.salary < 15000;

commit;

さらに、問題文に記載があったMERGE文を実行したい場合は以下のサンプルを使用するとよいでしょう。

— table 定義 —
create table products 
(product_id number(5) not null, prod_cost number(10,2), expired_date date);
create table next_prices
(product_id number(5) not null, price number(10,2));

— sample date —
insert into products values(1,1000,sysdate);
insert into next_prices values(10,500);
commit;


— merge文 —
merge into next_prices n 
  using(select * from products) p
  on (n.product_id=p.product_id)
when matched then
  update set n.price=p.prod_cost
when not matched then
  insert (n.product_id, n.price) values(p.product_id,prod_cost*1.5);

実行結果

今回はMERGE文について学習を行いました。

構文が複雑だなぁと感じた方もいたと思いますが、仮に本番のORACLE MASTER試験の選択肢にMERGE
文がズラッと並んだとしても絶対に適当に選択肢を選ばないように！落ち着いて構文を読み解けば必ず正解の選択肢を選ぶことが出来るはずです。合格目指して頑張っていきましょう！

それでは今回は以上にしたいと思います。お疲れ様でした。

————————————————-

– ORACLE MASTER Silver SQL 2019 のご紹介

– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-