Oracle View 建立語法詳解與進階應用模式

視圖是邏輯層封裝、安全控制與查詢簡化的核心工具。本文系統講解 CREATE VIEW 完整語法、簡單/複雜視圖差異、WITH CHECK OPTION / READ ONLY 限制機制，以及不可更新視圖的 INSTEAD OF 觸發器解法。

完整語法圖解

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

關鍵字選項深度解析

經典範例：部門薪資彙總視圖

CREATE OR REPLACE VIEW dept_salary_vw (
  dept_name, min_sal, max_sal, avg_sal, emp_cnt
) AS
SELECT d.dname,
       MIN(e.sal), MAX(e.sal), AVG(e.sal), COUNT(*)
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
WITH READ ONLY;  -- 報表視圖禁止寫入

簡單視圖 vs 複雜視圖 (可更新性判斷)

進階模式 1：安全性隔離 - WITH CHECK OPTION

-- 僅允許存取自己部門資料
CREATE OR REPLACE VIEW emp_self_vw AS
SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')  -- 應用設定 Context
WITH CHECK OPTION;  -- 防止 UPDATE deptno 竄改他部門資料

進階模式 2：複雜視圖寫入 - INSTEAD OF TRIGGER

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

CREATE OR REPLACE TRIGGER emp_dept_vw_iud
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_dept_vw
FOR EACH ROW
DECLARE
  v_deptno dept.deptno%TYPE;
BEGIN
  IF INSERTING OR UPDATING THEN
    -- 確保部門存在或自動建立
    SELECT deptno INTO v_deptno FROM dept WHERE dname = :NEW.dname;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      SELECT dept_seq.NEXTVAL INTO v_deptno FROM dual;
      INSERT INTO dept (deptno, dname, loc) VALUES (v_deptno, :NEW.dname, :NEW.loc);
  END IF;

  IF INSERTING THEN
    INSERT INTO emp (empno, ename, sal, deptno)
    VALUES (:NEW.empno, :NEW.ename, :NEW.sal, v_deptno);
  ELSIF UPDATING THEN
    UPDATE emp SET ename = :NEW.ename, sal = :NEW.sal, deptno = v_deptno
    WHERE empno = :OLD.empno;
  ELSIF DELETING THEN
    DELETE FROM emp WHERE empno = :OLD.empno;
  END IF;
END;
/

視圖效能優化技巧

• Predicate Pushing：優化器會嘗試將外層 WHERE 條件下推至視圖內部，複雜視圖可能受阻，可用 /*+ PUSH_PRED(view_alias) */ 強制。
• Materialized View 替代：極複雜聚合視圖頻繁查詢，改用 MV + 定時刷新，查詢效能提升數量級。
• 版本控管：OR REPLACE 會導致相依 PL/SQL 失效 (需重編譯)，生產環境建議 DROP VIEW ... CASCADE CONSTRAINTS 後重建，並同步部署相依程式碼。