歸納總結Oracle視圖知識點

本篇文章給大家帶來了關于oracle的相關知識，其中主要介紹了關于視圖的相關問題，視圖 是一種數據庫對象，是從一個或者多個數據表或視圖中導出的 虛表，下面一起來看一下，希望對大家有幫助。

推薦教程：《oracle》

一、視圖

視圖 是一種數據庫對象，是從一個或者多個數據表或視圖中導出的 虛表。視圖所對應的 數據并不真正地存儲在視圖中，而是存儲在所引用的數據表中。視圖的結構和數據，是對數據表進行查詢的結果。

• 根據創建視圖時給定的條件，視圖可以是一個數據表的一部分，也可以是多個基表的聯合。
• 它存儲了要執行檢索的 查詢語句的定義，以便在引用該視圖時使用。

• 使用視圖的優點：

1. 簡化數據操作：視圖可以簡化用戶處理數據的方式。
2. 著重于特定數據：不必要的數據或敏感數據，可以不出現在視圖中。
3. 視圖提供了一個簡單而有效的安全機制，可以定制不同用戶對數據的訪問權限。
4. 提供向后兼容性：視圖使用戶能夠在表的架構更改時，為表創建向后兼容接口。

1. 創建修改視圖

CREATE [OR REPLACE] [FORCE] VIEW 'view_name'AS 'sub_query'[WITH CHECK OPTION]-- 只讀[WITH READ ONLY]

2. 刪除視圖

DROP VIEW 'view_name';

二、試圖案例

1. 簡單視圖

• 如果視圖中的語句只是 單表查詢，并且 沒有聚合函數，我們就稱之為 簡單視圖。

• 需求：創建視圖，業主類型為 1 的業主信息。

create or replace view `view_owners` asselect * from T_OWNERS where ownertypeid = 1;

• 查詢該視圖。

select * from `view_owners` where addressid = 1;

• 就像使用表一樣，去使用視圖就可以了。
• 對于簡單視圖，不僅可以用查詢，還可以增刪改記錄。

update `view_owners` set name='王剛' where id = 2;select * from `view_owners`;

• 再次查詢表數據，發現表的數據也跟著更改了。
• 結論：視圖其實是一個虛擬的表，它的數據其實來自于表。

1. 如果更改了視圖的數據，表的數據也自然會變化。
2. 更改了表的數據，視圖也自然會變化。
3. 一個視圖所存儲的并不是數據，而是一條 SQL 語句。

2. with check option 帶檢查約束視圖

• 需求：根據 T_ADDRESS（地址表）創建視圖 VIEW_ADDRESS，內容為 區域ID 為 2 的記錄。

create or replace view `view_address` asselect * from T_ADDRESS where areaid = 2 with check option;

• 執行更新語句，報錯。

-- 無法修改成功-- 因為該視圖的條件是`arreaid = 2`，不能修改為`arreaid = 1`。update `view_address` set areaid = 1 where id = 4;

• 錯誤信息：
  ORA-01402: view WITH CHECK OPTION where-clause violation
  

3. with read only 只讀視圖

• 如果創建一個視圖，并不希望用戶能對視圖進行修改。
• 就需要創建視圖時，指定 WITH READ ONLY 選項，這樣創建的視圖就是一個 只讀視圖。

• 需求：修改視圖為只讀視圖。

create or replace view `view_owners` asselect * from T_OWNERS where ownertypeid = 1 with read only;

• 執行更新語句，報錯。

update `view_owners` set name='王強' where id = 2;

• 錯誤信息：
  ORA-42399: cannot perform a DML operation on a read-only view
  

4. 帶錯誤視圖

• 創建一個視圖，如果視圖的 SQL 語句所設計的表并不存在。

-- 錯誤視圖，創建不成功create or replace view `view_TEMP`as-- `T_TEMP`表不存在select * from `T_TEMP`;

• T_TEMP 表并不存在。
  

