助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26794

EMPLOYEES表の構造を確認して下さい。

EMPLOYEES表を基に次の3つのビューを作成しました。ただし、INSTEAD OFトリガーは定義していないものとします。
ビューと実行可能な操作の組合せとして正しいものはどれですか(該当するものを2つ選択してください)。

　ビュー1)
　　CREATE OR REPLACE VIEW v_emp1
　　　AS
　　SELECT employee_id, employee_name, salary * 12 sal
　　FROM employees;

　ビュー2)
　　CREATE VIEW v_emp2
　　　AS
　　SELECT department_id, AVG(salary) avgsal
　　FROM employees
　　WHERE salary > 600000
　　GROUP BY department_id;

　ビュー3)
　　CREATE VIEW v_emp3
　　　AS
　　SELECT employee_name, salary
　　FROM employees;

ビュー1を通じて、EMPLOYEES表のデータの削除ができる

ビュー2を通じて、EMPLOYEES表へのデータの追加ができる

ビュー2を通じて、EMPLOYEES表のデータの更新ができる

ビュー3を通じて、EMPLOYEES表のデータの更新ができる

正解

ビュー1を通じて、EMPLOYEES表のデータの削除ができる

ビュー3を通じて、EMPLOYEES表のデータの更新ができる

解説

作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。

ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。

設問のビュー1はビューの定義に式によって定義された列が含まれています。データの削除を行うことができます。
ビュー2はビューの定義にGROUP BY句とグループ関数が使用されているため、データの追加、更新、削除の全てを行うことができません。
ビュー3はNOT NULL制約が定義されているEMPLOYEE_ID列がビューの定義に含まれていません。データの更新を行うことができます。

以上より、
・ビュー1を通じて、EMPLOYEES表のデータの削除ができる
・ビュー3を通じて、EMPLOYEES表のデータの更新ができる
が正解となります。

設問のSQL文の実行結果は次のようになります。

　[ビュー1]

　

SQLを表示

CREATE OR REPLACE VIEW v_emp1
　AS
SELECT employee_id, employee_name, salary * 12 sal
FROM employees;

INSERT INTO v_emp1
　VALUES (2000, 'Tanaka', 500000);

UPDATE v_emp1 SET sal = 500000;

DELETE v_emp1 WHERE sal > 5000000;

　[ビュー2]

SQLを表示

CREATE VIEW v_emp2
　AS
SELECT department_id, AVG(salary) avgsal
FROM employees
WHERE salary > 600000
GROUP BY department_id;

INSERT INTO v_emp2
　VALUES (6, 500000);

UPDATE v_emp2 SET department_id = 6;

DELETE v_emp2 WHERE department_id = 5;

　[ビュー3]

SQLを表示

CREATE VIEW v_emp3
　AS
SELECT employee_name, salary
FROM employees;

INSERT INTO v_emp3
　VALUES ('Tanaka', 400000);

UPDATE v_emp3 SET salary = 250000;

DELETE v_emp3 WHERE salary = 250000;

参考

作成したビューを通じて実表のデータを操作することができます。
ただし、表に直接アクセスする場合とは異なり、ビューを通じての実表のデータの操作には、ビューの定義によっていろいろな制限があります。

ビューを通じて実表のデータを操作することができる基本的なルールは次の通りです。

[ビューを通じてのデータの追加]
ビューを通じて実表のデータを追加する場合には、ビューの定義に次の要素が含まれていないことが条件になります。

・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※

　

SQLを表示

CREATE VIEW v_prod
　AS
SELECT prodid, category, name
FROM prod
WHERE category < 50;

INSERT INTO v_prod
　VALUES (50, 60, 'MP3 Player');

SELECT prodid, category, name
FROM prod;

<img src="/mondai3/img/jpg/kk49148.jpg">

SQLを表示

DESC prod;

CREATE VIEW v_prod
　AS
SELECT category, name
FROM prod
WHERE category < 50;

INSERT INTO v_prod
　VALUES (70, 'HeadPhone');

ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。

<img src="/mondai3/img/jpg/kk49239.jpg">

