Oracle 檢視詳解

一. 檢視的定義

檢視(view)，也稱虛表, 不佔用物理空間，這個也是相對概念，因為檢視本身的定義語句還是要儲存在資料字典裡的。檢視只有邏輯定義。每次使用的時候,只是重新執行SQL。

檢視是從一個或多個實際表中獲得的，這些表的資料存放在資料庫中。那些用於產生檢視的表叫做該檢視的基表。一個檢視也可以從另一個檢視中產生。

檢視的定義存在資料庫中，與此定義相關的資料並沒有再存一份於資料庫中。透過檢視看到的資料存放在基表中。

檢視看上去非常象資料庫的物理表，對它的操作同任何其它的表一樣。當透過檢視修改資料時，實際上是在改變基表中的資料；相反地，基表資料的改變也會自動反映在由基表產生的檢視中。由於邏輯上的原因，有些Oracle檢視可以修改對應的基表，有些則不能（僅僅能查詢）。

還有一種檢視：物化檢視（MATERIALIZED VIEW ），也稱實體化檢視，快照（8i 以前的說法），它是含有資料的，佔用儲存空間。

tips: 查詢檢視沒有什麼限制, 插入/更新/刪除檢視的操作會受到一定的限制; 所有針對檢視的操作都會影響到檢視的基表; 為了防止使用者透過檢視間接修改基表的資料, 可以將檢視建立為只讀檢視(帶上with read only選項)

二. 檢視的作用

1)提供各種資料表現形式, 可以使用各種不同的方式將基表的資料展現在使用者面前, 以便符合使用者的使用習慣(主要手段: 使用別名)；

2)隱藏資料的邏輯複雜性並簡化查詢語句, 多表查詢語句一般是比較複雜的, 而且使用者需要了解表之間的關係, 否則容易寫錯; 如果基於這樣的查詢語句建立一個檢視, 使用者就可以直接對這個檢視進行"簡單查詢"而獲得結果. 這樣就隱藏了資料的複雜性並簡化了查詢語句.這也是oracle提供各種"資料字典檢視"的原因之一,all_constraints就是一個含有2個子查詢並連線了9個表的檢視(在catalog.sql中定義)；

3)執行某些必須使用檢視的查詢. 某些查詢必須藉助檢視的幫助才能完成. 比如, 有些查詢需要連線一個分組統計後的表和另一表, 這時就可以先基於分組統計的結果建立一個檢視, 然後在查詢中連線這個檢視和另一個表就可以了；

4)提供某些安全性保證. 檢視提供了一種可以控制的方式, 即可以讓不同的使用者看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感資料不被使用者看見；

5)簡化使用者許可權的管理. 可以將檢視的許可權授予使用者, 而不必將基表中某些列的許可權授予使用者, 這樣就簡化了使用者許可權的定義。

三建立檢視

1 許可權: 要在當前方案中建立檢視, 使用者必須具有create view系統許可權; 要在其他方案中建立檢視, 使用者必須具有create any view系統許可權. 檢視的功能取決於檢視擁有者的許可權.

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: " 強制"建立檢視,不考慮基表是否存在,也不考慮是否具有使用基表的許可權
3 column1,column2,... ：檢視的列名, 列名的個數必須與select查詢中列的個數相同; 如果select查詢包含函式或表示式, 則必須為其定義列名.此時, 既可以用column1, column2指定列名, 也可以在select查詢中指定列名.
4 with check option: 指定對檢視執行的dml操作必須滿足“檢視子查詢”的條件即,對透過檢視進行的增刪改操作進行"檢查",要求增刪改操作的資料, 必須是 select查詢所能查詢到的資料,否則不允許操作並返回錯誤提示. 預設情況下, 在增刪改之前"並不會檢查"這些行是否能被select查詢檢索到.
5 with read only ： 建立的檢視只能用於查詢資料, 而不能用於更改資料.

3.1 建立簡單檢視

簡單檢視定義：是指基於單個表建立的，不包含任何函式、表示式和分組資料的檢視。

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

基表也發生了相應的更改

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.

建立只讀檢視

SQL> create view vw_emp_readonly as select empno,ename,job,hiredate,deptno from emp with 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

更新基表，只讀檢視也發生了相應的更改

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

建立檢查約束檢視 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

建立檢查檢視：對透過檢視進行的增刪改操作進行檢查，要求增刪改操作的資料必須是select查詢 所能查詢到的資料

20號部門不在查詢範圍內， 違反檢查約束,所以無法插入；

SQL> delete vw_emp_check where empno=2;

1 row deleted.

--------所刪除的資料在查詢範圍內，不違反檢查約束

