Oracle 視圖完全指南：建立、DML 操作、複雜視圖限制與 INSTEAD OF 觸發器突破

Oracle 視圖完全指南：建立、DML 操作、複雜視圖限制與 INSTEAD OF 觸發器突破

視圖 是儲存查詢定義的虛擬表，不佔用資料儲存空間，卻能簡化存取、隱藏複雜度、實作安全控制。本文系統整理視圖建立語法、簡單/複雜視圖差異、DML 限制、WITH CHECK OPTION/WITH READ ONLY 保護機制，以及透過 INSTEAD OF 觸發器對複雜視圖進行寫入的完整模式。

視圖核心優勢

• 簡化操作：將多表連接、複雜過濾封裝，使用者只需 SELECT * FROM v_emp_dept。
• 多角度存取：同一基礎表可建立不同視圖滿足不同業務角色 (財務/行銷/稽核)。
• 邏輯獨立性：基礎表結構變更 (增欄位、調整型別) 時，視圖可透過 CREATE OR REPLACE 平滑遷移，應用端無感。
• 資料安全：GRANT SELECT ON v_salary TO hr_role 只暴露必要欄位，隱藏薪資細節。
• 查詢清晰化：將常用 CTE/子查詢固化為視圖，主查詢更易閱讀。

建立視圖語法全解

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias1, alias2, ...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY];

簡單視圖：可直接 INSERT/UPDATE/DELETE

定義：單一基礎表、無彙總/分組/連接/DISTINCT/集合運算/子查詢/虛擬欄位。

-- 範例：薪資 > 2000 的員工視圖
CREATE VIEW v_emp_high_sal AS
SELECT empno, ename, job, sal, deptno
FROM   emp
WHERE  sal > 2000;

-- 直接 DML
INSERT INTO v_emp_high_sal VALUES (9999, 'TEST', 'CLERK', 2500, 10);
UPDATE v_emp_high_sal SET sal = 3000 WHERE empno = 9999;
DELETE FROM v_emp_high_sal WHERE empno = 9999;

Oracle 會自動將 DML 轉譯至基礎表，並檢查 WITH CHECK OPTION 條件。

複雜視圖：原生禁止 DML，需 INSTEAD OF 觸發器

符合下列任一特徵即為複雜視圖：

1. 包含集合運算子 (UNION, MINUS...)
2. 包含群組函數 (SUM, AVG, COUNT...) 或 GROUP BY
3. 包含 CONNECT BY, START WITH, DISTINCT
4. 多表連接 (JOIN)
5. 包含虛擬欄位 / CASE / DECODE 運算式

-- 典型複雜視圖：部門彙總
CREATE OR REPLACE VIEW v_dept_summary AS
SELECT d.deptno, d.dname, d.loc,
       COUNT(e.empno) AS cnt,
       ROUND(AVG(e.sal),2) AS avg_sal,
       SUM(e.sal) AS sum_sal
FROM   dept d
LEFT JOIN emp e ON e.deptno = d.deptno
GROUP BY d.deptno, d.dname, d.loc;

對此視圖執行 INSERT/UPDATE/DELETE 會報 ORA-01779: cannot modify a column which maps to a non key-preserved table 或 ORA-01732: data manipulation operation not legal on this view。

突破限制：INSTEAD OF 觸發器

INSTEAD OF 觸發器定義於視圖上，取代 原本的 DML 動作，由開發者撰寫 PL/SQL 決定如何同步更新多張基礎表。

語法

CREATE [OR REPLACE] TRIGGER trig_name
INSTEAD OF INSERT OR UPDATE OR DELETE ON view_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE ...
BEGIN
   -- :NEW 代表新值 (INSERT/UPDATE)，:OLD 代表舊值 (UPDATE/DELETE)
   -- 根據 INSERTING/UPDATING/DELETING 判斷執行邏輯
END;

完整範例：部門-員工連接視圖的三合一觸發器

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.dname, d.loc
FROM   emp e JOIN dept d ON e.deptno = d.deptno;

CREATE OR REPLACE TRIGGER v_emp_dept_iod_trg
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_emp_dept
FOR EACH ROW
DECLARE
    v_emp_cnt  NUMBER;
    v_dept_cnt NUMBER;
BEGIN
    IF INSERTING THEN
        -- 檢查員工是否存在
        SELECT COUNT(*) INTO v_emp_cnt FROM emp WHERE empno = :NEW.empno;
        -- 檢查部門是否存在
        SELECT COUNT(*) INTO v_dept_cnt FROM dept WHERE deptno = :NEW.deptno;

        IF v_emp_cnt = 0 THEN
            INSERT INTO emp (empno, ename, job, sal, deptno)
            VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.sal, :NEW.deptno);
        END IF;
        IF v_dept_cnt = 0 THEN
            INSERT INTO dept (deptno, dname, loc)
            VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);
        END IF;

    ELSIF UPDATING THEN
        UPDATE emp SET ename = :NEW.ename, job = :NEW.job, sal = :NEW.sal
        WHERE empno = :NEW.empno;
        UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc
        WHERE deptno = :NEW.deptno;

    ELSIF DELETING THEN
        -- 僅刪除員工，保留部門 (視業務決定)
        DELETE FROM emp WHERE empno = :OLD.empno;
    END IF;
END;
/

-- 測試
INSERT INTO v_emp_dept VALUES (8888, 'NEW', 'CLERK', 2000, 50, 'NEW_DEPT', 'TAIPEI');
UPDATE v_emp_dept SET sal = 2500, dname = 'UPD_DEPT' WHERE empno = 8888;
DELETE FROM v_emp_dept WHERE empno = 8888;

安全強化：WITH CHECK OPTION 與 WITH READ ONLY

-- 強制 WHERE 條件不可被更新違反
CREATE OR REPLACE VIEW v_emp_20 AS
SELECT * FROM emp WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT v_emp_20_ck;

-- 嘗試將 deptno 改為 30 → ORA-01402: view WITH CHECK OPTION where-clause violation
UPDATE v_emp_20 SET deptno = 30 WHERE empno = 7369;

-- 唯讀視圖
CREATE OR REPLACE VIEW v_emp_20_ro AS
SELECT * FROM emp WHERE deptno = 20
WITH READ ONLY;

-- 任何 DML → ORA-42399: cannot perform a DML operation on a read-only view

視圖管理常用查詢

-- 列出當前使用者視圖
SELECT view_name, text_length, text, read_only
FROM   user_views;

-- 刪除視圖
DROP VIEW view_name;

最佳實踐檢核表

• ☐ 視圖命名規範：V__ (如 V_HR_EMP_SALARY)
• ☐ 複雜視圖皆建立 INSTEAD OF 觸發器並完整測試 INSERT/UPDATE/DELETE 路徑
• ☐ 敏感資料視圖加上 WITH CHECK OPTION 或 WITH READ ONLY
• ☐ 定期檢視 USER_DEPENDENCIES 確認視圖相依關係，避免基礎表 DDL 導致視圖失效
• ☐ 大量存取視圖考慮 MATERIALIZED VIEW (物化視圖) 提升效能

善用視圖與 INSTEAD OF 觸發器，能在「資料模型正規化」與「應用存取簡單化」之間取得最佳平衡。