Oracle 視圖並非天生可更新。能否對視圖執行 INSERT/UPDATE/DELETE,取決於視圖定義結構、基礎表關係、約束條件。本文系統梳理視圖可更新性規則、非可更新視圖的 INSTEAD OF 觸發器實現模式、USER_UPDATABLE_COLUMNS 判斷視圖,並給出多表關聯視圖插入的最佳實踐模板。
| 視圖類型 | 可更新性 | 核心條件 |
|---|---|---|
| 單表視圖 (無聚合/分組/去重) | ✅ 完全可更新 | 基於單一基礎表,無 GROUP BY、DISTINCT、集合運算、連接、子查詢、偽列 (ROWNUM/LEVEL) |
| 單表視圖 (含衍生欄位/函數) | ⚠️ 部分可更新 | 衍生欄位不可直接更新,需 INSTEAD OF 觸發器處理 |
| 連接視圖 (Join View) | ⚠️ 有條件可更新 | 需滿足「關鍵值保留表」規則:僅能更新關鍵值保留表的欄位 |
| 含 GROUP BY/DISTINCT/UNION/子查詢 | ❌ 不可直接更新 | 必須使用 INSTEAD OF 觸發器 |
| WITH CHECK OPTION 視圖 | ⚠️ 受限 | INSERT/UPDATE 需滿足視圖 WHERE 條件,否則拋錯 |
-- 部門表 (主鍵 DEPTNO)
CREATE TABLE dept (deptno NUMBER PRIMARY KEY, dname VARCHAR2(30));
-- 員工表 (主鍵 EMPNO)
CREATE TABLE emp (empno NUMBER PRIMARY KEY, ename VARCHAR2(30), deptno NUMBER);
-- 連接視圖
CREATE VIEW emp_dept_v AS
SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- 分析:
-- EMP 表的主鍵 EMPNO 在視圖中保留 → EMP 是「關鍵值保留表」 → 可更新 EMP 欄位
-- DEPT 表的主鍵 DEPTNO 在視圖中**不唯一** (一個部門多員工) → DEPT 是「非關鍵值保留表」 → **不可直接更新 DEPT 欄位**
CREATE OR REPLACE TRIGGER trg_emp_dept_v_ioi
INSTEAD OF INSERT ON emp_dept_v
DECLARE
v_deptno dept.deptno%TYPE;
BEGIN
-- 1. 處理部門 (非關鍵值保留表):若不存在則插入,或取現有
BEGIN
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) VALUES (v_deptno, :NEW.dname);
END;
-- 2. 插入員工 (關鍵值保留表)
INSERT INTO emp (empno, ename, deptno)
VALUES (emp_seq.NEXTVAL, :NEW.ename, v_deptno);
END;
/
SHOW ERRORS
CREATE OR REPLACE TRIGGER trg_complex_view_ioi
INSTEAD OF INSERT ON complex_view
FOR EACH ROW -- 行級觸發器,針對每行 INSERT 執行
DECLARE
v_pk1 table1.pk1%TYPE;
v_pk2 table2.pk2%TYPE;
v_flag BOOLEAN := FALSE;
BEGIN
-- 1. 從 :NEW 獲取所有需插入的欄位值
-- 2. 按父表→子表順序處理 (避免外鍵衝突)
-- 父表 1
BEGIN
SELECT pk1 INTO v_pk1 FROM table1 WHERE uk_col = :NEW.col1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_pk1 := seq1.NEXTVAL;
INSERT INTO table1 (pk1, col1, col2) VALUES (v_pk1, :NEW.col1, :NEW.col2);
END;
-- 父表 2
BEGIN
SELECT pk2 INTO v_pk2 FROM table2 WHERE uk_col = :NEW.col3;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_pk2 := seq2.NEXTVAL;
INSERT INTO table2 (pk2, col3, col4) VALUES (v_pk2, :NEW.col3, :NEW.col4);
END;
-- 子表 (關聯父表 PK)
INSERT INTO child_table (pk1, pk2, attr1, attr2)
VALUES (v_pk1, v_pk2, :NEW.attr1, :NEW.attr2);
EXCEPTION
WHEN OTHERS THEN
-- 記錄錯誤日誌,拋出應用可識別的自定義異常
INSERT INTO view_dml_error_log (view_name, error_time, error_msg, sql_code)
VALUES ('COMPLEX_VIEW', SYSTIMESTAMP, SQLERRM, SQLCODE);
RAISE_APPLICATION_ERROR(-20001, 'View insert failed: ' || SQLERRM);
END;
/
SHOW ERRORS
SELECT
table_name,
column_name,
updatable,
insertable,
deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_DEPT_V'
ORDER BY column_name;
-- 輸出範例:
-- TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
-- EMP_DEPT_V EMPNO YES YES YES
-- EMP_DEPT_V ENAME YES YES YES
-- EMP_DEPT_V DEPTNO YES YES YES
-- EMP_DEPT_V DNAME NO NO NO -- 非關鍵值保留表欄位
WITH CHECK OPTION 時直接 INSERT 違反 WHERE 條件的行 → ORA-01402PRAGMA AUTONOMOUS_TRANSACTION 在觸發器中記錄錯誤日誌 (不回滾主事務)PKG_VIEW_API.INSERT_ROW(p_params) 封裝邏輯,禁止直接對視圖 DML