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

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

今回はORACLE MASTER Silver SQLの試験トピック「DML文を使用した表の管理 – 複数の表の挿入 -」に関連する問題をご紹介いたします。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文に関する問題を解いていきましょう。DML文はUPDATE、INSERT、DELETE文などが該当しますが今回はINSERT文に関する問題になります。

それでは内容を確認していきます。

(解説)

INSERT文のINSERT ALLという構文に関する問題でした。さて早速このINSERT ALLを文章で説明すると、

「副問合せから戻された各行に対して指定された条件が満たされている場合に各ターゲットとなる表にデータを挿入することができる」

と言った感じでしょうか。文章だけだと何とも分かりにくいですね(汗)。

それでは「既存表のデータを分析する」というイメージを使ってINSERT ALLを学習していきましょう。

以下のような要望があったと仮定します。

  • 商品を購入するお客様データを管理するために顧客表(CUSTOMERS表)がある
  • CUSTOMERS表には「独身の方」と「結婚している方」を判断するためにCUST_MARITAL_STATUS列が定義されている
  • データ分析をするために、「独身の方(single)」と「結婚している方(married)」の条件により、別々の専用の表にデータを格納したい

これらの条件を通常のINSERT INTO … SELECT 構文(INSERT ALLを使用しない)を使用して満たす場合は以下のようになると思います。

副問合せのSELECT文のWHERE句で条件を指定していますね。たしかにこの方法でも要件自体は満たしますがINSERT文を2回実行しなければいけません。このような場合にINSERT ALL文を使用すると1度のINSERT文で、指定した複数の表に対してデータを挿入することが可能になります。

以下をご確認ください。

INSERT ALL構文では WHENで分岐条件を記載し、THEN INTOでデータを挿入する表を指定することができます。このような操作によりINSERT文を1回実行するだけで複数の表にデータを挿入することができます。副問合せに記載があるCUSTOMERS表へのアクセスも1度で済むためパフォーマンス的にもメリットがあります。

さあ、INSERT ALLの構文が分かったところで改めて選択肢を見ていきましょう。

選択肢１は、まさにINSERT ALLのことを説明しているので正解です。

選択肢２は「外部表」という用語が出てきてますね。外部表というのは、データベース内に表の定義だけを構築し、実際のデータはデータベース外(OS上)にある少し特殊な表になります。専用のドライバを使用して、あたかもデータベース内にデータがあるようにSQL文でアクセスできますが、外部表は読取り専用の表になります。INSERT ALL文を使用してもデータ挿入はできませんので選択肢２は誤りになります。
※外部表の詳細は「第27回：外部表の作成と使用」を参考にしてください。

選択肢３は正しい内容ですね。WHEN …. THEN INTO を使用することでデータ分岐、指定した表へのデータ挿入が可能です。

選択肢４は少し難しかったかと思います。一見正解のように見えますが実は誤りで、1つのWHEN条件に複数のINTO句を使用することができます。参考となる構文を以下に載せておきましょう。

CUST_MARITAL_STATUSがSINGLEだったらCUST_SINGLE表とCUST_OTHER表に挿入するというような記述が出来るわけですね。したがって選択肢４は誤りになります。

選択肢５は「・・・副問合せでアクセスされた行数と必ず一致する」とありますが条件分岐でデータを割振る動作になりますから「必ず」という部分が間違っていますね。

正解は１と３になります。

今回の問題に関するSQL文が実行できるように定義のサンプルを乗せておきます。

こちらのサンプルではWHEN … THEN INTO に加えてELSE句も追加してあります。条件に一致しなかったデータはELSE句で指定したテーブルに格納されるという動作になります。こちらも必ず覚えておきましょう。

— customers table 作成 —
drop table customers;

create table customers (
                cust_id number not null,
                cust_name varchar2(50) not null,
                cust_gender char(2) ,
                cust_marital_status varchar2(10),
                cust_city varchar2(30),
                cust_p_number varchar2(25));


— sample データ —
insert into customers values (104133, ‘Ian Joshua’, ‘M’, ‘married’, ‘Palmdale’, ‘484-508-5562’);
insert into customers values (100632, ‘Joe Spivak’, ‘M’, ‘single’, ‘North Hills’, ‘248-303-6668’);
insert into customers values (103594, ‘Samantha Anderson’, ‘F’, ‘single’, ‘Los Angeles’, ‘219-205-9170’);
insert into customers values (103392, ‘Makiko Ikeda’, ‘F’, ‘married’, ‘Tokyo’, ’03-4455-8888′);
insert into customers values (118333, ‘Jasmine Kilroy’, ‘F’, ‘ ‘, ‘Montreal’, ‘164-164-4789’);
commit;

 

— cust_single, cust_married, cust_other table 作成 —
drop table cust_single;
drop table cust_married;
drop table cust_other;

create table cust_single (
                cust_id number not null,
                cust_gender char(2),
                cust_marital_status varchar2(10),
                cust_p_phone varchar2(25));

create table cust_married (
                cust_id number not null,
                cust_gender char(2),
                cust_marital_status varchar2(10),
                cust_p_phone varchar2(25));

create table cust_other (
                cust_id number not null,
                cust_gender char(2),
                cust_marital_status varchar2(10),
                cust_p_phone varchar2(25));


— insert all 文 —
insert all
when cust_marital_status=’single’ then into cust_single
when cust_marital_status=’married’ then into cust_married
else into cust_other
select cust_id, cust_gender, cust_marital_status, cust_p_number from customers;

commit;

今回はDML文のINSERT ALLについて学習を行いました。とても便利な構文ですし、パフォーマンス的にもメリットがありますのでしっかりと身につけて様々なところでご活用ください。

今回の講義はこれまでとします。それではまた。

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

– ORACLE MASTER Silver SQL 2019 のご紹介

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

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

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

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

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