• 有的時候，創建視圖時的表可能并不存在，但是以后可能會存在。
• 如果此時需要創建這樣的視圖，需要添加 FORCE 選項。

create or replace FORCE view `view_TEMP` asselect * from `T_TEMP`;

5. 復雜視圖

• 復雜視圖，就是視圖的 SQL 語句中，有 聚合函數 或 多表關聯查詢。

5.1 多表關聯查詢

• 需求：創建視圖，查詢顯示業主編號，業主名稱，業主類型名稱。

create or replace view `view_owners` asselect o.id '業主編號', o.name '業主名稱', ot.name '業主類型' from T_OWNERS o, T_OWNERTYPE ot  where o.ownertypeid = ot.id;

• 查詢該視圖。

select * from `view_owners`;

• 修改該視圖。

-- 更新成功update view_owners set '業主名稱' = '范小冰' where '業主編號' = 1;  -- 更新失敗update view_owners set '業主類型' ='普通居民' where '業主編號' = 1;

• 出錯誤提示：是說所需改的列不屬于鍵保留表的列。
  ORA-01779: cannot modify a column which maps to a non key-preserved table
  

• 什么叫鍵保留表。

1. 鍵保留表，是理解連接視圖修改限制的一個基本概念。
2. 該表的主鍵列全部顯示在視圖中，并且它們的值在視圖中都是唯一且非空的。
3. 也就是說，表的鍵值在一個連接視圖中也是鍵值，那么就稱這個表為 鍵保留表。

• 該視圖中存在兩個表 T_OWNERS（業主表）和 T_OWNERTYPE（業主類型表）。

1. 其中 T_OWNERS 表就是 鍵保留表。
2. 因為 T_OWNERS 的主鍵也是作為視圖的主鍵。
3. 鍵保留表的字段是可以更新的，而非鍵保留表是不能更新的。

7.2 分組聚合統計查詢

• 需求：創建視圖，按年月統計水費金額。

create view `view_account_sum` as-- 必須 + `money_sum` 別名select year, month, sum(money) money_sum from `T_ACCOUNT` group by year, monthorder by year, month;

• 修改該視圖。

update view_account_sum set money_sum = money_sum - 100 where year = 2012 and month = 12;

• 此例用到聚合函數，沒有鍵保留表，所以無法執行 update。
  ORA-01732: data manipulation operation not legal on this view
  

三、物化視圖

• 視圖是一個 虛擬表（也可以認為是一條語句）。

1. 基于它創建時，指定的查詢語句返回的結果集。
2. 每次訪問它，都會導致這個查詢語句被執行一次。
3. 為了避免每次訪問都執行這個查詢，可以將這個查詢結果集存儲到一個 物化視圖（也叫實體化視圖）。

• 物化視圖 與普通的視圖相比的區別是。

1. 物化視圖是建立的副本。
2. 它類似于一張表，需要占用存儲空間。
3. 而對一個物化視圖查詢的執行效率與查詢一個表是一樣的。

# 物化視圖日志（Tables）MLOG$_'表名'# 物化視圖（Tables）MV_'表名'

1. 創建物化視圖

• 生成數據

1. BUILD IMMEDIATE：是在創建物化視圖的時候就生成數據（默認）。
2. BUILD DEFERRED：則在創建時不生成數據，以后根據需要再生成數據。

• REFRESH：刷新，指當基表發生了 DML 操作后，物化視圖何時采用哪種方式和基表進行同步。
  REFRESH 后跟著指定的刷新方法有三種：FAST、COMPLETE、FORCE。

1. FAST：采用增量刷新，只刷新自上次刷新以后進行的修改。
2. COMPLETE：對整個物化視圖進行完全的刷新。
3. FORCE：刷新時去判斷是否可以快速刷新，如果可以則采用 FAST 方式，否則采用 COMPLETE 的方式（默認）。

• 刷新的模式。

