本篇文章給大家帶來了關于oracle的相關知識,其中主要介紹了關于視圖的相關問題,視圖 是一種數據庫對象,是從一個或者多個數據表或視圖中導出的 虛表,下面一起來看一下,希望對大家有幫助。
推薦教程:《oracle》
一、視圖
視圖 是一種數據庫對象,是從一個或者多個數據表或視圖中導出的 虛表。視圖所對應的 數據并不真正地存儲在視圖中,而是存儲在所引用的數據表中。視圖的結構和數據,是對數據表進行查詢的結果。
- 根據創建視圖時給定的條件,視圖可以是一個數據表的一部分,也可以是多個基表的聯合。
- 它存儲了要執行檢索的 查詢語句的定義,以便在引用該視圖時使用。
- 使用視圖的優點:
- 簡化數據操作:視圖可以簡化用戶處理數據的方式。
- 著重于特定數據:不必要的數據或敏感數據,可以不出現在視圖中。
- 視圖提供了一個簡單而有效的安全機制,可以定制不同用戶對數據的訪問權限。
- 提供向后兼容性:視圖使用戶能夠在表的架構更改時,為表創建向后兼容接口。
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`;
- 再次查詢表數據,發現表的數據也跟著更改了。
- 結論:視圖其實是一個虛擬的表,它的數據其實來自于表。
- 如果更改了視圖的數據,表的數據也自然會變化。
- 更改了表的數據,視圖也自然會變化。
- 一個視圖所存儲的并不是數據,而是一條 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![]()
- 什么叫鍵保留表。
- 鍵保留表,是理解連接視圖修改限制的一個基本概念。
- 該表的主鍵列全部顯示在視圖中,并且它們的值在視圖中都是唯一且非空的。
- 也就是說,表的鍵值在一個連接視圖中也是鍵值,那么就稱這個表為 鍵保留表。
- 該視圖中存在兩個表 T_OWNERS(業主表)和 T_OWNERTYPE(業主類型表)。
- 其中 T_OWNERS 表就是 鍵保留表。
- 因為 T_OWNERS 的主鍵也是作為視圖的主鍵。
- 鍵保留表的字段是可以更新的,而非鍵保留表是不能更新的。
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![]()
三、物化視圖
- 視圖是一個 虛擬表(也可以認為是一條語句)。
- 基于它創建時,指定的查詢語句返回的結果集。
- 每次訪問它,都會導致這個查詢語句被執行一次。
- 為了避免每次訪問都執行這個查詢,可以將這個查詢結果集存儲到一個 物化視圖(也叫實體化視圖)。
- 物化視圖 與普通的視圖相比的區別是。
- 物化視圖是建立的副本。
- 它類似于一張表,需要占用存儲空間。
- 而對一個物化視圖查詢的執行效率與查詢一個表是一樣的。
# 物化視圖日志(Tables)MLOG$_'表名'# 物化視圖(Tables)MV_'表名'
1. 創建物化視圖
- 生成數據
- BUILD IMMEDIATE:是在創建物化視圖的時候就生成數據(默認)。
- BUILD DEFERRED:則在創建時不生成數據,以后根據需要再生成數據。
- REFRESH:刷新,指當基表發生了 DML 操作后,物化視圖何時采用哪種方式和基表進行同步。
REFRESH 后跟著指定的刷新方法有三種:FAST、COMPLETE、FORCE。
- FAST:采用增量刷新,只刷新自上次刷新以后進行的修改。
- COMPLETE:對整個物化視圖進行完全的刷新。
- FORCE:刷新時去判斷是否可以快速刷新,如果可以則采用 FAST 方式,否則采用 COMPLETE 的方式(默認)。
- 刷新的模式。
- ON DEMAND:指需要手動刷新物化視圖(默認)。
- 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);
- 再次執行查詢語句,會發現新插入的數據并沒有出現在物化視圖中。
- 需要通過下面的 PL/SQL 語句,手動刷新物化視圖。
begin -- `C`完全刷新(COMPLETE) DBMS_MVIEW.refresh('MV_ADDRESS', 'C'); end;
- 或者通過下面的命令,手動刷新物化視圖。
-- 注意:此命令需要在命令窗口中執行。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;
- 注意:創建增量刷新的物化視圖。
- 創建物化視圖中涉及表的物化視圖日志。
- 在查詢語句中,必須包含所有表的 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