ORACLE進階（二）視圖詳解

一. 視圖的定義

視圖(view)，也稱虛表, 不占用物理空間，這個也是相對概念，因為視圖本身的定義語句還是要存儲在數據字典里的。視圖只有邏輯定義。每次使用的時候,只是重新執行SQL。

視圖是從一個或多個實際表中獲得的，這些表的數據存放在數據庫中。那些用于產生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中產生。

視圖的定義存在數據庫中，與此定義相關的數據并沒有再存一份于數據庫中。通過視圖看到的數據存放在基表中。

視圖看上去非常象數據庫的物理表，對它的操作同任何其它的表一樣。當通過視圖修改數據時，實際上是在改變基表中的數據；相反地，基表數據的改變也會自動反映在由基表產生的視圖中。由于邏輯上的原因，有些oracle視圖可以修改對應的基表，有些則不能（僅僅能查詢）。

還有一種視圖：物化視圖（MATERIALIZED VIEW ），也稱實體化視圖，快照 （8i 以前的說法） ，它是含有數據的，占用存儲空間。

tips: 查詢視圖沒有什么限制, 插入/更新/刪除視圖的操作會受到一定的限制; 所有針對視圖的操作都會影響到視圖的基表; 為了防止用戶通過視圖間接修改基表的數據, 可以將視圖創建為只讀視圖(帶上with read only選項)

二. 視圖的作用

提供各種數據表現形式, 可以使用各種不同的方式將基表的數據展現在用戶面前, 以便符合用戶的使用習慣(主要手段: 使用別名)；

隱藏數據的邏輯復雜性并簡化查詢語句, 多表查詢語句一般是比較復雜的, 而且用戶需要了解表之間的關系, 否則容易寫錯; 如果基于這樣的查詢語句創建一個視圖, 用戶就可以直接對這個視圖進行"簡單查詢"而獲得結果. 這樣就隱藏了數據的復雜性并簡化了查詢語句.這也是oracle提供各種"數據字典視圖"的原因之一,all_constraints就是一個含有2個子查詢并連接了9個表的視圖(在catalog.sql中定義)；

執行某些必須使用視圖的查詢. 某些查詢必須借助視圖的幫助才能完成. 比如, 有些查詢需要連接一個分組統計后的表和另一表, 這時就可以先基于分組統計的結果創建一個視圖, 然后在查詢中連接這個視圖和另一個表就可以了；

提供某些安全性保證. 視圖提供了一種可以控制的方式, 即可以讓不同的用戶看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感數據不被用戶看見；

簡化用戶權限的管理. 可以將視圖的權限授予用戶, 而不必將基表中某些列的權限授予用戶, 這樣就簡化了用戶權限的定義。

三 創建視圖

權限:

要在當前方案中創建視圖, 用戶必須具有create view系統權限; 要在其他方案中創建視圖, 用戶必須具有create any view系統權限.視圖的功能取決于視圖擁有者的權限.

語法:

create [ or replace ] [ force ] view [schema.]view_name

1

[ (column1,column2,...) ] as select ... [ with check option ] [ constraint constraint_name ] [ with read only ]

1

2

3

4

5

tips:

1 or replace: 如果存在同名的視圖, 則使用新視圖"替代"已有的視圖

2 force: "強制"創建視圖,不考慮基表是否存在,也不考慮是否具有使用基表的權限

3 column1,column2,…：視圖的列名, 列名的個數必須與select查詢中列的個數相同;如果select查詢包含函數或表達式, 則必須為其定義列名.此時, 既可以用column1, column2指定列名,也可以在select查詢中指定列名.

4 with check option:

指定對視圖執行的dml操作必須滿足“視圖子查詢”的條件即,對通過視圖進行的增刪改操作進行"檢查",要求增刪改操作的數據,必須是select查詢所能查詢到的數據,否則不允許操作并返回錯誤提示. 默認情況下,在增刪改之前"并不會檢查"這些行是否能被select查詢檢索到.

5 with read only：創建的視圖只能用于查詢數據, 而不能用于更改數據.

3.1 創建簡單視圖

簡單視圖定義：是指基于單個表建立的，不包含任何函數、表達式和分組數據的視圖。

create view vw_emp as select empno,ename,job,hiredate,deptno from emp;

1

對簡單視圖進行DML操作

insert into vw_emp values(1,'a','aa','05-JUN-88',10); update vw_emp set ename='cc' where ename='KING'; delete vw_emp where ename='cc'; select * from vw_emp where deptno=10;

