Oracle DB 視圖

• 什麼是視圖

通過建立表的視圖可以顯示資料的邏輯子集或組合。視圖是一種 基于表或其它視圖的邏輯 表。視圖沒有自己的資料，但它如同一個視窗，通過它可以檢視或更改表中的資料。視圖 所基于的表被稱為基表。視圖以SELECT 語句的形式存儲在資料字典中。

• 視圖的優點

• 由于視圖可以顯示表中的標明列，因而可以 限制對資料的通路。 • 視圖可用來通過進行簡單查詢來檢索複雜查詢的結果。例如，使用者在不了解如何編寫 聯接語句時，使用視圖就可以查詢多個表中的資訊。 • 視圖為特定使用者和特定應用程式 提供了資料獨立性。 一個視圖可用來檢索多個表中的 資料。 • 通過視圖，使用者組可根據各自的特定标準通路資料。

• 簡單視圖和複雜視圖

視圖有兩種分類：簡單視圖和複雜視圖。這兩類視圖的基本差别與DML（INSERT 、 UPDATE 和DELETE ）操作有關。 • 簡單視圖有如下特點： - 隻從一個表中獲得資料 - 不包含函數或資料組 - 可以通過視圖執行DML 操作 • 複雜視圖有如下特點： - 從多個表中獲得資料 - 包含函數或資料組 - 不一定允許通過視圖執行DML 操作

• 建立視圖

• 在CREATE VIEW 語句中嵌入一個子查詢： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias [,  alias ]...)] AS subquery [WITH CHECK O PTION [CONSTRAINT  constraint ]] [WITH READ ON LY [CONSTRAINT  constraint ]];

• 子查詢可以包含複雜的SELECT 文法。

可以通過在CREATE VIEW 語句中嵌入子查詢來建立視圖。 在該文法中： OR REPLACE ：如果該視圖已存在則重新建立 FORCE ：不管基表是否存在都建立視圖 NOFORCE ：僅當基表存在時才建立視圖（預設設定） view ：是視圖的名稱 alias ：指定由視圖查詢標明的表達式的名稱（别名的數量必須與視圖選擇 表達式數量比對） subquery ：是一個完整的SELECT 語句（可以在SELECT 清單中使用列的别名 WITH CHECK OPTION： 指定隻插入或隻更新視圖中可以通路的那些行 constraint： 是為CHECK OPTION限制條件指定的名稱 WITH READ ONLY ：確定不對此視圖執行DML 操作

• 建立視圖EMPVU80，視圖包含部門80 中每位雇員的雇員編号、姓氏和薪金 ： [email protected]> create view empvu80   2  as select EMPLOYEE_ID,LAST_NAME,SALARY from employees where EMPLOYEE_ID=80;

View created.

• 使用SQL*Plus DESCRIBE 指令描述視圖的結構： DESCRIBE empvu80 [email protected]> desc empvu80  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  EMPLOYEE_ID                                           NOT NULL NUMBER(6)  LAST_NAME                                             NOT NULL VARCHAR2(25)  SALARY                                                           NUMBER(8,2)

•  在子查詢中使用列别名建立視圖， 可以通過将列别名包含在子查詢中來控制列名 ： [email protected]> create view salvu50   2  as select EMPLOYEE_ID id_number,LAST_NAME name,SALARY*12 ann_salary from employees where EMPLOYEE_ID=50;

View created.

[email protected]> desc salvu50  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  ID_NUMBER                                             NOT NULL NUMBER(6)  NAME                                                  NOT NULL VARCHAR2(25)  ANN_SALARY                                                     NUMBER 示例中建立了一個視圖，其中包含部門50 中每位雇員的以下資訊：别名為 ID_NUMBER 的雇員編号( EMPLOYEE_ID ) 、别名為NAME 的姓名( LAST_NAME)  以及别名 為ANN_SALARY的年薪( SALARY ) 。

你也可以在CREATE 語句之後和SELECT 子查詢之前使用别名。列出的别名數量必須與 在子查詢中標明的表達式數量相比對。 [email protected]> CREATE OR REPLACE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)   2  AS SELECT  employee_id, last_name, salary*12 FROM    employees WHERE   department_id = 50;

View created.

[email protected]> desc salvu50  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  ID_NUMBER                                             NOT NULL NUMBER(6)  NAME                                                  NOT NULL VARCHAR2(25)  ANN_SALARY                                                     NUMBER

• 準則

• 定義視圖的子查詢可以包含複雜的SELECT 文法，其中包括聯接、分組和子查詢。 • 如果你沒有為使用WITH CHECK OPTION建立的視圖指定限制條件名稱，系統則 會以SYS_Cn 格式指定預設名稱。 • 可以使用OR REPLACE選項更改視圖的定義，而不必在删除該視圖後再重新建立， 也不必對其重新授予以前授予過的對象權限。

• 從視圖中檢索資料

你可以從視圖中檢索資料，就像從任何表中檢索資料一樣。可以顯示整個視圖的内容， 也可以隻顯示特定的行或列。 [email protected]> select * from salvu50;

 ID_NUMBER NAME                      ANN_SALARY ---------- ------------------------- ----------        198 OConnell                       31200        199 Grant                          31200 ... 45 rows selected.

• 修改視圖

• 可以使用CREATE OR REPLACE VIEW子句修改視圖 EMPVU80。為每個列名添加一個别名： [email protected]> CREATE OR REPLACE VIEW empvu80(id_number, name,  sal, department_id)   2  AS SELECT  employee_id, first_name || ' '|| last_name, salary, department_id FROM    employees WHERE   department_id = 80;

