【Oracle】ビュー（VIEW）完全ガイド｜CREATE VIEW・更新可能ビュー・WITH CHECK OPTION・INSTEAD OF トリガーまで解説

-- 基本構文: CREATE [OR REPLACE] [FORCE] VIEW ビュー名 [(列名リスト)] AS SELECT文
-- [WITH CHECK OPTION [CONSTRAINT 制約名]]
-- [WITH READ ONLY [CONSTRAINT 制約名]]

-- 簡単なビュー: 特定の部門の社員のみ表示する
CREATE OR REPLACE VIEW hr_dept60_v AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM   employees
WHERE  department_id = 60;   -- IT 部門（department_id = 60）のみ

-- ビューを使用する（テーブルと同様に扱える）
SELECT * FROM hr_dept60_v WHERE salary > 8000;

-- 列に別名を付ける（2種類の方法）
CREATE OR REPLACE VIEW emp_summary_v (emp_id, full_name, dept_id, annual_salary) AS
SELECT employee_id, first_name || ' ' || last_name, department_id, salary * 12
FROM   employees;

-- OR REPLACE: 既存のビューを置き換える（DROP → CREATE を1コマンドで行う）
-- OR REPLACE なしで既存ビューを CREATE すると ORA-00955（名前がすでに使用されています）

-- FORCE: 基底テーブルが存在しなくてもビューを作成する
-- テーブル作成前にビューの定義だけ先に作っておく場合に使う
CREATE OR REPLACE FORCE VIEW future_table_v AS
SELECT id, name FROM future_table;  -- future_table はまだ存在しない
-- → ビューは作成されるが、SELECT 時に "ORA-04063: view has errors" になる場合がある

-- ビューの削除
DROP VIEW hr_dept60_v;

-- 更新可能ビュー: 単純な WHERE フィルタのみ
CREATE OR REPLACE VIEW updatable_emp_v AS
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 60;

-- ビュー経由で UPDATE できる
UPDATE updatable_emp_v SET salary = salary * 1.1 WHERE employee_id = 107;
COMMIT;

-- ビュー経由で INSERT できる（department_id = 60 以外も INSERT できてしまう → WITH CHECK OPTION で防ぐ）
INSERT INTO updatable_emp_v (employee_id, last_name, salary, department_id)
VALUES (9999, 'Test', 5000, 50);   -- 部門 50 でも INSERT できる（ビューの WHERE と矛盾）

-- 更新不可ビュー: 集計関数や GROUP BY を含む
CREATE OR REPLACE VIEW dept_salary_v AS
SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM   employees
GROUP  BY department_id;

UPDATE dept_salary_v SET avg_salary = 10000 WHERE department_id = 60;
-- ORA-01732: データ操作は、このビューでは許可されません

-- USER_UPDATABLE_COLUMNS で列単位の更新可否を確認する
SELECT column_name, updatable, insertable, deletable
FROM   USER_UPDATABLE_COLUMNS
WHERE  table_name = 'UPDATABLE_EMP_V';
-- UPDATABLE='YES', INSERTABLE='YES', DELETABLE='YES' の列のみ更新可能

-- WITH CHECK OPTION: INSERT/UPDATE した結果がビューの WHERE 条件を満たさなくなる場合にエラーにする
CREATE OR REPLACE VIEW dept60_employees_v AS
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 60
WITH CHECK OPTION;  -- CHECK OPTION なし→ department_id = 50 の行も INSERT できる

-- ビュー内の行（department_id = 60）を更新 → 成功
UPDATE dept60_employees_v SET salary = 7000 WHERE employee_id = 107;

-- department_id = 50 に変更しようとする → ビューから外れるため失敗
UPDATE dept60_employees_v SET department_id = 50 WHERE employee_id = 107;
-- ORA-01402: ビューのWITH CHECK OPTION条件の違反

-- department_id = 70 で INSERT しようとする → WITH CHECK OPTION が拒否する
INSERT INTO dept60_employees_v (employee_id, last_name, salary, department_id)
VALUES (9999, 'Test', 5000, 70);
-- ORA-01402: ビューのWITH CHECK OPTION条件の違反

-- WITH READ ONLY: INSERT/UPDATE/DELETE を完全に禁止する
CREATE OR REPLACE VIEW readonly_emp_v AS
SELECT employee_id, last_name, salary
FROM   employees
WITH READ ONLY;

UPDATE readonly_emp_v SET salary = 9000 WHERE employee_id = 100;
-- ORA-42399: 読み取り専用ビューに対してDML操作を実行できません

-- 複合ビュー（JOIN を含むため直接 INSERT/UPDATE はできない）
CREATE OR REPLACE VIEW emp_dept_v AS
SELECT e.employee_id, e.last_name, e.salary,
       d.department_id, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- INSTEAD OF INSERT トリガー: ビューへの INSERT を employees テーブルへの INSERT に変換する
CREATE OR REPLACE TRIGGER emp_dept_v_insert
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
    -- employees テーブルに INSERT する（departments への INSERT はしない）
    INSERT INTO employees (employee_id, last_name, salary, department_id)
    VALUES (:NEW.employee_id, :NEW.last_name, :NEW.salary, :NEW.department_id);
END;
/

-- INSTEAD OF UPDATE トリガー
CREATE OR REPLACE TRIGGER emp_dept_v_update
INSTEAD OF UPDATE ON emp_dept_v
FOR EACH ROW
BEGIN
    UPDATE employees
    SET    last_name    = :NEW.last_name,
           salary       = :NEW.salary,
           department_id = :NEW.department_id
    WHERE  employee_id  = :OLD.employee_id;
END;
/

-- これでビューへの INSERT が可能になる
INSERT INTO emp_dept_v (employee_id, last_name, salary, department_id)
VALUES (9999, 'NewEmployee', 5000, 60);
COMMIT;

-- ビューの一覧と定義テキストを確認する
SELECT view_name, text_length, read_only
FROM   USER_VIEWS
ORDER  BY view_name;

-- ビューの定義 SQL を取得する
SELECT text FROM USER_VIEWS WHERE view_name = 'EMP_DEPT_V';

-- ビューの依存オブジェクトを確認する（ビューが参照しているテーブル・ビュー）
SELECT name, type, referenced_name, referenced_type
FROM   USER_DEPENDENCIES
WHERE  name = 'EMP_DEPT_V';

-- コンパイルエラーがあるビューを確認する
SELECT object_name, status FROM USER_OBJECTS WHERE object_type = 'VIEW' AND status = 'INVALID';
-- INVALID: 基底テーブルの変更（列名変更・削除など）でビューが無効化されている

-- ビューを再コンパイルする
ALTER VIEW emp_dept_v COMPILE;

-- DDL を取得する（DBMS_METADATA）
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_DEPT_V') FROM DUAL;