助け合いフォーラム
正解
ビューの定義に式を含む列が定義されていても、対象列以外は更新・追加することができる
解説
ビューの定義に式を含む列が定義されている場合、対象列を含むデータの追加、対象列のデータの更新を行うことはできません。
ですが、対象列以外はデータの追加、更新を行うことができます。
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キーワード
・式によって定義された列 ※
・ビューに含まれない実表の列に定義されたNOT NULL制約 ※
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;
CREATE VIEW v_prod
AS
SELECT category, name
FROM prod
WHERE category < 50;
INSERT INTO v_prod
VALUES (70, 'HeadPhone');
ただし、NOT NULL制約が定義されている列に、デフォルト値が設定されていれば、ビューを通じてデータを追加することができます。
(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キーワード
・式によって定義された列 ※
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;
AS
SELECT category, MAX(name) name
FROM prod
GROUP BY category;
UPDATE v_prod SET name = 'Chopin'
WHERE category = 10;
ただし、ビューの定義に式を含む列が定義されている場合でも、対象列以外はデータの追加、更新を行うことができます。
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キーワード
AS
SELECT category, name
FROM prod;
SELECT * FROM v_prod;
DELETE FROM v_prod
WHERE category = 50;
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文が実行できるようになります。
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;
ビューのDMLについて
CERATE TABLE TEST1 (
COL1 NUMBER(1)
);
CREATE ORE REPLACE VIEW TEST1_VIEW AS
SELECT DISTINCT COL1 FROM TEST1 GROUP BY COL1;
INSERT TEST1_VIEW VALUES('1')
で確認したのが正常終了しました。
GROUP BYとDISTINCTが含まれていれば、行の削除、追加、更新ができない認識なのですが違うのでしょうか？
確かに実機で試してみると、記載されている create view 文で作成したビューに insert ができますね。
少し調べてみたのですが、AIさんによると、以下のような回答があり、内部的な最適化により実行できてしまうこともあるようです。
これは Oracle の内部的なビュー最適化メカニズムによるものです。
SELECT DISTINCT col1 FROM test1 GROUP BY col1;
この場合、Oracleのクエリオプティマイザが 「DISTINCT と GROUP BY が冗長」 であることを認識し、内部的にクエリを最適化します。
ただ、公式ドキュメント上は、本設問の解説文のように、DISTINCTやGROUP BY等が含むSELECTが使用されている場合には、行の挿入、更新、削除はできないと記載されていました。
黒本でも、やはり「ビュー定義のSELECT文に以下が含まれる場合、ビューへDMLを実行することはできない」と記載されており、その中に、DISTINCTやGROUP BYが列挙されていました。
実際に実行できる挙動が見られたので気になってしまうとは思いますが、実際のSilver SQL試験向けの知識としては、列挙されているような要素を含む場合には「DMLが実行できない」と覚えて差し支えはないかなと思います。
コメント
この投稿に対して返信しませんか？
j junext25
2025/07/31 09:48
行の追加○ 行の削除× 行の更新× でした。 解説の表には全部×だったのですが間違いでしょうか