Oracle 視圖插入資料完全指南：可更新視圖條件與 INSTEAD OF 觸發器實戰

視圖可更新性判斷

查詢 USER_UPDATABLE_COLUMNS 確認：

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

非連接視圖 (單表視圖)

• 基於單一基礎表建立
• 原則上皆可更新：支援 INSERT/UPDATE/DELETE
• 例外：包含聚合、GROUP BY、DISTINCT、ROWMID、運算式等衍生欄位不可更新

連接視圖 (多表視圖) 更新規則

關鍵概念：**碼保留表** 與 **非碼保留表**

定義

-- 部門表 (DEPTNO 為主鍵)
-- 員工表 (EMPNO 為主鍵)

CREATE VIEW emp_dept AS
SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

• 碼保留表：連接後仍保有唯一鍵特性的表 (此例 EMP，EMPNO 仍唯一)
• 非碼保留表：連接後失去唯一鍵特性的表 (此例 DEPT，DEPTNO 重複)

INSERT 規則

1. 不可明確或隱含引用 **非碼保留表** 的欄位
2. 若視圖定義含 WITH CHECK OPTION，**完全禁止 INSERT**
3. 僅能插入碼保留表的欄位，非碼保留表欄位由資料庫自動處理 (需有預設值或可為 NULL)

INSTEAD OF 觸發器：複雜視圖更新的標準解法

適用於：連接視圖、含運算式視圖、需自定義 DML 邏輯

語法

CREATE OR REPLACE TRIGGER view_name_io_trg
INSTEAD OF INSERT OR UPDATE OR DELETE ON view_name
FOR EACH ROW
DECLARE
  v_var TYPE;
BEGIN
  IF INSERTING THEN
    -- 解析 :NEW 欄位，分別插入基礎表
    INSERT INTO base_table1 (...) VALUES (...);
    INSERT INTO base_table2 (...) VALUES (...);
  ELSIF UPDATING THEN
    UPDATE base_table1 SET ... WHERE ...;
    UPDATE base_table2 SET ... WHERE ...;
  ELSIF DELETING THEN
    DELETE FROM base_table1 WHERE ...;
    DELETE FROM base_table2 WHERE ...;
  END IF;
END;
/

實戰範例：emp_dept 視圖

CREATE OR REPLACE TRIGGER emp_dept_io_trg
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_dept
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    -- 必須先插入非碼保留表 (部門)，再插入碼保留表 (員工)
    -- 這裡假設部門已存在，或需先檢查/建立
    INSERT INTO emp (empno, ename, deptno)
    VALUES (:NEW.empno, :NEW.ename, :NEW.deptno);
    
  ELSIF UPDATING THEN
    UPDATE emp
    SET ename = :NEW.ename,
        deptno = :NEW.deptno
    WHERE empno = :OLD.empno;
    
  ELSIF DELETING THEN
    DELETE FROM emp WHERE empno = :OLD.empno;
  END IF;
END;
/

完整 DML 支援範本

CREATE OR REPLACE TRIGGER complex_view_io_trg
INSTEAD OF INSERT OR UPDATE OR DELETE ON complex_view
FOR EACH ROW
DECLARE
  v_cnt NUMBER;
BEGIN
  IF INSERTING THEN
    -- 1. 處理主表
    INSERT INTO main_table (id, col1, col2, created_at)
    VALUES (:NEW.id, :NEW.col1, :NEW.col2, SYSDATE);
    
    -- 2. 處理關聯表 (可選)
    IF :NEW.related_id IS NOT NULL THEN
      INSERT INTO related_table (main_id, attr) 
      VALUES (:NEW.id, :NEW.related_attr);
    END IF;
    
  ELSIF UPDATING THEN
    UPDATE main_table
    SET col1 = :NEW.col1,
        col2 = :NEW.col2,
        updated_at = SYSDATE
    WHERE id = :OLD.id;
    
    -- 同步關聯表
    UPDATE related_table
    SET attr = :NEW.related_attr
    WHERE main_id = :OLD.id;
    
  ELSIF DELETING THEN
    -- 級聯刪除 (順序重要)
    DELETE FROM related_table WHERE main_id = :OLD.id;
    DELETE FROM main_table WHERE id = :OLD.id;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 記錄錯誤日誌
    RAISE;  -- 重拋讓呼叫端知曉
END;
/

效能與設計建議

常見錯誤代碼