歸納總結Oracle視圖知識點

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

歸納總結Oracle視圖知識點

推薦教程:《oracle

一、視圖

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


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

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

1. 創建修改視圖

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

參數 說明
OR REPLACE 若所創建的試圖已經存在,oracle 自動重建該視圖
FORCE 不管基表是否存在 Oracle 都會自動創建該視圖
sub_query 一條完整的 SELECT 語句,可以在該語句中定義別名
WITH CHECK OPTION 數據表插入或修改的數據行,必須滿足視圖定義的約束
WITH READ ONLY 該視圖上不能進行任何 DML 操作

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
    歸納總結Oracle視圖知識點

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
    歸納總結Oracle視圖知識點

4. 帶錯誤視圖

  • 創建一個視圖,如果視圖的 SQL 語句所設計的表并不存在。
-- 錯誤視圖,創建不成功create or replace view `view_TEMP`as-- `T_TEMP`表不存在select * from `T_TEMP`;
  • T_TEMP 表并不存在。
    歸納總結Oracle視圖知識點
  • 有的時候,創建視圖時的表可能并不存在,但是以后可能會存在。
  • 如果此時需要創建這樣的視圖,需要添加 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
    歸納總結Oracle視圖知識點
  • 什么叫鍵保留表。
  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
    歸納總結Oracle視圖知識點

三、物化視圖

  • 視圖是一個 虛擬表(也可以認為是一條語句)。
  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;

  1. 或者通過下面的命令,手動刷新物化視圖。
-- 注意:此命令需要在命令窗口中執行。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 方式建立物化視圖日志)

  • 當向地址表插入數據后,物化視圖日志的內容。
參數 說明
SNAPTIME$$ 用于表示刷新時間。
DMLTYPE$$ 用于表示 DML 操作類型(I 表示 INSERT,D 表示 DELETE,U 表示 UPDATE)。
OLD_NEW$$ 用于表示這個值是新值還是舊值(N(EW)表示新值,O(LD)表示舊值,U 表示 UPDATE 操作)。
CHANGE_VECTOR$$ 表示修改矢量,用來表示被修改的是哪個或哪幾個字段(此列是 RAW 類型)。
其實 Oracle 采用的方式就是用每個 BIT 位去映射一個列。
插入操作顯示為:FE,刪除顯示為:OO,更新操作則根據更新字段的位置而顯示不同的值。

  • 當手動刷新物化視圖后,物化視圖日志被清空,物化視圖更新。
begin     DBMS_MVIEW.refresh('MV_ADDRESS4', 'C');end;

推薦教程:《oracle

? 版權聲明
THE END
喜歡就支持一下吧
點贊12 分享