程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 深入Oracle視圖編寫的示例

深入Oracle視圖編寫的示例

編輯:Oracle數據庫基礎

本章重點介紹Oracle視圖基本操作,包括創建連接視圖,復雜視圖,強制創建視圖,更改視圖,視圖重新編譯等。

創建Oracle視圖

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

  1. create [ or replace ] [ force ] view [schema.]vIEw_name  
  2.                       [ (column1,column2,...) ]  
  3.                       as   
  4.                       select ...  
  5.                       [ with check option ] [ constraint constraint_name ]  
  6.                       [ with read only ];  
  7.         tips:  
  8.         or replace: 如果存在同名的視圖, 則使用新視圖"替代"已有的視圖  
  9.         force: "強制"創建視圖,不考慮基表是否存在,也不考慮是否具有使用基表的權限  
  10.         column1,column2,...:視圖的列名, 

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

創建簡單視圖

是指基於單個表建立的,不包含任何函數、表達式和分組數據的視圖。
示例1:基於emp表創建一個vw_emp視圖 

  1. create vIEw vw_emp  
  2.        as  
  3.        select  empno,ename,job,hiredate,deptno from emp; --------創建簡單視圖  
  4.        desc vw_emp; --------象表一樣使用  
  5.        select * from vw_emp  where  deptno=10;--------查詢  
  6.        insert into vw_emp values(1234,'JACK','CLERK','29-4月-1963',10);--------增加  
  7.        update vw_emp set ename='劉德華' where ename='JACK';--------更新  
  8.        delete vw_emp where ename='劉德華'; --------刪除  
  9.          
  10.        create vIEw vw_emp_readonly  
  11.        as  
  12.        select  empno,ename,job,hiredate,deptno from emp  
  13.        with read only ; --------創建只讀視圖,只能用於執行select語句  
  14.        delete from vw_emp_readonly where empno=1234;--------刪除失敗  
  15.        create vIEw vw_emp_check  
  16.        as  
  17.        select  empno,ename,job,hiredate,deptno   
  18.        from emp where deptno=10 
  19.        with check option constraint vw_emp_chk ;  
  20.        --------創建檢查視圖:對通過視圖進行的增刪改操作進行檢查,  
  21.                                      要求增刪改操作的數據必須是select查詢所能查詢到的數據。             
  22.        insert into vw_emp_check  
  23.        values(1235,'JACK','CLERK','29-4月-1963',20);  
  24.        --------20號部門不在查詢范圍內,違反檢查約束  
  25.        delete from vw_emp_check  where empno=1234;  
  26.        --------所刪除的數據在查詢范圍內,不違反檢查約束  
  27. 3.2 創建連接視圖  
  28.       是指基於多個表所創建的視圖,即,定義視圖的查詢是一個連接查詢。 主要目的是為了簡化連接查詢  
  29.       示例1: 查詢部門編號為10和30的部門及雇員信息  
  30.             create vIEw vw_dept_emp  
  31.             as  
  32.             select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal  
  33.             from dept a , emp b  
  34.             where a.deptno=b.deptno   
  35.             and  a.deptno in (10,30);   
  36.             select * from vw_dept_emp;  

創建復雜Oracle視圖

是指包含函數、表達式、或分組數據的視圖。主要目的是為了簡化查詢。 主要用於執行查詢操作,並不用於執行DML操作。
注意:當視圖的select查詢中包含函數或表達式時,必須為其定義列別名。
示例1:查詢目前每個崗位的平均工資、工資總和、最高工資和最低工資。          

  1. create vIEw vw_emp_job_sal  
  2. (job,avgsal,sumsal,maxsal,minsal)  
  3. as  
  4. select job,avg(sal),sum(sal),max(sal),min(sal)  
  5. from emp  
  6. group by job;  
  7. select * from vw_emp_job_sal;  

強制創建Oracle視圖

正常情況下,如果基表不存在,創建視圖就會失敗。但是可以使用force選項強制創建視圖(前提:創建視圖的語句沒有語法錯誤!),此時該視圖處於失效狀態。
示例1:       

  1. create force vIEw  vw_test_tab  
  2. as  
  3. select c1,c2 from test_tab;--------會出現“警告: 創建的視圖帶有編譯錯誤。”  
  4. select object_name,status from  user_objects  
  5. where  object_name='VW_TEST_TAB';--------視圖狀態:INVALID  
  6. select  *  from vw_test_tab;--------報錯  
  7. create table test_tab--------先建表  
  8. (c1 number(9) primary key, c2  varchar2(20),c3 varchar2(30));  
  9. select  *  from vw_test_tab;--------自動編譯失效的視圖  
  10. select object_name,status from  user_objects  
  11. where  object_name='VW_TEST_TAB';--------視圖狀態:VALID    
        

更改Oracle視圖

在對視圖進行更改(或重定義)之前,需要考慮如下幾個問題:
之一——由於視圖只是一個虛表,其中沒有數據,所以更改視圖只是改變數據字典中對該視圖的定義信息,視圖的所有基礎對象都不會受到任何影響
之二——更改視圖之後,依賴於該視圖的所有視圖和PL/SQL程序都將變為INVALID(失效)狀態
之三——如果以前的視圖中具有with check option選項,但是重定義時沒有使用該選項,
則以前的此選項將自動刪除.

更改Oracle視圖的定義

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

  1. create or replace vIEw v_test_tab  
  2. as  
  3. select c1,c2||' + '||c3  c23 from test_tab;  

視圖的重新編譯

