oracle檢視基本操作

lygle發表於2014-05-12
Oracle
1.1 概念
       檢視-----是由SELECT查詢語句(可以是單表或者多表查詢)定義的一個"邏輯表",只有定義而無資料,是一個"虛表". 在建立檢視時,只是將檢視的定義資訊儲存在資料字典中, 而並不將實際的資料複製到任何地方, 即不需要在表空間中為檢視分配儲存空間. 檢視是檢視和操縱基表資料的一種方法, 可以像使用表一樣使用檢視.
        tips: 查詢檢視沒有什麼限制, 插入/更新/刪除檢視的操作會受到一定的限制; 所有針對檢視的操作都會影響到檢視的基表; 為了防止使用者通過檢視間接修改基表的資料, 可以將檢視建立為只讀檢視(帶上with read only選項). 
檢視中的資料會隨基表的更新而自動更新.
        檢視猶如基表的一個"視窗", 通過這個"視窗", 可以實施許多管理. 在一個檢視中可以定義的最大列數為1000, 與表的限制相同.
        檢視約束: 允許在檢視上生成約束, 如"主鍵約束、唯一鍵約束、外來鍵約束、檢查約束"等. 但檢視上的約束不是強制性的, 而是宣告性的. 檢視約束的語法與表相同. 在建立檢視時, 可以使用with check option選項,給檢視定義check約束,使其只能查詢、操作滿足check約束的記錄行.
1.2  作用
        1)提供各種資料表現形式, 可以使用各種不同的方式將基表的資料展現在使用者面前, 以便符合使用者的使用習慣(主要手段: 使用別名).
        2)隱藏資料的邏輯複雜性並簡化查詢語句, 多表查詢語句一般是比較複雜的, 而且使用者需要了解表之間的關係, 否則容易寫錯; 如果基於這樣的查詢語句建立一個檢視, 使用者就可以直接對這個檢視進行"簡單查詢"而獲得結果. 這樣就隱藏了資料的複雜性並簡化了查詢語句. 這也是oracle提供各種"資料字典檢視"的原因之一,all_constraints就是一個含有2個子查詢並連線了9個表的檢視(在catalog.sql中定義).
        3)執行某些必須使用檢視的查詢. 某些查詢必須藉助檢視的幫助才能完成. 比如, 有些查詢需要連線一個分組統計後的表和另一表, 這時就可以先基於分組統計的結果建立一個檢視, 然後在查詢中連線這個檢視和另一個表就可以了.
        4)提供某些安全性保證. 檢視提供了一種可以控制的方式, 即可以讓不同的使用者看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感資料不被使用者看見.
        5)簡化使用者許可權的管理. 可以將檢視的許可權授予使用者, 而不必將基表中某些列的許可權授予使用者, 這樣就簡化了使用者許可權的定義.
1.3  建立檢視
        許可權: 要在當前方案中建立檢視, 使用者必須具有create view系統許可權; 要在其他方案中建立檢視, 使用者必須具有create any view系統許可權. 檢視的功能取決於檢視擁有者的許可權.
        語法: 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: "強制"建立檢視,不考慮基表是否存在,也不考慮是否具有使用基表的許可權
        column1,column2,...：檢視的列名, 列名的個數必須與select查詢中列的個數相同; 如果select查詢包含函式或表示式, 則必須為其定義列名.  此時, 既可以用column1, column2指定列名, 也可以在select查詢中指定列名.
        with check option: 指定對檢視執行的dml操作必須滿足“檢視子查詢”的條件即,對通過檢視進行的增刪改操作進行"檢查",要求增刪改操作的資料, 必須是select查詢所能查詢到的資料,  否則不允許操作並返回錯誤提示. 預設情況下, 在增刪改之前"並不會檢查"這些行是否能被select查詢檢索到. 
        with read only：建立的檢視只能用於查詢資料, 而不能用於更改資料.
1.3.1 建立簡單檢視
        是指基於單個表建立的，不包含任何函式、表示式和分組資料的檢視。
         示例1：基於emp表建立一個vw_emp檢視
         create view vw_emp
         as
         select  empno,ename,job,hiredate,deptno from emp; --------建立簡單檢視
         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 ; --------建立只讀檢視，只能用於執行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 ;
         --------建立檢查檢視：對通過檢視進行的增刪改操作進行檢查，
                                       要求增刪改操作的資料必須是select查詢所能查詢到的資料。           
         insert into vw_emp_check
         values(1235,'JACK','CLERK','29-4月-1963',20);
         --------20號部門不在查詢範圍內，違反檢查約束
         delete from vw_emp_check  where empno=1234;
         --------所刪除的資料在查詢範圍內，不違反檢查約束
1.3.2 建立連線檢視
        是指基於多個表所建立的檢視，即，定義檢視的查詢是一個連線查詢。 主要目的是為了簡化連線查詢
        示例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;
  
1.3.3 建立複雜檢視
        是指包含函式、表示式、或分組資料的檢視。主要目的是為了簡化查詢。 主要用於執行查詢操作，並不用於執行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.3.4 強制建立檢視
        正常情況下，如果基表不存在，建立檢視就會失敗。但是可以使用force選項強制建立檢視(前提：建立檢視的語句沒有語法錯誤！)，此時該檢視處於失效狀態。
        示例1：
              create force view  vw_test_tab
              as
              select c1,c2 from test_tab;--------會出現“警告: 建立的檢視帶有編譯錯誤。”
              select object_name,status from  user_objects
              where  object_name='VW_TEST_TAB';--------檢視狀態：INVALID
              select  *  from vw_test_tab;--------報錯
              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';--------檢視狀態：VALID               
1.4 更改檢視
          在對檢視進行更改(或重定義)之前，需要考慮如下幾個問題：
          之一——由於檢視只是一個虛表，其中沒有資料，所以更改檢視只是改變資料字典中對該檢視的
                       定義資訊，檢視的所有基礎物件都不會受到任何影響
          之二——更改檢視之後，依賴於該檢視的所有檢視和PL/SQL程式都將變為INVALID(失效)狀態
          之三——如果以前的檢視中具有with check option選項，但是重定義時沒有使用該選項，
                       則以前的此選項將自動刪除。
1.4.1 更改檢視的定義
          方法——執行create or replace view語句。這種方法代替了先刪除(“許可權也將隨之刪除”)
                       後建立的方法，會保留檢視上的許可權，但與該檢視相關的儲存過程和檢視會失效。
          示例1：create or replace view v_test_tab
                      as
                      select c1,c2||' + '||c3  c23 from test_tab;
   
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='V_TEST_TAB';——檢視的狀態:有效
                       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';——檢視的狀態:失效
                       alter view v_test_tab compile;——明確的重新編譯
                       select last_ddl_time,object_name,status
                       from    user_objects
                       where  object_name='V_TEST_TAB';——檢視的狀態:有效
            思考：若上述程式碼修改的不是列長，而是表名，結果又會如何？
                     
                                           
1.5 刪除檢視
        可以刪除當前模式中的任何檢視；
        如果要刪除其他模式中的檢視，必須擁有DROP ANY VIEW系統許可權；
        檢視被刪除後，該檢視的定義會從詞典中被刪除，並且在該檢視上授予的“許可權”也將被刪除。
        檢視被刪除後，其他引用該檢視的檢視及儲存過程等都會失效。
       示例1：drop view vw_test_tab;
1.6 檢視檢視
        使用資料字典檢視
         dba_views——DBA檢視描述資料庫中的所有檢視
         all_views——ALL檢視描述使用者“可訪問的”檢視
         user_views——USER檢視描述“使用者擁有的”檢視
         dba_tab_columns——DBA檢視描述資料庫中的所有檢視的列(或表的列)
         all_tab_columns——ALL檢視描述使用者“可訪問的”檢視的列(或表的列)
         user_tab_columns——USER檢視描述“使用者擁有的”檢視的列(或表的列)
         示例1：查詢當前方案中所有檢視的資訊
         desc user_views;
         set long 400;
         select view_name,text  from user_views;
         示例1：查詢當前方案中指定檢視(或表)的列名資訊
         select * from user_tab_columns where table_name='VW_DEPT';
1.7 在連線檢視上執行DML操作
         在檢視上進行的所有DML操作，最終都會在基表上完成；
         select 檢視沒有什麼限制，但insert/delete/update有一些限制
1.7.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;
                      當使用者執行如下查詢語句時：
                      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;
        然後，解析(並優化)合併後的查詢語句，並執行查詢語句
1.7.2 查詢檢視“可更新”(包括“增刪改”)的列
        使用資料字典檢視
        dba_updatable_columns——顯示資料庫所有檢視中的所有列的可更新狀態
        all_updatable_columns——顯示使用者可訪問的檢視中的所有列的可更新狀態                                    
        user_updatable_columns——顯示使用者擁有的檢視中的所有列的可更新狀態
        示例1： 查詢v_stu_dept中的哪些列是可更新的
              select table_name,column_name,insertable,updatable,deletable
              from    user_updatable_columns
              where table_name='V_STU_DEPT';
1.7.3 可更新連線檢視
       如果建立連線檢視的select查詢“不包含”如下結構，
       並且遵守連線檢視的“更新準則”，
       則這樣的連線檢視是“可更新”的：
       之一：集合運算子(union,intersect,minus)
       之二：DISTINCT關鍵字
       之三：GROUP BY，ORDER BY，CONNECT BY或START WITH子句
       之四：子查詢
       之五：分組函式
       之六：需要更新的列不是由“列表示式”定義的
       之七：基表中所有NOT NULL列均屬於該檢視
1.7.4 鍵值儲存表
        如果連線檢視中的一個“基表的鍵”(主鍵、唯一鍵)在它的檢視中仍然存在，
        並且“基表的鍵”仍然是“連線檢視中的鍵”(主鍵、唯一鍵)；
        即，某列在基表中是主鍵|唯一鍵，在檢視中仍然是主鍵|唯一鍵
        則稱這個基表為“鍵值儲存表”。
              
         一般地，由主外來鍵關係的2個表組成的連線檢視，外來鍵表就是鍵值儲存表，而主鍵表不是。 
1.7.5 連線檢視的更新準則
        之一：一般準則——(講)
                        任何DML操作，只能對檢視中的鍵值儲存表進行更新,
                        即，“不能通過連線檢視修改多個基表”;
                        在DML操作中，“只能使用連線檢視定義過的列”;
                        “自連線檢視”的所有列都是可更新(增刪改)的
        之二：insert準則
                       在insert語句中不能使用“非鍵值儲存表”中的列(包括“連線列”)；
                       執行insert操作的檢視，至少應該“包含”鍵值儲存表中所有設定了約束的列；
                       如果在定義連線檢視時使用了WITH CHECK OPTION 選項，
                       則“不能”針對連線檢視執行insert操作                                     
       之三：update準則
                        鍵值儲存表中的列是可以更新的；
                        如果在定義連線檢視時使用了WITH CHECK OPTION 選項，
                        則連線檢視中的連線列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，
                        連線列和共有列之外的其他列是“可以”更新的
      之四：delete準則
                        如果在定義連線檢視時使用了WITH CHECK OPTION 選項，
                        依然“可以”針對連線檢視執行delete操作

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-1159273/，如需轉載，請註明出處，否則將追究法律責任。

相關文章