| 安全需求 | 視圖實現方式 | 範例 |
|---|
| 限制特定行 | WHERE 子句過濾 | CREATE VIEW V_MY_ORDERS AS SELECT * FROM ORDERS WHERE USER_ID = SYS_CONTEXT('USERENV','SESSION_USER') |
| 限制特定欄 | SELECT 列表投影 | CREATE VIEW V_EMP_PUBLIC AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE (隱藏薪資、身分證) |
| 限制行+欄 | WHERE + 投影結合 | CREATE VIEW V_HR_SELF AS SELECT EMP_NAME, PHONE FROM EMPLOYEE WHERE DEPT_ID = USER_DEPT |
| 多表聯結限制行 | JOIN + WHERE | CREATE VIEW V_DEPT_EMP AS SELECT D.DEPT_NAME, E.EMP_NAME FROM DEPT D JOIN EMP E ON D.DEPT_ID=E.DEPT_ID WHERE D.MGR_ID = CURRENT_USER |
| 統計彙總限制 | GROUP BY 聚合 | CREATE VIEW V_DEPT_SALARY_AVG AS SELECT DEPT_ID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT_ID (隱藏個人薪資) |
| 視圖疊加 | 基於其他視圖建立 | CREATE VIEW V_MGR_SUMMARY AS SELECT * FROM V_DEPT_SALARY_AVG WHERE DEPT_ID IN (SELECT DEPT_ID FROM V_MY_DEPTS) |
實戰範例：基於用戶名的行級安全視圖
-- 1. 基表增加用戶標識欄位
ALTER TABLE ORDERS ADD (OWNER VARCHAR2(30));
-- 2. 建立視圖：每用戶僅見自己資料
CREATE VIEW V_MY_ORDERS AS
SELECT ORDER_ID, ORDER_DATE, AMOUNT, STATUS
FROM ORDERS
WHERE OWNER = SYS_CONTEXT('USERENV', 'SESSION_USER');
-- 3. 授權視圖而非基表
GRANT SELECT ON V_MY_ORDERS TO APP_USER_ROLE;
-- 撤銷基表直接存取
REVOKE SELECT ON ORDERS FROM APP_USER_ROLE;
3. 邏輯資料獨立性：隔離應用與結構變更
視圖充當穩定介面層，吸收基表結構變更衝擊：
| 變更來源 | 無視圖影響 | 有視圖緩衝 |
|---|
| 基表結構變更 (增/減欄、改名、分表) | 所有應用 SQL 需修改 | 修改視圖定義，應用程式碼不動 |
| 應用需求變更 (新增欄位、調整過濾) | 需修改表結構 (DDL 風險、鎖表) | 修改視圖定義 (DDL 輕量、無資料移動) |
| 基表重構 (垂直/水平拆分、合併) | 應用全面改寫 | 視圖重寫聯結邏輯，應用介面不變 |
| 應用重構 (微服務拆分、API 版本) | 資料庫結構被綁定 | 視圖適配新介面，基表結構穩定 |
視圖可更新性規則
並非所有視圖可直接 DML。可更新視圖需滿足：
- 單一基表 (無 JOIN、UNION、GROUP BY、DISTINCT、聚合、子查詢)
- 無衍生欄位 (運算式、函數、常數)
- 包含基表所有 NOT NULL 且無預設值的欄位
- 無 GROUP BY、HAVING、CONNECT BY、START WITH
- 無 ROWNUM、LEVEL 偽欄
不可更新視圖：仍可建立 INSTEAD OF TRIGGER 實現自定義 DML 邏輯，將操作轉發至基表。
視圖類型擴展
| 類型 | 特性 | 適用場景 |
|---|
| 標準視圖 | 虛擬、即時查詢基表 | 簡化查詢、安全性、邏輯獨立 |
| 物化視圖 | 儲存查詢結果、支援快速刷新/完整刷新 | 資料倉儲彙總、遠端資料本地快取、昂貴查詢預計算 |
| 唯讀視圖 | WITH READ ONLY 子句 | 嚴格防止誤更新、報表專用 |
| 檢查選項視圖 | WITH CHECK OPTION | 確保 INSERT/UPDATE 符合視圖 WHERE 條件，防止「更新後看不見」 |
實務最佳實踐
- 命名規範：
V_<業務域>_<用途> (如 V_HR_EMP_SALARY、V_FIN_MONTHLY_SUMMARY)
- 文檔化：
COMMENT ON VIEW V_... IS '用途、基表、安全策略、維護者'
- 權限最小化：撤銷基表權限，僅授予視圖權限
- 避免過度嵌套：視圖套視圖超過 3 層影響效能與除錯
- 效能監控：視圖展開後的 SQL 計劃可能非最優，定期
EXPLAIN PLAN 驗證
- 版本控管：視圖 DDL 納入 Schema 遷移腳本 (Flyway/Liquibase)
總結
Oracle 視圖不只是「存儲的查詢」，是資料庫安全架構的關鍵組件 (行級/欄級存取控制)、應用解耦的介面層 (邏輯資料獨立性)、開發效率的倍增器 (簡化查詢)。善用視圖，能在不修改基表結構、不動應用程式碼的前提下，靈活應對安全合規、業務變更、架構演進。
- 上一篇文章： Oracle 刪除重複資料完整指南：部分欄位重複與完全重複兩大場景解決方案
- 下一篇文章： Oracle 10g 效能調優實戰經驗：從 OS 層到 SQL 層的系統化調優方法論
Copyright ©
程式碼之家 All Rights Reserved
