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

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

今回はORACLE MASTER Silver SQLの試験トピック「ビューの管理 – ビューの管理 -」に関連する問題をご紹介いたします。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

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

それではビューに関する問題を見ていきましょう。

(解説)

それでは解説をしていきますが、そもそもビューとは？から確認をしていきましょう。ビューの特徴をまとめると以下の２点になります。

  • 問合せの定義のみが実装され、実データは含まれない
  • データアクセス制限や複雑な問合せの簡素化ができる

簡単な例も掲載しておきます。ビューのイメージが出来ると思います。

ビューの実態はSELECT文になり実データはもっていません。複雑なSQL文を事前にビューに定義しておけば、簡素化したインタフェースとして使用することができるためSQLをあまり詳しくない方であっても簡単に使うことができます。

今回の問題は応用編としてビューを介して実表へどのような操作が可能であるか？を問う選択肢やビューのオプションに関する内容がありますね。それでは選択肢を、実機操作した結果を踏まえつつ、確認していきましょう。以下の定義を使用するので皆さんも実際に構築して操作をしてみてください。

— sample テーブル作成 —
drop table emp2;
create table emp2 (
    empno number(6)        not null,
    ename varchar2(20), 
    sal number(10), 
    deptno number(3)
);

— sample データ挿入 (7件) —
insert into emp2 values(101,’HOBO’, 6000, 10);
insert into emp2 values(102,’NAKAMURA’, 3000, 20);
insert into emp2 values(103,’SAKURAI’, 2000, 20);
insert into emp2 values(104,’UEKI’, 3500, 30);
insert into emp2 values(105,’SUGITA’, 1000, 30);
insert into emp2 values(107, ‘SUGITA’, 4000, 30);
insert into emp2 values(106,’TADANO’, 4000, 10);
commit;

選択肢１と２を確認するために実際にGROUP BY句を含むビューを作成していきます。

EMP2G_Vビューの内容はDEPTNO毎の平均給与(AVG_SAL)を求めているSELECT文ですね。ではこのビューを介してDELETE文を実行してみます。

いずれのDELETE処理もエラーとなり怒られました。何故エラーになったか？ですが、そもそも今回作成したビューはGROUP BYを使用して実表のデータを加工して表示する内容になります。表示されている内容は実表に該当するデータはありませんので削除処理ができてしまったら逆におかしいですよね。DISTINCTについても同様で、重複行を省くという実表のデータを加工して表示するのでビューを介してのDELETE操作は同様のエラーとなります。実際に実機を使って動作確認をすることで何故エラーになるのかの理由がよく分かると思います(丸暗記だけだとこうはいきませんよね)。この内容はDELETE処理だけではなくUPDATEやINSERT処理をする際にも同様のことが言えます。参考までにUPDATE処理した内容も載せておきましょう。

ビューにDISTINCT演算子やGROUP BY句、またグループ関数などを使用している場合はDML操作に影響がでますので実際に操作しながら覚えていきましょう。以上の内容から選択肢１は正しく、選択肢2は誤りです。

補足しておきますが「ビューを介したDMLの処理はすべて出来ない」というわけではありませんので誤って覚えないように。以下のビューで確認しておきます。

はい、問題なく操作ができましたね。正しい内容をしっかり押さえておきましょう。

次に選択肢３を見ていきます。WITH CHECK OPTIONはビューに対して実行したUPDATEやINSERT操作をビュー領域内にとどめることができるオプションになります。以下に内容をまとめておきます。

ビューで指定している表示可能なデータ(上記例はDEPTNO=30)のみ操作できるようにするオプションになりますので選択肢３は正しい内容を述べていますね。

選択肢４ですが、ビューの作成には元となる表が存在してなくても強制的に作成するオプションとしてFORCEオプションがあります。実行例を載せておきます。

警告は出ますが作成自体は完了します。もちろん元となる表を作成しないとビューとして正しくアクセスすることはできませんが選択肢の説明は正しい内容になります。

選択肢５は誤りですね。ビューは実データを持ちませんし、表とは独立したオブジェクトになりますのでビューの削除に連動して表内データが削除されることはありません。

選択肢６は、これまでの説明で誤りであることが分かるかと思いますが、INSERTについてはもう少し補足しておきます。INSERT処理については元となる表の列にNOT NULL制約がある場合は気を付けてください。これまで使用してきた定義で確認しておきましょう。

EMP2_Vビューを介してデータを追加しようとしましたがNOT NULL制約によりエラーになりました。NOT NULL制約＆デフォルト値の設定無しの列がビュー定義に含まれていない場合、ビューを介したINSERT処理をすると上記のように制約違反のエラーになるのでこれも覚えておきましょう。

以上を踏まえまして正解は １、3、４ になります。

ビューは使い勝手のよいオブジェクトなので使用する機会が多いかと思いますが、ビュー定義を介したデータ操作やオプション指定など複雑な部分もあるのでしっかりと内容を押さえておきたいですね。今回の講義はこれまでとします。お疲れ様でした！

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

– ORACLE MASTER Silver SQL 2019 のご紹介

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

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

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

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

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