語法:alter vIEw 視圖名 compile;
作用:當視圖依賴的基表改變後,視圖會“失效”。為了確保這種改變“不影響”視圖和依賴於該視圖的其他對象,應該使用 alter vIEw 語句“明確的重新編譯”該視圖,從而在運行視圖前發現重新編譯的錯誤。視圖被重新編譯後,若發現錯誤,則依賴該視圖的對象也會失效;若沒有錯誤,視圖會變為“有效”。
權限:為了重新編譯其他模式中的視圖,必須擁有alter any table系統權限。
注意:當訪問基表改變後的視圖時,Oracle會“自動重新編譯”這些視圖。
示例1:

  1. select last_ddl_time,object_name,status  
  2. from  user_objects  
  3. where  object_name='V_TEST_TAB';——視圖的狀態:有效  
  4. alter table test_tab  modify (c2  varchar2(30));——修改c2列的長度  
  5. select last_ddl_time,object_name,status  
  6. from  user_objects  
  7. where  object_name='V_TEST_TAB';——視圖的狀態:失效  
  8. alter vIEw v_test_tab compile;——明確的重新編譯  
  9. select last_ddl_time,object_name,status  
  10. from    user_objects  
  11. where  object_name='V_TEST_TAB';——視圖的狀態:有效  

思考:若上述代碼修改的不是列長,而是表名,結果又會如何?
<警告:更改的視圖帶有編譯錯誤;視圖狀態:失效>

刪除視圖

可以刪除當前模式中的任何視圖;如果要刪除其他模式中的視圖,必須擁有DROP ANY VIEW系統權限;視圖被刪除後,該視圖的定義會從詞典中被刪除,並且在該視圖上授予的“權限”也將被刪除。視圖被刪除後,其他引用該視圖的視圖及存儲過程等都會失效。
示例1:drop vIEw vw_test_tab;

查看視,使用數據字典視圖     

  1. dba_vIEws——DBA視圖描述數據庫中的所有視圖  
  2. all_vIEws——ALL視圖描述用戶“可訪問的”視圖  
  3. user_vIEws——USER視圖描述“用戶擁有的”視圖  
  4. dba_tab_columns——DBA視圖描述數據庫中的所有視圖的列(或表的列)  
  5. all_tab_columns——ALL視圖描述用戶“可訪問的”視圖的列(或表的列)  
  6. user_tab_columns——USER視圖描述“用戶擁有的”視圖的列(或表的列)  
  7.          示例1:查詢當前方案中所有視圖的信息  
  8. desc user_vIEws;  
  9. set long 400;  
  10. select view_name,text  from user_vIEws;  
  11. 示例1:查詢當前方案中指定視圖(或表)的列名信息  
  12. select * from user_tab_columns where table_name='VW_DEPT';  

在連接視圖上執行DML操作

在視圖上進行的所有DML操作,最終都會在基表上完成;select 視圖沒有什麼限制,但insert/delete/update有一些限制

在視圖上執行DML操作的步驟和原理
第一步:將針對視圖的SQL語句與視圖的定義語句(保存在數據字典中)“合並”成一條SQL語句
第二步:在內存結構的共享SQL區中“解析”(並優化)合並後的SQL語句
第三步:“執行”SQL語句
示例:假設視圖v_emp的定義語句如下:                  

  1. create vIEw v_emp   
  2. as  
  3. select empno,ename,loc  
  4. from employees emp,departments dept  
  5. where emp.deptno=dept.deptno and dept.deptno=10;  
  6. 當用戶執行如下查詢語句時:  
  7. select ename  from  v_emp  
  8. where empno=9876;  
  9. Oracle將把這條SQL語句與視圖定義語句“合並”成如下查詢語句:  
  10. select  ename    
  11. from employees emp,departments dept  
  12. where emp.deptno=dept.deptno  and dept.deptno=10 
  13. and empno=9876;  

然後,解析(並優化)合並後的查詢語句,並執行查詢語句

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

  1. dba_updatable_columns——顯示數據庫所有視圖中的所有列的可更新狀態  
  2. all_updatable_columns——顯示用戶可訪問的視圖中的所有列的可更新狀態                                      
  3. user_updatable_columns——顯示用戶擁有的視圖中的所有列的可更新狀態  
  4. 示例1: 查詢v_stu_dept中的哪些列是可更新的  
  5. select table_name,column_name,insertable,updatable,deletable  
  6. from    user_updatable_columns  
  7. where table_name='V_STU_DEPT';  

可更新連接視圖

如果創建連接視圖的select查詢“不包含”如下結構,並且遵守連接視圖的“更新准則”,
則這樣的連接視圖是“可更新”的:
之一:集合運算符(union,intersect,minus)
之二:DISTINCT關鍵字
之三:GROUP BY,ORDER BY,CONNECT BY或START WITH子句
之四:子查詢
之五:分組函數
之六:需要更新的列不是由“列表達式”定義的
之七:基表中所有NOT NULL列均屬於該視圖

鍵值保存表

如果連接視圖中的一個“基表的鍵”(主鍵、唯一鍵)在它的視圖中仍然存在,並且“基表的鍵”仍然是“連接視圖中的鍵”(主鍵、唯一鍵);即,某列在基表中是主鍵|唯一鍵,在視圖中仍然是主鍵|唯一鍵則稱這個基表為“鍵值保存表”。一般地,由主外鍵關系的2個表組成的連接視圖,外鍵表就是鍵值保存表,而主鍵表不是。 

連接視圖的更新准則

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

之二:insert准則

在insert語句中不能使用“非鍵值保存表”中的列(包括“連接列”);執行insert操作的視圖,至少應該“包含”鍵值保存表中所有設置了約束的列;如果在定義連接視圖時使用了WITH CHECK OPTION 選項,則“不能”針對連接視圖執行insert操作                                    
之三:update准則

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

之四:delete准則

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved