Oracle-day03 上

標籤：Oracle

一、視圖
（一）什麼是視圖
視圖是一種資料庫物件，是從一個或者多個資料表或視圖中匯出的虛表，視圖所對應的資料並不真正地儲存在視圖中，而是儲存在所引用的資料表中，視圖的結構和資料是對資料表進行查詢的結果。根據建立視圖時給定的條件，視圖可以是一個資料表的一部分，也可以是多個基表的聯合，它儲存了要執行檢索的查詢語句的定義，以便在引用該視圖時使用。
使用視圖的優點：
1.簡化資料操作：視圖可以簡化使用者處理資料的方式。
2.著重於特定資料：不必要的資料或敏感性資料可以不出現在視圖中。
3.視圖提供了一個簡單而有效安全機制，可以定製不同使用者對資料的存取權限。
4.提供向後相容性：視圖使使用者能夠在表的架構更改時為表建立向後相容介面。
（二）建立或修改視圖文法

CREATE [OR REPLACE] [FORCE] VIEW view_nameAS subquery[WITH CHECK OPTION ][WITH READ ONLY]

選項解釋：
OR REPLACE ：若所建立的試圖已經存在，ORACLE 自動重建該視圖；
FORCE：不管基表是否存在 ORACLE 都會自動建立該視圖；
subquery：一條完整的 SELECT 語句，可以在該語句中定義別名；
WITH CHECK OPTION ：插入或修改的資料行必須滿足視圖定義的約束；
WITH READ ONLY ：該視圖上不能進行任何 DML 操作。
（三）刪除視圖文法

DROP VIEW view_name

（四）案例

1. 簡單視圖的建立與使用
   什麼是簡單視圖？如果視圖中的語句只是單表查詢，並且沒有彙總函式，我們就
   稱之為簡單視圖。
   需求：建立視圖 ：業主類型為 1 的業主資訊
   語句：

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1

利用該視圖進行查詢`select * from view_owners1 where addressid=1;`就像使用表一樣去使用視圖就可以了。對於簡單視圖，我們不僅可以用查詢，還可以增刪改記錄。我們下面寫一條更新的語句，試一下：`update view_owners1 set name=‘王剛‘ where id=2;`重新查詢：`select * from view_owners1`查詢結果如下： ![](http://i2.51cto.com/images/blog/201806/04/0e4d2b4cc58b990c6f1394997171b959.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)結果已經更改成功。我們重新查詢表資料 ![](http://i2.51cto.com/images/blog/201806/04/6b2738461c0f6d123c2bc30440dd3f3c.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)發現表的資料也跟著更改了。由此我們得出結論：視圖其實是一個虛擬表，它的資料其實來自於表。如果更改了視圖的資料，表的資料也自然會變化，更改了表的資料，視圖也自然會變化。一個視圖所儲存的並不是資料，而是一條 SQL語句。2. 帶檢查約束的視圖需求：根據地址表（T_ADDRESS）建立視圖 VIEW_ADDRESS2 ,內容為地區 ID為 2 的記錄。語句：

create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option

執行下列更新語句：

[/align]
update view_address2 set areaid=1 where id=4

系統提示如下錯誤資訊： ![](http://i2.51cto.com/images/blog/201806/04/8c31e9189ae052e65654d62e40df9459.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)3. 唯讀視圖的建立與使用如果我們建立一個視圖，並不希望使用者能對視圖進行修改，那我們就需要建立視圖時指定 WITH READ ONLY選項，這樣建立的視圖就是一個唯讀視圖。需求：將上邊的視圖修改為唯讀視圖語句：

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only

修改後，再次執行 update 語句，會出現如下錯誤提示 ![](http://i2.51cto.com/images/blog/201806/04/fd9df668dc0c797df19656c6fba93109.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)4. 建立帶錯誤的視圖我們建立一個視圖，如果視圖的 SQL 陳述式所設計的表並不存在，如下

create or replace view view_TEMP as
select * from T_TEMP

T_TEMP 表並不存在，此時系統會給出錯誤提示 ![](http://i2.51cto.com/images/blog/201806/04/016f9401e4e7ee07a977d9e1e11b55dd.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)有的時候，我們建立視圖時的表可能並不存在，但是以後可能會存在，我們如果此時需要建立這樣的視圖，需要添加 FORCE 選項，SQL 陳述式如下：create or replace FORCE view view_TEMP asselect * from T_TEMP此時視圖建立成功。5. 複雜視圖的建立與使用所謂複雜視圖，就是視圖的 SQL 陳述式中，有彙總函式或多表關聯查詢。我們看下面的例子：（1）多表關聯查詢的例子需求：建立視圖，查詢顯示業主編號，業主名稱，業主類型名稱語句：

create or replace view view_owners as
select 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;`這次我們會發現，系統彈出錯誤提示： ![](http://i2.51cto.com/images/blog/201806/04/af92c44c167c173b198846a5e1d0633d.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)這個是什麼意思？是說我們所需改的列不屬於鍵保留表的列。什麼叫鍵保留表呢？鍵保留表是理解串連視圖修改限制的一個基本概念。該表的主鍵列全部顯示在視圖中,並且它們的值在視圖中都是唯一且非空的。也就是說，表的索引值在一個串連視圖中也是索引值，那麼就稱這個表為鍵保留表。在我們這個例子中，視圖中存在兩個表，業主表（T_OWNERS）和業主類型表（T_OWNERTYPE）, 其中 T_OWNERS 表就是鍵保留表，因為 T_OWNERS 的主鍵也是作為視圖的主鍵。鍵保留表的欄位是可以更新的，而非鍵保留表是不能更新的。（2）分組彙總統計查詢的例子需求：建立視圖，按年月統計水費金額，效果如下 ![](http://i2.51cto.com/images/blog/201806/04/f41d427032a8aa433e1ee0bd20b8e270.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)語句：

create view view_accountsum as
select year,month,sum(money) moneysum
from T_ACCOUNT
group by year,month
order by year,month

此例用到彙總函式，沒有鍵保留表，所以無法執行 update 。 ![](http://i2.51cto.com/images/blog/201806/04/62eaff589cdb415edd2117d15c819424.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

Oracle-day03 上