3.2 連線檢視

3.2.1 連線檢視定義：是指基於多個表所建立的檢視，即，定義檢視的查詢是一個連線查詢。主要目的是為了簡化連線查詢；

3.2.2 建立連線檢視

示例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 鍵值儲存表

如果連線檢視中的一個“基表的鍵”(主鍵、唯一鍵)在它的檢視中仍然存在，並且“基表的鍵”仍然是“連線檢視中的鍵”(主鍵、唯一鍵)；即，某列在基表中是主鍵|唯一鍵，在檢視中仍然是主鍵|唯一鍵，則稱這個基表為“鍵值儲存表”。
一般地，由主外來鍵關係的2個表組成的連線檢視，外來鍵表就是鍵值儲存表，而主鍵表不是。

3.2.5 連線檢視的更新準則

一：一般準則——(講)
   1任何DML操作，只能對檢視中的鍵值儲存表進行更新, 即，“不能透過連線檢視修    改多個基表”;
    2在DML操作中，“只能使用連線檢視定義過的列”;
   3“自連線檢視”的所有列都是可更新(增刪改)的
二：insert準則
    1 在insert語句中不能使用“非鍵值儲存表”中的列(包括“連線列”)；
   2 執行insert操作的檢視，至少應該“包含”鍵值儲存表中所有設定了約束的列；
   3 如果在定義連線檢視時使用了WITH CHECK OPTION 選項，則“不能”針對連線視圖執行insert操作
三：update準則
   1鍵值儲存表中的列是可以更新的；
   2如果在定義連線檢視時使用了WITH CHECK OPTION 選項，則連線檢視中的連線列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，連線列和共有列之外的其他列是“可以”更新的
四：delete準則
   1如果在定義連線檢視時使用了WITH CHECK OPTION 選項，依然“可以”針對連線檢視執行delete操作

3.2.6 可更新連線檢視

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

3.3 建立複雜檢視

複雜檢視定義：是指包含函式、表示式、或分組資料的檢視。主要目的是為了簡化查詢。主要用於執行查詢操作，並不用於執行DML操作。
注意：當檢視的select查詢中包含函式或表示式時，必須為其定義列別名。

示例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 強制建立檢視

強制檢視定義：正常情況下，如果基表不存在，建立檢視就會失敗。但是可以使用force選項強制建立檢視(前提：建立檢視的語句沒有語法錯誤！)，此時該檢視處於失效狀態。

SQL> create force view vw_test_tab

2 as select c1,c2 from test_tab;

Warning: View created with compilation errors. 警告: 建立的檢視帶有編譯錯誤。

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 --------- 檢視狀態為不可用

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 -------------- 檢視狀態為可用

四更改檢視

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

的此選項將自動刪除。

4.1 更改檢視的定義
方法——執行create or replace view語句。這種方法代替了先刪除(“許可權也將隨之刪除”)後建立的方法，會保留檢視上的許可權，但與該檢視相關的儲存過程和檢視會失效。

示例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;
   作用：當檢視依賴的基表改變後，檢視會“失效”。為了確保這種改變“不影響”檢視和依賴於該檢視的其他物件，應該使用 alter view 語句“明確的重新編譯”該檢視，從而在執行檢視前發現重新編譯的錯誤。檢視被重新編譯後，若發現錯誤，則依賴該檢視的物件也會失效；若沒有錯誤，檢視會變為“有效”。
   許可權：為了重新編譯其他模式中的檢視，必須擁有alter any table系統許可權。
   注意：當訪問基表改變後的檢視時，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 ----- 檢視的狀態:有效

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 —— 檢視的狀態:失效

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 --- 檢視有效

思考：若上述程式碼修改的不是列長，而是表名，結果又會如何？

五刪除檢視

可以刪除當前模式中的任何檢視；
   如果要刪除其他模式中的檢視，必須擁有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檢視描述“使用者擁有的”檢視的列(或表的列)
示例1：查詢當前方案中所有檢視的資訊

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：查詢當前方案中指定檢視(或表)的列名資訊
select * from user_tab_columns where table_name='VW_DEPT';

七在檢視上執行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;
然後，解析(並最佳化)合併後的查詢語句，並執行查詢語句;

7.1 查詢檢視“可更新”(包括“增刪改”)的列

使用資料字典檢視

dba_updatable_columns——顯示資料庫所有檢視中的所有列的可更新狀態

all_updatable_columns——顯示使用者可訪問的檢視中的所有列的可更新狀態

user_updatable_columns——顯示使用者擁有的檢視中的所有列的可更新狀態
示例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.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

oracle檢視詳解

oracle檢視詳解

相關文章