1

2

3

4

基表也發生了相應的更改

3.2 創建只讀視圖

create view vw_emp_readonly as select empno,ename,job,hiredate,deptno from emp with read only; select * from vw_emp_readonly where deptno=10;

1

2

只能查詢，無法進行更改

delete vw_emp_readonly where empno=1

1

ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view

1

2

更新基表，只讀視圖也發生了相應的更改

update emp set empno=2 where ename='a';

1

3.3 創建檢查約束視圖with check option

create view vw_emp_check as select empno,ename,job,hiredate,deptno from emp where deptno=10 with check option;

1

insert into vw_emp_check values('3','d','dd','02-JAN-65',20)

1

ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

1

2

創建檢查視圖：對通過視圖進行的增刪改操作進行檢查，要求增刪改操作的數據必須是select查詢所能查詢到的數據

20號部門不在查詢范圍內，違反檢查約束,所以無法插入；

delete vw_emp_check where empno=2;

1

1 row deleted.

--------所刪除的數據在查詢范圍內，不違反檢查約束

3.4 連接視圖

基于多個表所創建的視圖，即，定義視圖的查詢是一個連接查詢。 主要目的是為了簡化連接查詢；

示例1： 查詢部門編號為10和30的部門及雇員信息

create view vw_dept_emp as select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal from dept a,emp b where a.deptno=b.deptno and a.deptno in(10,30);

1

select * from vw_dept_emp;

1

insert into vw_dept_emp values(10,'aaa','aaaa',22,'a',5000)

1

ERROR at line 1: ORA-01779: cannot modify a column which maps to a non key-preserved table

1

2

在視圖上進行的所有DML操作，最終都會在基表上完成；

select 視圖沒有什么限制，但insert/delete/update有一些限制；

如果連接視圖中的一個“基表的鍵”(主鍵、唯一鍵)在它的視圖中仍然存在，并且“基表的鍵”仍然是“連接視圖中的鍵”(主鍵、唯一鍵)；即，某列在基表中是主鍵|唯一鍵，在視圖中仍然是主鍵|唯一鍵，則稱這個基表為“鍵值保存表”。

一般地，由主外鍵關系的2個表組成的連接視圖，外鍵表就是鍵值保存表，而主鍵表不是。

一：一般準則

任何DML操作，只能對視圖中的鍵值保存表進行更新, 即，“不能通過連接視圖修改多個基表”;

在DML操作中，“只能使用連接視圖定義過的列”;

“自連接視圖”的所有列都是可更新(增刪改)的

二：insert準則

在insert語句中不能使用“非鍵值保存表”中的列(包括“連接列”)；

執行insert操作的視圖，至少應該“包含”鍵值保存表中所有設置了約束的列；

如果在定義連接視圖時使用了WITH CHECK OPTION 選項，則“不能”針對連接視圖執行insert操作;

三：update準則

鍵值保存表中的列是可以更新的；

如果在定義連接視圖時使用了WITH CHECK OPTION選項，則連接視圖中的連接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，連接列和共有列之外的 其他列是“可以”更新的;

四：delete準則

如果在定義連接視圖時使用了WITH CHECK OPTION 選項，依然“可以”針對連接視圖執行delete操作;

如果創建連接視圖的select查詢“不包含”如下結構，并且遵守連接視圖的“更新準則”，則這樣的連接視圖是“可更新”的：

集合運算符(union,intersect,minus)

DISTINCT關鍵字

GROUP BY，ORDER BY，CONNECT BY或START WITH子句

子查詢

分組函數

需要更新的列不是由“列表達式”定義的

基表中所有NOT NULL列均屬于該視圖

3.5 創建復雜視圖

復雜視圖定義：是指包含函數、表達式、或分組數據的視圖。主要目的是為了簡化查詢。主要用于執行查詢操作，并不用于執行DML操作。

注意：當視圖的select查詢中包含函數或表達式時，必須為其定義列別名。

示例1：查詢目前每個崗位的平均工資、工資總和、最高工資和最低工資。

create view vw_emp_job_sal(job,avgsal,sumsal,maxsal,minsal) as select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job; select * from vw_emp_job_sal;

1

2

3

3.6 強制創建視圖

強制視圖定義：正常情況下，如果基表不存在，創建視圖就會失敗。但是可以使用force選項強制創建視圖(前提：創建視圖的語句沒有語法錯誤！)，此時該視圖處于失效狀態。

create force view vw_test_tab as select c1,c2 from test_tab;

