視圖(view)，也稱虛表, 不占用物理空間，這個也是相對概念，因為視圖本身的定義語句還是要存儲在數(shù)據(jù)字典里的。視圖只有邏輯定義。每次使用的時候,只是重新執(zhí)行SQL。
視圖是從一個或多個實際表中獲得的，這些表的數(shù)據(jù)存放在數(shù)據(jù)庫中。那些用于產(chǎn)生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中產(chǎn)生。
視圖的定義存在數(shù)據(jù)庫中，與此定義相關(guān)的數(shù)據(jù)并沒有再存一份于數(shù)據(jù)庫中。通過視圖看到的數(shù)據(jù)存放在基表中。
視圖看上去非常象數(shù)據(jù)庫的物理表，對它的操作同任何其它的表一樣。當(dāng)通過視圖修改數(shù)據(jù)時，實際上是在改變基表中的數(shù)據(jù)；相反地，基表數(shù)據(jù)的改變也會自動反映在由基表產(chǎn)生的視圖中。由于邏輯上的原因，有些Oracle視圖可以修改對應(yīng)的基表，有些則不能（僅僅能查詢）。
還有一種視圖：物化視圖（MATERIALIZED VIEW ），也稱實體化視圖，快照 （8i 以前的說法） ，它是含有數(shù)據(jù)的，占用存儲空間。
tips: 查詢視圖沒有什么限制, 插入/更新/刪除視圖的操作會受到一定的限制;所有針對視圖的操作都會影響到視圖的基表; 為了防止用戶通過視圖間接修改基表的數(shù)據(jù),可以將視圖創(chuàng)建為只讀視圖(帶上with read only選項)
二. 視圖的作用
1)提供各種數(shù)據(jù)表現(xiàn)形式, 可以使用各種不同的方式將基表的數(shù)據(jù)展現(xiàn)在用戶面前, 以便符合用戶的使用習(xí)慣(主要手段: 使用別名)；
2)隱藏數(shù)據(jù)的邏輯復(fù)雜性并簡化查詢語句, 多表查詢語句一般是比較復(fù)雜的, 而且用戶需要了解表之間的關(guān)系, 否則容易寫錯; 如果基于這樣的查詢語句創(chuàng)建一個視圖, 用戶就可以直接對這個視圖進行"簡單查詢"而獲得結(jié)果. 這樣就隱藏了數(shù)據(jù)的復(fù)雜性并簡化了查詢語句.這也是oracle提供各種"數(shù)據(jù)字典視圖"的原因之一,all_constraints就是一個含有2個子查詢并連接了9個表的視圖(在catalog.sql中定義)；
3)執(zhí)行某些必須使用視圖的查詢. 某些查詢必須借助視圖的幫助才能完成. 比如, 有些查詢需要連接一個分組統(tǒng)計后的表和另一表, 這時就可以先基于分組統(tǒng)計的結(jié)果創(chuàng)建一個視圖, 然后在查詢中連接這個視圖和另一個表就可以了；
4)提供某些安全性保證. 視圖提供了一種可以控制的方式, 即可以讓不同的用戶看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感數(shù)據(jù)不被用戶看見；
5)簡化用戶權(quán)限的管理. 可以將視圖的權(quán)限授予用戶, 而不必將基表中某些列的權(quán)限授予用戶, 這樣就簡化了用戶權(quán)限的定義。
三 創(chuàng)建視圖
1權(quán)限: 要在當(dāng)前方案中創(chuàng)建視圖, 用戶必須具有create view系統(tǒng)權(quán)限; 要在其他方案中創(chuàng)建視圖, 用戶必須具有create any view系統(tǒng)權(quán)限. 視圖的功能取決于視圖擁有者的權(quán)限.
2 語法: create [ or replace ] [ force ] view [schema.]view_name [ (column1,column2,...) ] as select ... [ with check option ] [ constraint constraint_name ] [ with read only ];
tips: 1 or replace: 如果存在同名的視圖, 則使用新視圖"替代"已有的視圖 2 force: "強制"創(chuàng)建視圖,不考慮基表是否存在,也不考慮是否具有使用基表的權(quán)限 3 column1,column2,...：視圖的列名, 列名的個數(shù)必須與select查詢中列的個數(shù)相同; 如果select查詢包含函數(shù)或表達式, 則必須為其定義列名.此時, 既可以用column1, column2指定列名, 也可以在select查詢中指定列名. 4 with check option: 指定對視圖執(zhí)行的dml操作必須滿足“視圖子查詢”的條件即,對通過視圖進行的增刪改操作進行"檢查",要求增刪改操作的數(shù)據(jù), 必須是select查詢所能查詢到的數(shù)據(jù),否則不允許操作并返回錯誤提示. 默認情況下, 在增刪改之前"并不會檢查"這些行是否能被select查詢檢索到. 5 with read only：創(chuàng)建的視圖只能用于查詢數(shù)據(jù), 而不能用于更改數(shù)據(jù).
3.1 創(chuàng)建簡單視圖
簡單視圖定義：是指基于單個表建立的，不包含任何函數(shù)、表達式和分組數(shù)據(jù)的視圖。
SQL> conn /as sysdba
Connected.
SQL> grant create view to scott;
SQL> conn scott/tiger
Connected.
SQL> create view vw_emp as select empno,ename,job,hiredate,deptno from emp;
SQL> desc vw_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
HIREDATE DATE
DEPTNO NUMBER(2)
SQL> select * from vw_emp where deptno=10;
EMPNO ENAME JOB HIREDATE DEPTNO
---------- ---------- --------- ---------
7782 CLARK MANAGER 09-JUN-81 10
7839 KING PRESIDENT 17-NOV-81 10
7934 MILLER CLERK 23-JAN-82 10
對簡單視圖進行DML操作
SQL> insert into vw_emp values(1,'a','aa','05-JUN-88',10);
SQL> update vw_emp set ename='cc' where ename='KING';
SQL> delete vw_emp where ename='cc';
SQL> select * from vw_emp where deptno=10;
EMPNO ENAME JOB HIREDATE DEPTNO
---------- ---------- --------- --------- ----------
7782 CLARK MANAGER 09-JUN-81 10
7934 MILLER CLERK 23-JAN-82 10
1 a aa 05-JUN-88 10
基表也發(fā)生了相應(yīng)的更改
SQL> select empno,ename,job,hiredate,deptno from emp where deptno=10;
EMPNO ENAME JOB HIREDATE DEPTNO
---------- ---------- --------- --------- ----------
7782 CLARK MANAGER 09-JUN-81 10
7934 MILLER CLERK 23-JAN-82 10
1 a aa 05-JUN-88 10
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
VW_EMP VIEW
SALGRADE TABLE
BONUS TABLE
PK_EMP INDEX
EMP TABLE
DEPT TABLE
PK_DEPT INDEX
7 rows selected.
創(chuàng)建只讀視圖
SQL> create view vw_emp_readonly as select empno,ename,job,hiredate,deptno from empwith read only;
SQL> select * from vw_emp_readonly where deptno=10;
EMPNO ENAME JOB HIREDATE DEPTNO
---------- ---------- --------- --------- ----------
7782 CLARK MANAGER 09-JUN-81 10
7934 MILLER CLERK 23-JAN-82 10
1 a aa 05-JUN-88 10
只能查詢，無法進行更改
SQL> delete vw_emp_readonly where empno=1;
delete vw_emp_readonly where empno=1
*
ERROR at line 1:
ORA-42399: cannot perform a DML Operation on a read-only view
更新基表，只讀視圖也發(fā)生了相應(yīng)的更改
SQL> update emp set empno=2 where ename='a';
SQL> select * from vw_emp_readonly where deptno=10;
EMPNO ENAME JOB HIREDATE DEPTNO
---------- ---------- --------- --------- ----------
7782 CLARK MANAGER 09-JUN-81 10
7934 MILLER CLERK 23-JAN-82 10
2 a aa 05-JUN-88 10
創(chuàng)建檢查約束視圖with check option
Sql>create view vw_emp_check as select empno,ename,job,hiredate,deptno from emp where deptno=10 with check option;
SQL> insert into vw_emp_check values('2','c','cc','02-JAN-55',10);
SQL> insert into vw_emp_check values('3','d','dd','02-JAN-65',20);
insert into vw_emp_check values('3','d','dd','02-JAN-65',20)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
創(chuàng)建檢查視圖：對通過視圖進行的增刪改操作進行檢查，要求增刪改操作的數(shù)據(jù)必須是select查詢所能查詢到的數(shù)據(jù)
20號部門不在查詢范圍內(nèi)，違反檢查約束,所以無法插入；
SQL> delete vw_emp_check where empno=2;
1 row deleted.
--------所刪除的數(shù)據(jù)在查詢范圍內(nèi)，不違反檢查約束
3.2 連接視圖
3.2.1 連接視圖定義：是指基于多個表所創(chuàng)建的視圖，即，定義視圖的查詢是一個連接查詢。 主要目的是為了簡化連接查詢；
3.2.2 創(chuàng)建連接視圖
示例1： 查詢部門編號為10和30的部門及雇員信息
SQL> create view vw_dept_emp
2 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);
View created.
SQL> select * from vw_dept_emp;
DEPTNO DNAME LOC EMPNO ENAME SAL
---------- -------------- ------------- ---------- ---------- ----------
30 SALES CHICAGO 7499 ALLEN 1600
30 SALES CHICAGO 7521 WARD 1250
30 SALES CHICAGO 7654 MARTIN 1250
30 SALES CHICAGO 7698 BLAKE 2850
10 ACCOUNTING NEW YORK 7782 CLARK 2450
30 SALES CHICAGO 7844 TURNER 1500
30 SALES CHICAGO 7900 JAMES 950
10 ACCOUNTING NEW YORK 7934 MILLER 1300
10 ACCOUNTING NEW YORK 1 a
9 rows selected.
3.2.3 連接視圖上的DML操作
SQL> insert into vw_dept_emp values(10,'aaa','aaaa',22,'a',5000);
insert into vw_dept_emp values(10,'aaa','aaaa',22,'a',5000)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
在視圖上進行的所有DML操作，最終都會在基表上完成；select 視圖沒有什么限制，但insert/delete/update有一些限制；
3.2.4鍵值保存表
如果連接視圖中的一個“基表的鍵”(主鍵、唯一鍵)在它的視圖中仍然存在，并且“基表的鍵”仍然是“連接視圖中的鍵”(主鍵、唯一鍵)；即，某列在基表中是主鍵|唯一鍵，在視圖中仍然是主鍵|唯一鍵，則稱這個基表為“鍵值保存表”。 一般地，由主外鍵關(guān)系的2個表組成的連接視圖，外鍵表就是鍵值保存表，而主鍵表不是。
3.2.5 連接視圖的更新準(zhǔn)則
一：一般準(zhǔn)則——(講) 1任何DML操作，只能對視圖中的鍵值保存表進行更新, 即，“不能通過連接視圖修 改多個基表”; 2在DML操作中，“只能使用連接視圖定義過的列”; 3“自連接視圖”的所有列都是可更新(增刪改)的 二：insert準(zhǔn)則 1 在insert語句中不能使用“非鍵值保存表”中的列(包括“連接列”)； 2 執(zhí)行insert操作的視圖，至少應(yīng)該“包含”鍵值保存表中所有設(shè)置了約束的列； 3 如果在定義連接視圖時使用了WITH CHECK OPTION 選項，則“不能”針對連接視 圖執(zhí)行insert操作 三：update準(zhǔn)則 1鍵值保存表中的列是可以更新的； 2如果在定義連接視圖時使用了WITH CHECK OPTION 選項，則連接視圖中的連接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，連接列和共有列之外的 其他列是“可以”更新的 四：delete準(zhǔn)則 1如果在定義連接視圖時使用了WITH CHECK OPTION 選項，依然“可以”針對連接視圖執(zhí)行delete操作
3.2.6 可更新連接視圖
如果創(chuàng)建連接視圖的select查詢“不包含”如下結(jié)構(gòu)，并且遵守連接視圖的“更新準(zhǔn)則”，則這樣的連接視圖是“可更新”的： 一：集合運算符(union,intersect,minus) 二：DISTINCT關(guān)鍵字 三：GROUP BY，ORDER BY，CONNECT BY或START WITH子句 四：子查詢 五：分組函數(shù) 六：需要更新的列不是由“列表達式”定義的 七：基表中所有NOT NULL列均屬于該視圖
3.3 創(chuàng)建復(fù)雜視圖
復(fù)雜視圖定義：是指包含函數(shù)、表達式、或分組數(shù)據(jù)的視圖。主要目的是為了簡化查詢。主要用于執(zhí)行查詢操作，并不用于執(zhí)行DML操作。 注意：當(dāng)視圖的select查詢中包含函數(shù)或表達式時，必須為其定義列別名。
示例1：查詢目前每個崗位的平均工資、工資總和、最高工資和最低工資。
SQL> create view vw_emp_job_sal(job,avgsal,sumsal,maxsal,minsal)
2 as select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;
View created.
SQL> select * from vw_emp_job_sal;
JOB AVGSAL SUMSAL MAXSAL MINSAL
--------- ---------- ---------- ---------- ----------
CLERK 1037.5 4150 1300 800
SALESMAN 1400 5600 1600 1250
aa
MANAGER 2758.33333 8275 2975 2450
ANALYST 3000 6000 3000 3000
3.4 強制創(chuàng)建視圖
強制視圖定義：正常情況下，如果基表不存在，創(chuàng)建視圖就會失敗。但是可以使用force選項強制創(chuàng)建視圖(前提：創(chuàng)建視圖的語句沒有語法錯誤！)，此時該視圖處于失效狀態(tài)。
SQL> create force view vw_test_tab
2 as select c1,c2 from test_tab;
Warning: View created with compilation errors. 警告: 創(chuàng)建的視圖帶有編譯錯誤。
SQL> select * from vw_test_tab;
select * from vw_test_tab
*
ERROR at line 1:
ORA-04063: view "SCOTT.VW_TEST_TAB" has errors
SQL> select object_name,status from user_objects where object_name='VW_TEST_TAB';
OBJECT_NAME STATUS
------------------------------ -------
VW_TEST_TAB INVALID ---------視圖狀態(tài)為不可用
SQL> create table test_tab(c1 number(9) primary key,c2 varchar2(20),c3 varchar2(30));
Table created.
SQL> select * from vw_test_tab;
no rows selected
SQL> select object_name,status from user_objects where object_name='VW_TEST_TAB';
OBJECT_NAME STATUS
------------------------------ -------
VW_TEST_TAB VALID --------------視圖狀態(tài)為可用
四 更改視圖
在對視圖進行更改(或重定義)之前，需要考慮如下幾個問題：1由于視圖只是一個虛表，其中沒有數(shù)據(jù)，所以更改視圖只是改變數(shù)據(jù)字典中對該視圖的 定義信息，視圖的所有基礎(chǔ)對象都不會受到任何影響2更改視圖之后，依賴于該視圖的所有視圖和PL/SQL程序都將變?yōu)镮NVALID(失效)狀態(tài)3如果以前的視圖中具有with check option選項，但是重定義時沒有使用該選項，則以前
的此選項將自動刪除。
4.1更改視圖的定義 方法——執(zhí)行create or replace view語句。這種方法代替了先刪除(“權(quán)限也將隨之刪除”)后創(chuàng)建的方法，會保留視圖上的權(quán)限，但與該視圖相關(guān)的存儲過程和視圖會失效。
示例1：
將視圖改為改為只讀
SQL> create or replace view vw_emp
2 as
3 select empno,ename,job,hiredate,deptno from emp with read only;
View created.
4.2視圖的重新編譯 語法：alter view 視圖名 compile; 作用：當(dāng)視圖依賴的基表改變后，視圖會“失效”。為了確保這種改變“不影響”視圖和依賴于該視圖的其他對象，應(yīng)該使用 alter view 語句“明確的重新編譯”該視圖，從而在運行視圖前發(fā)現(xiàn)重新編譯的錯誤。視圖被重新編譯后，若發(fā)現(xiàn)錯誤，則依賴該視圖的對象也會失效；若沒有錯誤，視圖會變?yōu)椤坝行А薄?nbsp; 權(quán)限：為了重新編譯其他模式中的視圖，必須擁有alter any table系統(tǒng)權(quán)限。 注意：當(dāng)訪問基表改變后的視圖時，oracle會“自動重新編譯”這些視圖。
示例1：
SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';
LAST_DDL_ OBJECT_NAME STATUS
--------- ------------------------------ -------
23-AUG-14 VW_TEST_TAB VALID -----視圖的狀態(tài):有效
SQL> alter table test_tab modify(c2 varchar2(30)); ——修改基表，c2列的長度
SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';
LAST_DDL_ OBJECT_NAME STATUS
--------- ------------------------------ -------
23-AUG-14 VW_TEST_TAB INVALID ——視圖的狀態(tài):失效
SQL> alter view vw_test_tab compile; ---重新編譯
SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';
LAST_DDL_ OBJECT_NAME STATUS
--------- ------------------------------ -------
23-AUG-14 VW_TEST_TAB VALID ---視圖有效
思考：若上述代碼修改的不是列長，而是表名，結(jié)果又會如何？ <警告：更改的視圖帶有編譯錯誤；視圖狀態(tài)：失效>
五 刪除視圖
可以刪除當(dāng)前模式中的任何視圖； 如果要刪除其他模式中的視圖，必須擁有DROP ANY VIEW系統(tǒng)權(quán)限； 視圖被刪除后，該視圖的定義會從詞典中被刪除，并且在該視圖上授予的“權(quán)限”也將被刪除。 視圖被刪除后，其他引用該視圖的視圖及存儲過程等都會失效。 示例1：drop view vw_test_tab;
六 查看視圖
使用數(shù)據(jù)字典視圖1 dba_views——DBA視圖描述數(shù)據(jù)庫中的所有視圖2 all_views——ALL視圖描述用戶“可訪問的”視圖3 user_views——USER視圖描述“用戶擁有的”視圖 4 dba_tab_columns——DBA視圖描述數(shù)據(jù)庫中的所有視圖的列(或表的列)5 all_tab_columns——ALL視圖描述用戶“可訪問的”視圖的列(或表的列)6 user_tab_columns——USER視圖描述“用戶擁有的”視圖的列(或表的列) 示例1：查詢當(dāng)前方案中所有視圖的信息
SQL> select view_name,text from user_views;
VIEW_NAME TEXT
------------------ -------------------------------------------------------
VW_DEPT_EMP select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal fro
m dept a,emp b where a.de
VW_EMP select empno,ename,job,hiredate,deptno from emp
VW_EMP_CHECK select empno,ename,job,hiredate,deptno from emp where d
eptno=10 with check optio
VW_EMP_JOB_SAL select job,avg(sal),sum(sal),max(sal),min(sal) from emp
group by job
VW_EMP_READONLY select empno,ename,job,hiredate,deptno from emp with re
ad only
VW_TEST_TAB select c1,c2 from test_tab
6 rows selected.
示例2：查詢當(dāng)前方案中指定視圖(或表)的列名信息 select * from user_tab_columns where table_name='VW_DEPT';
七 在視圖上執(zhí)行DML操作的步驟和原理
第一步：將針對視圖的SQL語句與視圖的定義語句(保存在數(shù)據(jù)字典中)“合并”成一條SQL語句
第二步：在內(nèi)存結(jié)構(gòu)的共享SQL區(qū)中“解析”(并優(yōu)化)合并后的SQL語
第三步：“執(zhí)行”SQL語句示例：假設(shè)視圖v_emp的定義語句如下：create view v_emp asselect empno,ename,loc from employees emp,departments deptwhere
emp.deptno=dept.deptno and dept.deptno=10; 當(dāng)用戶執(zhí)行如下查詢語句時：select ename from v_emp where empno=9876; oracle將把這條SQL語句與視圖定義語句“合并”成如下查詢語句：select ename from employees emp,departments deptwhere
emp.deptno=dept.deptno and dept.deptno=10 and empno=9876; 然后，解析(并優(yōu)化)合并后的查詢語句，并執(zhí)行查詢語句;
7.1查詢視圖“可更新”(包括“增刪改”)的列
使用數(shù)據(jù)字典視圖
dba_updatable_columns——顯示數(shù)據(jù)庫所有視圖中的所有列的可更新狀態(tài)
all_updatable_columns——顯示用戶可訪問的視圖中的所有列的可更新狀態(tài)
user_updatable_columns——顯示用戶擁有的視圖中的所有列的可更新狀態(tài) 示例1：
SQL> select table_name,column_name,insertable,updatable,deletable from user_updatable_columns;
TABLE_NAME COLUMN_NAME INS UPD DEL
------------------------------ ------------------------------ --- --- ---
VW_EMP_READONLY EMPNO NO NO NO
VW_EMP_READONLY ENAME NO NO NO
VW_EMP_READONLY JOB NO NO NO
VW_EMP_READONLY HIREDATE NO NO NO
VW_EMP_READONLY DEPTNO NO NO NO
VW_EMP_CHECK EMPNO YES YES YES
VW_EMP_CHECK ENAME YES YES YES
VW_EMP_CHECK JOB YES YES YES
VW_EMP_CHECK HIREDATE YES YES YES
VW_EMP_CHECK DEPTNO YES YES YES
VW_DEPT_EMP DEPTNO NO NO NO
VW_DEPT_EMP DNAME NO NO NO
VW_DEPT_EMP LOC NO NO NO
VW_DEPT_EMP EMPNO YES YES YES
VW_DEPT_EMP ENAME YES YES YES
VW_DEPT_EMP SAL YES YES YES
VW_EMP_JOB_SAL JOB NO NO NO
VW_EMP_JOB_SAL AVGSAL NO NO NO
VW_EMP_JOB_SAL SUMSAL NO NO NO
VW_EMP_JOB_SAL MAXSAL NO NO NO
VW_EMP_JOB_SAL MINSAL NO NO NO
VW_TEST_TAB C1 YES YES YES
VW_TEST_TAB C2 YES YES YES
VW_EMP EMPNO YES YES YES
VW_EMP ENAME YES YES YES
VW_EMP JOB YES YES YES
VW_EMP HIREDATE YES YES YES
VW_EMP DEPTNO YES YES YES
SALGRADE GRADE YES YES YES
SALGRADE LOSAL YES YES YES
SALGRADE HISAL YES YES YES
EMP EMPNO YES YES YES
EMP ENAME YES YES YES
EMP JOB YES YES YES
EMP MGR YES YES YES
EMP HIREDATE YES YES YES
EMP SAL YES YES YES
EMP COMM YES YES YES
EMP DEPTNO YES YES YES
TEST_TAB C1 YES YES YES
TEST_TAB C2 YES YES YES
TEST_TAB C3 YES YES YES
BONUS ENAME YES YES YES
BONUS JOB YES YES YES
BONUS SAL YES YES YES
BONUS COMM YES YES YES
DEPT DEPTNO YES YES YES
DEPT DNAME YES YES YES
DEPT LOC YES YES YES
49 rows selected.
新聞熱點
疑難解答
圖片精選
