Oracle 視圖 WITH CHECK OPTION 詳解：確保 DML 符合視圖定義條件

核心問題

視圖定義包含 WHERE 條件時，若直接對視圖執行 INSERT/UPDATE，可能導致資料「脫離」視圖範圍，造成後續查詢視圖看不到該資料。

範例場景

-- 基礎表
CREATE TABLE employee (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  dept NUMBER,
  job VARCHAR2(50),
  hiredate DATE
);

-- 僅顯示部門 10 的視圖
CREATE VIEW emp_view2 AS
SELECT id, name, dept, job, hiredate
FROM employee
WHERE dept = 10;

無 CHECK OPTION 的風險

-- 透過視圖更新部門為 20
UPDATE emp_view2 SET dept = 20 WHERE dept = 10;
-- 執行成功！但視圖現在查無資料 (部門 10 已無員工)
SELECT * FROM emp_view2;  -- 空結果

資料仍在基礎表中，但不再符合視圖定義條件，導致視圖「失效」。

WITH CHECK OPTION 解決方案

CREATE OR REPLACE VIEW emp_view2 AS
SELECT id, name, dept, job, hiredate
FROM employee
WHERE dept = 10
WITH CHECK OPTION;

驗證效果

-- 再次嘗試更新部門
UPDATE emp_view2 SET dept = 20 WHERE dept = 10;
-- 錯誤：ORA-01402: view WITH CHECK OPTION where-clause violation

資料庫攔截違反視圖條件的 DML，保護視圖資料完整性。

INSERT 驗證

-- 嘗試插入部門 20 的員工
INSERT INTO emp_view2 (id, name, dept, job, hiredate)
VALUES (999, 'TEST', 20, 'CLERK', SYSDATE);
-- 錯誤：ORA-01402: view WITH CHECK OPTION where-clause violation

進階：巢狀視圖與 CHECK OPTION

-- 基礎視圖：部門 10
CREATE VIEW v_dept10 AS
SELECT * FROM employee WHERE dept = 10;

-- 衍生視圖：薪資 > 5000
CREATE VIEW v_dept10_highsal AS
SELECT * FROM v_dept10 WHERE sal > 5000
WITH CHECK OPTION;  -- 只檢查 sal > 5000

-- 衍生視圖加上 CASCADED (預設)
CREATE VIEW v_dept10_highsal_c AS
SELECT * FROM v_dept10 WHERE sal > 5000
WITH CASCADED CHECK OPTION;  -- 檢查 sal > 5000 AND dept = 10

-- 衍生視圖加上 LOCAL
CREATE VIEW v_dept10_highsal_l AS
SELECT * FROM v_dept10 WHERE sal > 5000
WITH LOCAL CHECK OPTION;  -- 只檢查 sal > 5000

可更新視圖判斷

查詢 USER_UPDATABLE_COLUMNS 確認視圖欄位是否可更新：

SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_VIEW2';

最佳實踐

1. 所有具備 WHERE 條件且需支援 DML 的視圖，**務必加上 WITH CHECK OPTION**
2. 巢狀視圖建議使用 CASCADED (預設) 確保完整性
3. 複雜連接視圖改用 INSTEAD OF 觸發器控制 DML 行為
4. 文件化視圖的業務意圖與檢查規則

限制

• 不影響直接對基礎表的 DML
• 含聚合函數、GROUP BY、DISTINCT、UNION 等不可更新視圖無效
• WITH READ ONLY 視圖本身禁止 DML，無需 CHECK OPTION