1

Warning: View created with compilation errors. 警告: 創建的視圖帶有編譯錯誤。

1

select * from vw_test_tab

1

ERROR at line 1: ORA-04063: view "SCOTT.VW_TEST_TAB" has errors

1

2

四 更改視圖

在對視圖進行更改(或重定義)之前，需要考慮如下幾個問題：

由于視圖只是一個虛表，其中沒有數據，所以更改視圖只是改變數據字典中對該視圖的定義信息，視圖的所有基礎對象都不會受到任何影響。

更改視圖之后，依賴于該視圖的所有視圖和PL/SQL程序都將變為INVALID(失效)狀態。

如果以前的視圖中具有with check option選項，但是重定義時沒有使用該選項，則以前的此選項將自動刪除。

4.1更改視圖的定義

方法——執行create or replace view語句。這種方法代替了先刪除(“權限也將隨之刪除”)后創建的方法，會保留視圖上的權限，但與該視圖相關的存儲過程和視圖會失效。

示例1：

將視圖改為改為只讀

create or replace view vw_emp as select empno,ename,job,hiredate,deptno from emp with read only;

1

4.2視圖的重新編譯

語法：alter view 視圖名 compile;

作用：當視圖依賴的基表改變后，視圖會“失效”。為了確保這種改變“不影響”視圖和依賴于該視圖的其他對象，應該使用alter view 語句“明確的重新編譯”該視圖，從而在運行視圖前發現重新編譯的錯誤。視圖被重新編譯后，若發現錯誤，則依賴該視圖的對象也會失效；若沒有錯誤，視圖會變為“有效”。

權限：為了重新編譯其他模式中的視圖，必須擁有alter any table系統權限。

注意：當訪問基表改變后的視圖時，oracle會“自動重新編譯”這些視圖。

示例1：

select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';

1

思考：若上述代碼修改的不是列長，而是表名，結果又會如何？

<警告：更改的視圖帶有編譯錯誤；視圖狀態：失效>

五 刪除視圖

可以刪除當前模式中的任何視圖；

如果要刪除其他模式中的視圖，必須擁有DROP ANY VIEW系統權限；

視圖被刪除后，該視圖的定義會從詞典中被刪除，并且在該視圖上授予的“權限”也將被刪除。

視圖被刪除后，其他引用該視圖的視圖及存儲過程等都會失效。

示例1：drop view vw_test_tab;

六 查看視圖

使用數據字典視圖

dba_views——DBA視圖描述數據庫中的所有視圖

all_views——ALL視圖描述用戶“可訪問的”視圖

user_views——USER視圖描述“用戶擁有的”視圖

dba_tab_columns——DBA視圖描述數據庫中的所有視圖的列(或表的列)

all_tab_columns——ALL視圖描述用戶“可訪問的”視圖的列(或表的列)

user_tab_columns——USER視圖描述“用戶擁有的”視圖的列(或表的列)

示例1：查詢當前方案中所有視圖的信息

select view_name,text from user_views;

1

示例2：查詢當前方案中指定視圖(或表)的列名信息

select * from user_tab_columns where table_name='VW_DEPT';

1

七 在視圖上執行DML操作的步驟和原理

第一步：將針對視圖的SQL語句與視圖的定義語句(保存在數據字典中)“合并”成一條SQL語句；

第二步：在內存結構的共享SQL區中“解析”(并優化)合并后的SQL語句；

第三步：“執行”SQL語句；

示例：假設視圖v_emp的定義語句如下：

create view v_emp as select empno,ename,loc from employees emp,departments dept where emp.deptno=dept.deptno and dept.deptno=10;

1

當用戶執行如下查詢語句時：

select ename from v_emp where empno=9876;

1

oracle將把這條SQL語句與視圖定義語句“合并”成如下查詢語句：

select ename from employees emp,departments dept where emp.deptno=dept.deptno and dept.deptno=10 and empno=9876;

1

然后，解析(并優化)合并后的查詢語句，并執行查詢語句;

7.1查詢視圖“可更新”(包括“增刪改”)的列

使用數據字典視圖

dba_updatable_columns——顯示數據庫所有視圖中的所有列的可更新狀態

all_updatable_columns——顯示用戶可訪問的視圖中的所有列的可更新狀態

user_updatable_columns——顯示用戶擁有的視圖中的所有列的可更新狀態

示例1：

select table_name,column_name,insertable,updatable,deletable from user_updatable_columns;

1

2

Oracle SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。