本章重點(diǎn)介紹Oracle視圖基本操作，包括創(chuàng)建連接視圖，復(fù)雜視圖，強(qiáng)制創(chuàng)建視圖，更改視圖，視圖重新編譯等。

創(chuàng)建Oracle視圖

權(quán)限: 要在當(dāng)前方案中創(chuàng)建視圖, 用戶必須具有create view系統(tǒng)權(quán)限; 要在其他方案中創(chuàng)建視圖, 用戶必須具有create any view系統(tǒng)權(quán)限. 視圖的功能取決于視圖擁有者的權(quán)限.
語法:

create [ or replace ] [ force ] view [schema.]view_name  
                      [ (column1,column2,...) ]  
                      as   
                      select ...  
                      [ with check option ] [ constraint constraint_name ]  
                      [ with read only ];  
        tips:  
        or replace: 如果存在同名的視圖, 則使用新視圖"替代"已有的視圖  
        force: "強(qiáng)制"創(chuàng)建視圖,不考慮基表是否存在,也不考慮是否具有使用基表的權(quán)限  
        column1,column2,...：視圖的列名, 

列名的個(gè)數(shù)必須與select查詢中列的個(gè)數(shù)相同;如果select查詢包含函數(shù)或表達(dá)式, 則必須為其定義列名.  此時(shí), 既可以用column1, column2指定列名, 也可以在select查詢中指定列名.
with check option: 指定對視圖執(zhí)行的dml操作必須滿足“視圖子查詢”的條件即,對通過視圖進(jìn)行的增刪改操作進(jìn)行"檢查",要求增刪改操作的數(shù)據(jù), 必須是select查詢所能查詢到的數(shù)據(jù),  否則不允許操作并返回錯(cuò)誤提示. 默認(rèn)情況下, 在增刪改之前"并不會檢查"這些行是否能被select查詢檢索到.
with read only：創(chuàng)建的視圖只能用于查詢數(shù)據(jù), 而不能用于更改數(shù)據(jù).

創(chuàng)建簡單視圖

是指基于單個(gè)表建立的，不包含任何函數(shù)、表達(dá)式和分組數(shù)據(jù)的視圖。
示例1：基于emp表創(chuàng)建一個(gè)vw_emp視圖 

create view vw_emp  
       as  
       select  empno,ename,job,hiredate,deptno from emp; --------創(chuàng)建簡單視圖  
       desc vw_emp; --------象表一樣使用  
       select * from vw_emp  where  deptno=10;--------查詢  
       insert into vw_emp values(1234,'JACK','CLERK','29-4月-1963',10);--------增加  
       update vw_emp set ename='劉德華' where ename='JACK';--------更新  
       delete vw_emp where ename='劉德華'; --------刪除  
         
       create view vw_emp_readonly  
       as  
       select  empno,ename,job,hiredate,deptno from emp  
       with read only ; --------創(chuàng)建只讀視圖，只能用于執(zhí)行select語句  
       delete from vw_emp_readonly where empno=1234;--------刪除失敗  
       create view vw_emp_check  
       as  
       select  empno,ename,job,hiredate,deptno   
       from emp where deptno=10 
       with check option constraint vw_emp_chk ;  
       --------創(chuàng)建檢查視圖：對通過視圖進(jìn)行的增刪改操作進(jìn)行檢查，  
                                     要求增刪改操作的數(shù)據(jù)必須是select查詢所能查詢到的數(shù)據(jù)。             
       insert into vw_emp_check  
       values(1235,'JACK','CLERK','29-4月-1963',20);  
       --------20號部門不在查詢范圍內(nèi)，違反檢查約束  
       delete from vw_emp_check  where empno=1234;  
       --------所刪除的數(shù)據(jù)在查詢范圍內(nèi)，不違反檢查約束  
3.2 創(chuàng)建連接視圖  
      是指基于多個(gè)表所創(chuàng)建的視圖，即，定義視圖的查詢是一個(gè)連接查詢。 主要目的是為了簡化連接查詢  
      示例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);   
            select * from vw_dept_emp;  

創(chuàng)建復(fù)雜Oracle視圖

是指包含函數(shù)、表達(dá)式、或分組數(shù)據(jù)的視圖。主要目的是為了簡化查詢。 主要用于執(zhí)行查詢操作，并不用于執(zhí)行DML操作。
注意：當(dāng)視圖的select查詢中包含函數(shù)或表達(dá)式時(shí)，必須為其定義列別名。
示例1：查詢目前每個(gè)崗位的平均工資、工資總和、最高工資和最低工資。          

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;  

#p#