SQLを表示

CREATE TABLE table2
(id NUMBER(2),
　name VARCHAR2(10),
　birth DATE DEFAULT SYSDATE NOT NULL
);

CREATE VIEW view2
　AS
SELECT id, name FROM table2;

INSERT INTO view2
　VALUES (1, 'Tanaka');

[ビューを通じてのデータの更新]
ビューを通じて実表のデータを更新する場合には、ビューの定義に次の要素が含まれていないことが条件になります。

・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード
・式によって定義された列 ※

SQLを表示

CREATE VIEW v_prod
　AS
SELECT category, name
FROM prod;

SELECT * FROM v_prod;

UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;

SELECT prodid, category, name
FROM prod;

<img src="/mondai3/img/jpg/kk49147.jpg">

SQLを表示

CREATE VIEW v_prod
　AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;

UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;

ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。

SQLを表示

DESC employees;

CREATE VIEW v_emp
　AS
SELECT employee_id, employee_name, salary * 12 salary
FROM employees;

INSERT INTO v_emp
　VALUES (1000, 'Tanaka', 8000000);

INSERT INTO v_emp (employee_id, employee_name)
　VALUES (1000, 'Tanaka');

UPDATE v_emp SET salary = 0;

UPDATE v_emp SET employee_name = 'Yamada'
WHERE employee_id = 1000;

[ビューを通じてのデータの削除]
ビューを通じて実表のデータを削除する場合には、ビューの定義に次の要素が含まれていないことが条件になります。

・GROUP BY句
・グループ関数
・ROWNUM擬似列
・DISTINCTキーワード

SQLを表示

CREATE VIEW v_prod
　AS
SELECT category, name
FROM prod;

SELECT * FROM v_prod;

DELETE FROM v_prod
WHERE category = 50;

SQLを表示

CREATE VIEW v_prod
　AS
SELECT DISTINCT category, name
FROM prod;

SELECT * FROM v_prod;

DELETE FROM v_prod
WHERE category = 40;

なお、実表に制約が定義されている場合は、ビューを通したデータの操作時にも制約のチェックが行われます。制約を満たさないデータの操作はできません。

【INSTEAD OFトリガー】
INSTEAD OFトリガーは、上記のようにDML文（INSERT、UPDATE、DELETE）を実行できないビューに対してDMLを実行できるようにするトリガーです。トリガーとは指定した条件の操作が行われた際に起動するプログラムです。Oracle独自のプログラミング言語であるPL/SQLで記述します。

ここでは、前述の[ビューを通じてのデータの削除]で作成したDISTINCTキーワードを定義に含むV_PRODビューに対してDELETE文が発行された時に起動するINSTEAD OFトリガーの例を記載します。
本来はビューの定義にDISTINCTキーワードが含まれている場合はビューを通じて実表のデータを削除することはできません。しかし、V_PRODビューにDELETE文が発行されると代わりに（INSTEAD OF）起動するINSTEAD OFトリガーを作成すると、実表に対してDELETE文が実行できるようになります。

SQLを表示

CREATE VIEW v_prod
　AS
SELECT DISTINCT category, name
FROM prod;

CREATE OR REPLACE TRIGGER delete_v_prod
INSTEAD OF DELETE ON v_prod
BEGIN
DELETE prod WHERE category = :OLD.category;
END;
/

DELETE FROM v_prod
WHERE category = 40;

SELECT * FROM prod;

参考URL

Oracle® Database SQL言語リファレンス「CREATE VIEW」: 更新可能なビューの注意事項

上に戻る

ビュー1は追加可能ではないでしょうか

s shuichi0705

公開日 2023/07/09

ビュー1を通じて、EMPLOYEES表へのデータの追加ができる
は、計算式を含む列以外の指定であればデータの追加は可能なのではないでしょうか。

スタッフからの返信

s staff_ishii

2023/07/11 17:50

shuichi0705 さんご指摘の点を修正いたしました。ご報告、誠にありがとうございました。

この投稿に対して返信しませんか？

助け合いフォーラム