助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26775
問題を開く
EMPLOYEES表の構造を確認して下さい。



次のSQL文でEMPLOYEES表を実表とするV_EMPビューを作成しました。

 CREATE OR REPLACE VIEW v_emp
  AS 
 SELECT department_id, employee_id, employee_name, salary
 FROM employees
 WHERE salary > 250000 AND salary < 800000
  AND department_id = 1
 WITH CHECK OPTION;

V_EMPビューに対して、エラーとならずに実行できるSQL文はどれですか(該当するものを全て選択して下さい)。

正解

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);

UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

解説

ビューの作成時、WITH CHECK OPTIONオプションを指定すると、ビューを通じて実表のデータを操作する場合に、ビューの定義時に指定したWHERE句の条件を満たしていないデータの追加や更新ができなくなります。

V_EMPビューは、
・SALARY列の値が250,000より大きく800,000より小さい
・DEPARTMENT_ID列の値が1
という条件が指定されているので、この条件を満たすデータの追加、更新のみ行うことができます。

以上より、
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);
・UPDATE v_emp SET salary = 500000 WHERE department_id = 3;
が正解となります。

後者のSQL文は、WHERE句でdepartment_id = 3という条件が指定されていますが、更新する値(salary = 500000)自体はビューで定義された条件を満たしているためエラーとはなりませんので注意しましょう。
ただし、V_EMPビューではDEPARTMENT_ID列が1のデータにしかアクセス出来ないため、データの更新は行われません。

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


 

SQLを表示
CREATE OR REPLACE VIEW v_emp
 AS 
SELECT department_id, employee_id, employee_name, salary
FROM employees
WHERE salary > 250000 AND salary < 800000
 AND department_id = 1
WITH CHECK OPTION;

INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);



 
SQLを表示
UPDATE v_emp SET salary = 500000
WHERE department_id = 3;


その他の選択肢については次のとおりです。

・UPDATE v_emp SET salary = 1000000 WHERE department_id = 1;
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 150000);
SALARY列の値がビューの定義を満たしていないため、エラーとなります。

・INSERT INTO v_emp VALUES (1, NULL, 'Tanaka', 40000);
EMPLOYEES表のEMPLOYEE_ID列にはNOT NULL制約が定義されているため、NULL値を登録することはできません。

参考

ビューの作成はCREATE VIEW権限を持つユーザーによって行われます。

 CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW ビュー名 [(別名 [,別名...])]
  AS
 副問合せ
 [WITH CHECK OPTION [CONSTRAINT 制約名]]
 [WITH READ ONLY [CONSTRAINT 制約名]];

 
 

SQLを表示
CREATE VIEW v_emp
 AS
SELECT employee_id, employee_name, hiredate, department_id
FROM employees
WHERE salary <= 450000;

SELECT * FROM v_emp;


CREATE VIEW文のオプションは次の通りです。



[FORCE/NOFORCE]
CREATE VIEW文でビューを作成する際に、FORCEオプションを指定すると、実表の有無にかかわらずビューを作成することができます。
メンテナンスや検証時に、一時的に実表が存在しないがビューを作成しておきたい場合などに利用します。


 
SQLを表示
SELECT * FROM emp3;

CREATE FORCE VIEW view1
 AS 
SELECT employee_id, employee_name, salary
FROM emp3;


ただし、ビューの参照時に実表が存在しない場合はエラーとなります。


 
SQLを表示
SELECT * FROM view1;


NOFORCEオプションを指定すると、実表がある場合のみビューが作成され、実表がない場合はエラーとなります。


 
SQLを表示
SELECT * FROM emp3;

CREATE NOFORCE VIEW view1
AS
SELECT employee_id, employee_name, salary
FROM emp3;


なお、CREATE VIEW文ではNOFORCEがデフォルトとなりますので、FORCE、NOFORCEどちらも指定しない場合は、実表がある場合のみビューを作成することができます。

[WITH CHECK OPTION]
ビューの作成時、WITH CHECK OPTIONオプションを指定すると、ビューを通じて実表のデータを操作する場合に、ビューの定義時に指定したWHERE句の条件を満たしていないデータの追加や更新ができなくなります。


 
SQLを表示
CREATE VIEW v_emp
 AS
SELECT employee_name, salary
FROM employees
WHERE department_id = 5
 AND salary >= 400000
WITH CHECK OPTION;

UPDATE v_emp SET salary = 300000;

UPDATE v_emp SET salary = 600000;


CREATE VIEW文でビューを作成する場合は、次の点に注意が必要です。

・副問合せのSELECT句に計算式や関数を指定する場合は、CREATE VIEW文で別名を指定するか、SELECT句の計算式や関数に列別名を指定する


 
SQLを表示
CREATE VIEW v_emp
 AS
SELECT employee_id, employee_name, salary*12, hiredate
FROM employees;


・ビューの列名を定義する場合は、副問合せのSELECT句に指定する列の数と同数の列名を定義する


 
SQLを表示
CREATE VIEW v_emp (id, name, sal, hireadte)
 AS
SELECT * FROM employees
WHERE salary >= 500000;


なお、ビューの構造は表と同様にDESCコマンドで確認することができます。


 
SQLを表示
DESC v_emp;

上に戻る

WITH CHECK OPTIONについて

公開日 2023/01/21

後者のSQL文は、WHERE句でdepartment_id = 3という条件が指定されていますが、更新する値(salary = 500000)自体はビューで定義された条件を満たしているためエラーとはなりませんので注意しましょう。
という解説が腑に落ちません。
insertはdepartment_id = 1でないとエラーになるようですが、updataは条件以外のレコードでもよいということなのでしょうか。どのように判断すればよいのかわかりません。

2023/01/22 23:22

insertはdepartment_id = 1でないとエラーになるようですが、updataは条件以外のレコードでもよいということなのでしょうか

問題のVIEWのポイントって

 SELECT department_id, employee_id, employee_name, salary
 FROM employees
 WHERE salary > 250000 AND salary < 800000
  AND department_id = 1

ですよね。要件としては

  • 部署ID(department_id)が1
  • 給与(salary)が250000から800000の間

です。

当たり前ですが、INSERTする時には「この条件に当てはまる行の挿入」となるので、どちらも満たさないと絶対エラーになりますよね。ですがUPDATEは「この条件に当てはまるものを指定した内容に更新」するよう指示しているだけです。改めて後者(UPDATEの方ですよね?)をみると

UPDATE v_emp SET salary = 500000 WHERE department_id = 3;

となっており、「部署IDが3の行のsalaryを一律500000にせよ」という指示なので、部署IDが3のものを見つけて更新しようとしますがVIEWの定義により部署ID:3のレコードはヒットしない(VIEWのWHERE句に含まれない)ので「対象レコードが見つからず更新できない=実行した結果0件更新できたよ」となるだけでエラーにはならないというだけのことです。

INSERT時は範囲外の値をセットすると「範囲外の値は入力できない(エラー)」となりますが、UPDATE時は更新指示自体がエラーになるのではなく「処理を実行した結果0件でした」となるのだよ、という違いですね。


コメント

m mmmss125

2023/01/23 22:03

ご丁寧にありがとうございます! 理解することができました。

この返信に対して
コメントを記入できます

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