強(qiáng)制創(chuàng)建Oracle視圖

正常情況下，如果基表不存在，創(chuàng)建視圖就會失敗。但是可以使用force選項(xiàng)強(qiáng)制創(chuàng)建視圖(前提：創(chuàng)建視圖的語句沒有語法錯(cuò)誤！)，此時(shí)該視圖處于失效狀態(tài)。
示例1：       

create force view  vw_test_tab  
as  
select c1,c2 from test_tab;--------會出現(xiàn)“警告: 創(chuàng)建的視圖帶有編譯錯(cuò)誤。”  
select object_name,status from  user_objects  
where  object_name='VW_TEST_TAB';--------視圖狀態(tài)：INVALID  
select  *  from vw_test_tab;--------報(bào)錯(cuò)  
create table test_tab--------先建表  
(c1 number(9) primary key, c2  varchar2(20),c3 varchar2(30));  
select  *  from vw_test_tab;--------自動編譯失效的視圖  
select object_name,status from  user_objects  
where  object_name='VW_TEST_TAB';--------視圖狀態(tài)：VALID    
         

更改Oracle視圖

在對視圖進(jìn)行更改(或重定義)之前，需要考慮如下幾個(gè)問題：
之一——由于視圖只是一個(gè)虛表，其中沒有數(shù)據(jù)，所以更改視圖只是改變數(shù)據(jù)字典中對該視圖的定義信息，視圖的所有基礎(chǔ)對象都不會受到任何影響
之二——更改視圖之后，依賴于該視圖的所有視圖和PL/SQL程序都將變?yōu)镮NVALID(失效)狀態(tài)
之三——如果以前的視圖中具有with check option選項(xiàng)，但是重定義時(shí)沒有使用該選項(xiàng)，
則以前的此選項(xiàng)將自動刪除.

更改Oracle視圖的定義

方法——執(zhí)行create or replace view語句。這種方法代替了先刪除(“權(quán)限也將隨之刪除”)
                       后創(chuàng)建的方法，會保留視圖上的權(quán)限，但與該視圖相關(guān)的存儲過程和視圖會失效。
示例1：

create or replace view v_test_tab  
as  
select c1,c2||' + '||c3  c23 from test_tab;  

視圖的重新編譯

語法：alter view 視圖名 compile;
作用：當(dāng)視圖依賴的基表改變后，視圖會“失效”。為了確保這種改變“不影響”視圖和依賴于該視圖的其他對象，應(yīng)該使用 alter view 語句“明確的重新編譯”該視圖，從而在運(yùn)行視圖前發(fā)現(xiàn)重新編譯的錯(cuò)誤。視圖被重新編譯后，若發(fā)現(xiàn)錯(cuò)誤，則依賴該視圖的對象也會失效；若沒有錯(cuò)誤，視圖會變?yōu)椤坝行А薄?BR>權(quán)限：為了重新編譯其他模式中的視圖，必須擁有alter any table系統(tǒng)權(quán)限。
注意：當(dāng)訪問基表改變后的視圖時(shí)，oracle會“自動重新編譯”這些視圖。
 示例1：

select last_ddl_time,object_name,status  
from  user_objects  
where  object_name='V_TEST_TAB';——視圖的狀態(tài):有效  
alter table test_tab  modify (c2  varchar2(30));——修改c2列的長度  
select last_ddl_time,object_name,status  
from  user_objects  
where  object_name='V_TEST_TAB';——視圖的狀態(tài):失效  
alter view v_test_tab compile;——明確的重新編譯  
select last_ddl_time,object_name,status  
from    user_objects  
where  object_name='V_TEST_TAB';——視圖的狀態(tài):有效  

思考：若上述代碼修改的不是列長，而是表名，結(jié)果又會如何？
<警告：更改的視圖帶有編譯錯(cuò)誤；視圖狀態(tài)：失效>#p#

刪除視圖

可以刪除當(dāng)前模式中的任何視圖；如果要?jiǎng)h除其他模式中的視圖，必須擁有DROP ANY VIEW系統(tǒng)權(quán)限；視圖被刪除后，該視圖的定義會從詞典中被刪除，并且在該視圖上授予的“權(quán)限”也將被刪除。視圖被刪除后，其他引用該視圖的視圖及存儲過程等都會失效。
示例1：drop view vw_test_tab;

查看視，使用數(shù)據(jù)字典視圖     

dba_views——DBA視圖描述數(shù)據(jù)庫中的所有視圖  
all_views——ALL視圖描述用戶“可訪問的”視圖  
user_views——USER視圖描述“用戶擁有的”視圖  
dba_tab_columns——DBA視圖描述數(shù)據(jù)庫中的所有視圖的列(或表的列)  
all_tab_columns——ALL視圖描述用戶“可訪問的”視圖的列(或表的列)  
user_tab_columns——USER視圖描述“用戶擁有的”視圖的列(或表的列)  
         示例1：查詢當(dāng)前方案中所有視圖的信息  
desc user_views;  
set long 400;  
select view_name,text  from user_views;  
示例1：查詢當(dāng)前方案中指定視圖(或表)的列名信息  
select * from user_tab_columns where table_name='VW_DEPT';  

在連接視圖上執(zhí)行DML操作

在視圖上進(jìn)行的所有DML操作，最終都會在基表上完成；select 視圖沒有什么限制，但insert/delete/update有一些限制

在視圖上執(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   
as  
select empno,ename,loc  
from employees emp,departments dept  
where emp.deptno=dept.deptno and dept.deptno=10;  
當(dāng)用戶執(zhí)行如下查詢語句時(shí)：  
select ename  from  v_emp  
where empno=9876;  
oracle將把這條SQL語句與視圖定義語句“合并”成如下查詢語句：  
select  ename    
from employees emp,departments dept  
where emp.deptno=dept.deptno  and dept.deptno=10 
and empno=9876;  

然后，解析(并優(yōu)化)合并后的查詢語句，并執(zhí)行查詢語句#p#

查詢視圖“可更新”(包括“增刪改”)的列。使用數(shù)據(jù)字典視圖

dba_updatable_columns——顯示數(shù)據(jù)庫所有視圖中的所有列的可更新狀態(tài)  
all_updatable_columns——顯示用戶可訪問的視圖中的所有列的可更新狀態(tài)                                      
user_updatable_columns——顯示用戶擁有的視圖中的所有列的可更新狀態(tài)  
示例1： 查詢v_stu_dept中的哪些列是可更新的  
select table_name,column_name,insertable,updatable,deletable  
from    user_updatable_columns  
where table_name='V_STU_DEPT';  

可更新連接視圖

如果創(chuàng)建連接視圖的select查詢“不包含”如下結(jié)構(gòu)，并且遵守連接視圖的“更新準(zhǔn)則”，
則這樣的連接視圖是“可更新”的：
之一：集合運(yùn)算符(union,intersect,minus)
之二：DISTINCT關(guān)鍵字
之三：GROUP BY，ORDER BY，CONNECT BY或START WITH子句
之四：子查詢
之五：分組函數(shù)
之六：需要更新的列不是由“列表達(dá)式”定義的
之七：基表中所有NOT NULL列均屬于該視圖

鍵值保存表

如果連接視圖中的一個(gè)“基表的鍵”(主鍵、唯一鍵)在它的視圖中仍然存在，并且“基表的鍵”仍然是“連接視圖中的鍵”(主鍵、唯一鍵)；即，某列在基表中是主鍵|唯一鍵，在視圖中仍然是主鍵|唯一鍵則稱這個(gè)基表為“鍵值保存表”。一般地，由主外鍵關(guān)系的2個(gè)表組成的連接視圖，外鍵表就是鍵值保存表，而主鍵表不是。 
 
連接視圖的更新準(zhǔn)則

之一：一般準(zhǔn)則——(講)
任何DML操作，只能對視圖中的鍵值保存表進(jìn)行更新,即，“不能通過連接視圖修改多個(gè)基表”;
在DML操作中，“只能使用連接視圖定義過的列”;“自連接視圖”的所有列都是可更新(增刪改)的

之二：insert準(zhǔn)則

在insert語句中不能使用“非鍵值保存表”中的列(包括“連接列”)；執(zhí)行insert操作的視圖，至少應(yīng)該“包含”鍵值保存表中所有設(shè)置了約束的列；如果在定義連接視圖時(shí)使用了WITH CHECK OPTION 選項(xiàng)，則“不能”針對連接視圖執(zhí)行insert操作                                    
之三：update準(zhǔn)則

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

之四：delete準(zhǔn)則

如果在定義連接視圖時(shí)使用了WITH CHECK OPTION 選項(xiàng)，
依然“可以”針對連接視圖執(zhí)行delete操作
 

【編輯推薦】

1. 修改Oracle存儲過程所需代碼
2. 對Oracle存儲過程的總結(jié)
3. 實(shí)現(xiàn)Oracle存儲過程的實(shí)際應(yīng)用的代碼 
4. 深入高性能的Oracle動態(tài)SQL開發(fā) 
5. Oracle SQL的優(yōu)化規(guī)則解析