oracle視圖總結)

 

視圖簡介:
視圖是基於一個表或多個表或視圖的邏輯表，本身不包含資料，通過它可以對錶裡面的資料進行查詢和修改。視圖基於的表稱為基表。視圖是儲存在資料字典裡的一條select語句。 通過建立視圖可以提取資料的邏輯上的集合或組合。

視圖的優點：
1.對資料庫的訪問，因為視圖可以有選擇性的選取資料庫裡的一部分。
2.使用者通過簡單的查詢可以從複雜查詢中得到結果。
3.維護資料的獨立性，試圖可從多個表檢索資料。
4.對於相同的資料可產生不同的視圖。

視圖的分類：
視圖分為簡單視圖和複雜視圖。

兩者區別如下：
1.簡單視圖只從單表裡擷取資料，複雜視圖從多表擷取資料；
2.簡單視圖不包含函數和資料群組，複雜視圖包含；
3.簡單視圖可以實現DML操作，複雜視圖不可以。

視圖的建立：

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
其中：
OR REPLACE：若所建立的試圖已經存在，ORACLE自動重建該視圖；
FORCE：不管基表是否存在ORACLE都會自動建立該視圖；
NOFORCE：只有基表都存在ORACLE才會建立該視圖：
alias：為視圖產生的列定義的別名；
subquery：一條完整的SELECT語句，可以在該語句中定義別名；
WITH CHECK OPTION ： 插入或修改的資料行必須滿足視圖定義的約束；
WITH READ ONLY ： 該視圖上不能進行任何DML操作。

 

DML（data manipulation language）：它們是SELECT、UPDATE、INSERT、DELETE，就象它的名字一樣，這4條命令是用來對資料庫裡的資料進行操作的語言。

DDL（data definition language）：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定義或改變表（TABLE）的結構，資料類型，
表之間的連結和約束等初始化工作上，他們大多在建立表時使用。

 

視圖的定義原則：
1.視圖的查詢可以使用複雜的SELECT文法，包括串連/分組查詢和子查詢；
2.在沒有WITH CHECK OPTION和 READ ONLY 的情況下，查詢中不能使用 ORDER BY 子句；
3.如果沒有為CHECK OPTION約束命名，系統會自動為之命名，形式為SYS_Cn;
4.OR REPLACE選項可以不刪除原視圖便可更改其定義並重建，或重新授予對象許可權。

查詢檢視：
視圖建立成功後，可以從視圖中檢索資料，這點和從表中檢索資料一樣。
樣本：
SQL>SELECT * FROM dept_sum_vw；

修改視圖：
通過OR REPLACE 重新建立同名視圖即可。

刪除視圖：
DROP VIEW VIEW_NAME語句刪除視圖。
刪除視圖的定義不影響基表中的資料。
只有視圖所有者和具備DROP VIEW許可權的使用者可以刪除視圖。
視圖被刪除後，基於被刪除視圖的其他視圖或應用將無效。

視圖上的DML 操作：
DML操作應遵循的原則：
1.簡單視圖可以執行DML操作；
2.在視圖包含GROUP 函數，GROUP BY子句，DISTINCT關鍵字時不能刪除資料行；
3.在視圖不出現下列情況時可通過視圖修改基表資料或插入資料：
a.視圖中包含GROUP 函數，GROUP BY子句，DISTINCT關鍵字；
b.使用運算式定義的列；
c.ROWNUM偽列。
d.基表中未在視圖中選擇的其他列定義為非空且無預設值。
WITH CHECK OPTION 子句
通過視圖執行的INSERTS和UPDATES操作不能建立該視圖檢索不到的資料行， 因為它會對插入或修改的資料行執行完整性條件約束和資料有效性檢查。 （也就是說在執行INSERTS、UPDATES時，WHERE條件中除需要INSERT、UPDATE本身的限制條件之外，還需要加上視圖建立時的WHERE條件。）

視圖作用
        1)提供各種資料表現形式, 可以使用各種不同的方式將基表的資料展現在使用者面前, 以便符合使用者的使用習慣(主要手段: 使用別名).
         2)隱藏資料的邏輯複雜性並簡化查詢語句, 多表查詢語句一般是比較複雜的, 而且使用者需要瞭解表之間的關係, 否則容易寫錯; 如果基於這樣的查詢語句建立一個視圖, 使用者就可以直接對這個視圖進行"簡單查詢"而獲得結果. 這樣就隱藏了資料的複雜性並簡化了查詢語句. 這也是oracle提供各種"資料字典視圖"的原因之一,all_constraints就是一個含有2個子查詢並串連了9個表的視圖(在catalog.sql中定義).
         3)執行某些必須使用視圖的查詢. 某些查詢必須藉助視圖的協助才能完成. 比如, 有些查詢需要串連一個分組統計後的表和另一表, 這時就可以先基於分組統計的結果建立一個視圖, 然後在查詢中串連這個視圖和另一個表就可以了.
        4)提供某些安全性保證. 視圖提供了一種可以控制的方式, 即可以讓不同的使用者看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感性資料不被使用者看見.
        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;

 

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

 

改視圖的定義
   方法——執行create or replace view語句。這種方法代替了先刪除(“許可權也將隨之刪除”)後建立的方法，會保留視圖上的許可權，但與該視圖相關的預存程序和視圖會失效。
   樣本1：create or replace view v_test_tab
                      as
                      select c1,c2||' + '||c3 c23 from test_tab;

 

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

 

刪除視圖
       可以刪除當前模式中的任何視圖；
        如果要刪除其他模式中的視圖，必須擁有DROP ANY VIEW系統許可權；
        視圖被刪除後，該視圖的定義會從詞典中被刪除，並且在該視圖上授予的“許可權”也將被刪除。
        視圖被刪除後，其他引用該視圖的視圖及預存程序等都會失效。
       樣本1：drop view vw_test_tab;

 

可更新串連視圖
       如果建立串連視圖的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操作