View created.

CREATE OR REPLACE VIEW子句中列出的列别名與 子查詢中的列具有相同的順序。 使用OR REPLACE選項，可以建立一個視圖，甚至可以建立一個與已存在的視圖同名的 視圖，以便替換舊版本的視圖。這意味着可以更改視圖，而不必經過删除、重新建立對象 和重新授予對象權限的過程。 注：在CREATE OR REPLACE VIEW子句中指定列别名時，請注意别名的列出順序應與 子查詢中列的順序相同。

• 建立複雜視圖

建立包含組函數的複雜視圖以顯示兩個表中的值： [email protected]> create or replace view dept_sum_vu(name,minsal,maxsal,avgsal)   2  as select d.department_name,min(e.salary),max(e.salary),avg(e.salary) from employees e join departments d   3  on (e.department_id=d.department_id) group by d.department_name   4  /

View created.

示例中建立了一個複雜視圖，其中包含按部門列出的部門名稱、最低薪金、最高 薪金和平均薪金。請注意，已為該視圖指定了替代名稱。如果視圖中有任何列來自于函數 或表達式，則需要使用替代名稱。 可以使用DESCRIBE 指令檢視視圖的結構。通過發出SELECT 語句可顯示視圖的内容。

[email protected]> select * from dept_sum_vu;

NAME                               MINSAL     MAXSAL     AVGSAL ------------------------------ ---------- ---------- ---------- Administration                       4400       4400       4400 Accounting                           8300      12008      10154 ... 11 rows selected.

• 對視圖執行DML 操作的規則

• 可以對整個視圖中的資料執行DML 操作，但這些操作必須符合特定的規則, 通常可以對簡單視圖執行DML 操作 。 • 如果視圖不包含以下任何内容，則可以從視圖中删除行： - 組函數 - GROUP BY 子句 - DISTINCT 關鍵字 - 僞列ROWNUM 關鍵字

• 對視圖執行DML 操作的規則修改資料

如果視圖包含以下内容，則不能修改視圖中的資料： • 組函數 • GROUP BY 子句 • DISTINCT 關鍵字 • 僞列ROWNUM 關鍵字 • 由表達式定義的列 可以修改視圖中的資料，除非該視圖包含上規則中提到的任何條件或由表達式定義 的列（例如，SALARY * 12 ）。

• 對視圖執行DML 操作的規則插入資料

如果視圖包括以下内容，則不能向視圖添加資料： • 組函數 • GROUP BY 子句 • DISTINCT 關鍵字 • 僞列ROWNUM 關鍵字 • 由表達式定義的列 • 基表中未被視圖選中的NOT NULL 列

可以向視圖添加資料，除非視圖包含此上列中列出的任何項。如果視圖包含的NOT  NULL列在基表中沒有指定預設值，則不能向視圖添加資料。在視圖中必須顯示所有需要 的值。請記住，您要通過視圖将值直接添加到基表中。

• 使用WITH CHECK OPTION子句

• 使用WITH CHECK OPTION子句可確定對視圖執行的 DML 操作隻在視圖範圍内起作用： [email protected]> create or replace view empvu20   2  as select * from employees where department_id=20   3  with check option constraint empvu20_ck   4  /

View created. • 如果嘗試使用INSERT語句插入department_id 不為20 的一行，或者使用UPDATE語句更新視圖中 任何行的部門編号，則操作會失敗，因為這違反WITH  CHECK OPTION限制條件。 可以在視圖中執行引用完整性檢查。還可以在資料庫級别強制實作限制條件。視圖可以用 來保護資料完整性，但此用途很有限。 WITH CHECK OPTION子句指定通過視圖執行的INSERT和UPDATE不能建立該視圖無 法選擇的行。是以，能夠對要插入或更新的資料強制執行完整性限制條件和資料驗證檢查。 如果嘗試對視圖未選中的行執行DML 操作，則會顯示一條錯誤，還會顯示限制條件名稱 （如果已指定）。 [email protected]> update empvu20 set department_id=10 where employee_id=201; update empvu20 set department_id=10 where employee_id=201        * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

注：如果部門編号已更改為10，則該視圖将無法看到此雇員，是以不會更新任何行。 是以，使用WITH CHECK OPTION子句時，該視圖隻能看到部門20 中的雇員，并且 不允許通過該視圖更改這些雇員的部門編号。

• 拒絕DML 操作

• 通過在視圖定義中添加WITH READ ONLY選項可以 確定不會執行DML 操作。 • 嘗試對視圖中的任何行執行DML 操作都會導緻産生 Oracle Server 錯誤。

[email protected]> create or replace view empvu10(employee_number,employee_name,job_title)   2  as select employee_id,last_name,job_id from employees where department_id=10   3  with read only   4  /

View created. 嘗試從具有隻讀限制條件的視圖中删除任何行會導緻産生錯誤： [email protected]> delete from empvu10 where employee_number=200; delete from empvu10 where employee_number=200             * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view

與此類似，嘗試使用具有隻讀限制條件的視圖插入行或修改行會導緻同樣的錯誤。

• 删除視圖

因為視圖是基于資料庫中的基表建立的，是以删除視圖不會 導緻丢失資料。 [email protected]> drop view empvu80;

View dropped. 使用DROP VIEW語句可删除視圖。該語句會從資料庫中删除視圖定義。但是，删除視圖 不會影響視圖的基表。此外，基于已删除視圖的視圖或其它應用程式會變得無效。隻有 建立者或具有DROP ANY VIEW權限的使用者才能删除視圖。