【Oracle】ビューの使い方完全ガイド｜CREATE VIEW・更新可能ビュー・WITH CHECK OPTION・WITH READ ONLY・FORCE まで解説

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW ビュー名
    [(列別名1, 列別名2, ...)]
AS
    SELECT 文
[WITH CHECK OPTION [CONSTRAINT 制約名]]
[WITH READ ONLY];

-- IT 部門の社員だけを表示するビュー
CREATE VIEW v_emp_it AS
SELECT emp_id, emp_name, hire_date, salary
FROM emp
WHERE dept_id = 10;

-- 列に日本語の別名を付けてビューを作成
CREATE VIEW v_emp_summary (
    社員ID, 氏名, 部署名, 給与
) AS
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM emp e
JOIN dept d ON e.dept_id = d.dept_id;

CREATE VIEW v_emp_active AS
SELECT emp_id, emp_name, dept_id, salary
FROM emp
WHERE status = 'ACTIVE';

CREATE VIEW v_emp_with_dept AS
SELECT
    e.emp_id,
    e.emp_name,
    e.salary,
    d.dept_name,
    d.dept_id
FROM emp e
JOIN dept d ON e.dept_id = d.dept_id;

CREATE VIEW v_dept_stats AS
SELECT
    d.dept_name,
    COUNT(e.emp_id)   AS emp_count,
    AVG(e.salary)     AS avg_salary,
    MAX(e.salary)     AS max_salary
FROM dept d
LEFT JOIN emp e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- まだ存在しない future_table を参照するビューを先に作成
CREATE FORCE VIEW v_future AS
SELECT id, name FROM future_table;
-- → 作成はできるが INVALID 状態。future_table 作成後に有効になる

-- 既存の v_emp_it ビューの定義を変更する
CREATE OR REPLACE VIEW v_emp_it AS
SELECT emp_id, emp_name, hire_date, salary, phone
FROM emp
WHERE dept_id = 10
  AND status = 'ACTIVE';

-- ビューを通して UPDATE
UPDATE v_emp_active SET salary = salary * 1.1 WHERE emp_id = 100;

-- ビューを通して INSERT
-- ※ ビュー定義の WHERE 条件（status = 'ACTIVE'）を満たさない値でも INSERT できる（後述）
INSERT INTO v_emp_active (emp_id, emp_name, dept_id, salary) VALUES (999, '新入社員', 10, 300000);

-- ビューを通して DELETE
DELETE FROM v_emp_active WHERE emp_id = 999;

-- status = 'ACTIVE' の社員のみを扱うビュー（CHECK OPTION付き）
CREATE OR REPLACE VIEW v_emp_active AS
SELECT emp_id, emp_name, dept_id, salary, status
FROM emp
WHERE status = 'ACTIVE'
WITH CHECK OPTION CONSTRAINT ck_v_emp_active;

-- OK: status = 'ACTIVE' なので許可される
INSERT INTO v_emp_active (emp_id, emp_name, dept_id, salary, status)
VALUES (1001, '山田太郎', 10, 300000, 'ACTIVE');

-- NG: status = 'INACTIVE' はビューの条件を満たさない → ORA-01402 エラー
INSERT INTO v_emp_active (emp_id, emp_name, dept_id, salary, status)
VALUES (1002, '鈴木花子', 10, 300000, 'INACTIVE');
-- ORA-01402: ビューのWITH CHECK OPTIONの条件に違反しました

-- 集計ビューを参照専用に
CREATE OR REPLACE VIEW v_dept_stats AS
SELECT
    d.dept_name,
    COUNT(e.emp_id) AS emp_count,
    AVG(e.salary)   AS avg_salary
FROM dept d
LEFT JOIN emp e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
WITH READ ONLY;

-- DML を試みると ORA-42399 エラー
UPDATE v_dept_stats SET dept_name = '開発部' WHERE dept_name = '開発';
-- ORA-42399: 読取り専用ビューに対してDML操作を実行できません

-- 給与（salary）を除いた社員情報ビューを作成
CREATE VIEW v_emp_public AS
SELECT emp_id, emp_name, dept_id, hire_date
FROM emp;
-- salary 列はビューに含まれないため、このビューを通じては見えない

-- テーブルへの直接アクセスは禁止し、ビューへのアクセスのみ許可する
REVOKE SELECT ON emp       FROM general_user;
GRANT  SELECT ON v_emp_public TO general_user;

-- ログインユーザーが所属する部署の社員のみ表示するビュー
-- SYS_CONTEXT で現在のDBユーザー名を取得
CREATE VIEW v_emp_my_dept AS
SELECT e.emp_id, e.emp_name, e.salary
FROM emp e
WHERE e.dept_id = (
    SELECT dept_id FROM emp
    WHERE emp_name = SYS_CONTEXT('USERENV', 'SESSION_USER')
);
-- SYS_CONTEXT('USERENV','SESSION_USER') は現在のログインユーザー名を返す
-- USER キーワードより確実で、パッケージ実行時のコンテキスト切り替えにも対応できる

DROP VIEW v_emp_it;

-- Oracle 23c 以降は IF EXISTS が使える
DROP VIEW IF EXISTS v_emp_it;

-- 23c 未満では例外ハンドリングで対処
BEGIN
    EXECUTE IMMEDIATE 'DROP VIEW v_emp_it';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;  -- ORA-00942: 表またはビューが存在しません
END;
/

CREATE OR REPLACE VIEW v_order_detail_full AS
SELECT
    od.order_id,
    od.line_no,
    p.product_name,
    od.qty,
    od.unit_price,
    od.qty * od.unit_price                              AS subtotal,
    ROUND(od.qty * od.unit_price * (1 + od.tax_rate))  AS total_with_tax,
    c.customer_name,
    o.order_date
FROM order_detail od
JOIN orders   o ON od.order_id   = o.order_id
JOIN product  p ON od.product_id = p.product_id
JOIN customer c ON o.customer_id = c.customer_id
WITH READ ONLY;

-- 個人情報（生年月日・住所）を除いた社員ビュー
CREATE OR REPLACE VIEW v_emp_safe AS
SELECT emp_id, emp_name, dept_id, hire_date
FROM emp
WITH READ ONLY;

GRANT SELECT ON v_emp_safe TO general_role;

-- 有効な商品だけを扱うビュー（無効化された商品への変更を禁止）
CREATE OR REPLACE VIEW v_product_active AS
SELECT product_id, product_name, price, stock
FROM product
WHERE is_active = 1
WITH CHECK OPTION CONSTRAINT ck_v_product_active;