1. ON DEMAND：指需要手動刷新物化視圖（默認）。
2. ON COMMIT：指在基表發生 COMMIT 操作時自動刷新。

CREATE MATERIALIZED VIEW 'view_name'-- 立即顯示 | 延遲顯示[ 'BUILD IMMEDIATE' | BUILD DEFERRED ] -- 快 | 完全 | 快or完全REFRESH [ FAST | COMPLETE | 'FORCE' ]  [ON [ 'DEMAND' | COMMIT ] | START WITH (start_time) NEXT (next_time)]AS 'sub_query';

四、物化試圖案例

1. 創建 手動刷新 的物化視圖

• 需求：查詢地址ID、地址名稱和所屬區域名稱。

create materialized view `mv_address`asselect ad.id, ad.name adname, ar.name ar_name  from t_address ad, t_area ar  where ad.areaid = ar.id;

• 執行查詢語句。

select * from `mv_address`;

• 向基表 T_ADDRESS（地址表）中插入一條新記錄。

insert into `t_address` values (8, '宏福苑小區', 1, 1);

• 再次執行查詢語句，會發現新插入的數據并沒有出現在物化視圖中。

1. 需要通過下面的 PL/SQL 語句，手動刷新物化視圖。

begin 	-- `C`完全刷新（COMPLETE） 	DBMS_MVIEW.refresh('MV_ADDRESS', 'C'); end;

2. 或者通過下面的命令，手動刷新物化視圖。

-- 注意：此命令需要在命令窗口中執行。EXEC DBMS_MVIEW.refresh('MV_ADDRESS', 'C');

• DBMS_MVIEW.refresh 是系統內置的存儲過程。
• 執行命令后，再次查詢物化視圖，就可以查詢到最新的數據了。

2. 創建 自動刷新 的物化視圖

• 創建此物化視圖后，當 T_ADDRESS 表發生變化時，MV_ADDRESS 自動跟著改變。

create materialized view `mv_address_2`refresh-- 自動刷新on commit asselect ad.id,ad.name adname,ar.name ar_name  from t_address ad,t_area ar  where ad.areaid=ar.id;

3. 創建時 不生成數據 的物化視圖

create materialized view `mv_address_3`-- 創建不生成數據build deferred  refresh-- 自動刷新on commit asselect ad.id,ad.name adname,ar.name ar_name  from t_address ad,t_area ar  where ad.areaid=ar.id;

• 創建后執行下列語句查詢物化視圖，沒有數據。

 select * from `mv_address_3`;

• 執行下列語句生成數據。

begin    DBMS_MVIEW.refresh('MV_ADDRESS3', 'C');end;

• 由于創建時指定的 on commit。
• 所以在修改數據后能立刻看到最新數據，無須再次執行 refresh。

4. 創建 增量刷新 的物化視圖

• 如果創建增量刷新的物化視圖，必須首先創建物化視圖日志。
• 記錄基表發生了哪些變化，用日志去更新物化視圖。

-- 根據`rowid`create materialized view log on t_address with rowid; create materialized view log on t_area with rowid;

• 創建的物化視圖日志名稱為 MLOG$_表名稱。

create materialized view `mv_address_4`-- 增量刷新refresh fast  asselect ad.rowid adrowid, ar.rowid arrowid, ad.id, ad.name adname, ar.name ar_name   -- 需要創建兩表日志from t_address ad, t_area ar   where ad.areaid = ar.id;

• 注意：創建增量刷新的物化視圖。

1. 創建物化視圖中涉及表的物化視圖日志。
2. 在查詢語句中，必須包含所有表的 rowid（以 rowid 方式建立物化視圖日志）

• 當向地址表插入數據后，物化視圖日志的內容。

• 當手動刷新物化視圖后，物化視圖日志被清空，物化視圖更新。

begin     DBMS_MVIEW.refresh('MV_ADDRESS4', 'C');end;

推薦教程：《oracle》