程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習筆記11--視圖(VIEW)

Oracle學習筆記11--視圖(VIEW)

編輯：Oracle教程

本次必須學習一個全新的概念-- 視圖 (VIEW)。在前面的筆記中曾提到過，數據對象包括：表、視圖、序列、索引和同義詞。前面的筆記都是對表的想剖析，那麼本次筆記就對視圖的世界進行深入的剖析。

視圖是通過對一個表或者是多個表查詢得到的，是從表中抽出的邏輯上相關的數據集合。但是視圖是一種虛表，它建立在已經存在表的基礎之上，也可以理解為表中導出的“表”。視圖定義所依據的表稱為基表，有點類似與JAVA中的父類(基類)。

利用視圖可以控制對數據的訪問，簡化查詢，可以避免直接對數據庫中真實存在的表進行直接訪問，提高數據庫的安全性。

創建視圖

創建視圖語法格式：

CREATE [OR ERPLACE ] VIEW view_name

as 子查詢 [WITH READ ONLY] | [WITH CHECK OPTION]；

子查詢可以是復雜的語句，如多個表之間的連接，子查詢可以使用單行函數。可以通過給子查詢中的列定義別名

來為視圖的列定義成想要的名字。

例：創建視圖，依賴emp表。

SQL> create view emp_view
2 as select empno ,ename ,sal from emp ;

View created

在用soctt用戶執行上述語句的時候，可能會提示，沒有創建視圖的權限。可用下面的操作解決。

Oracle - OraClient10g_home1 --> Oracle EnterPrise Manager Console -->連接身份DBA

-->安全性-->用戶-->scott --> 雙擊-->系統 --> 添加create any view 權限-->確定

現在對視圖進行查詢。

SQL> select * from emp_view ;

EMPNO ENAME SAL
----- ---------- ---------
7369 SMITH 800.00
7499 ALLEN 1600.00
7521 WARD 1250.00
7566 JONES 2975.00
7654 MARTIN 1250.00
7698 BLAKE 2850.00
7782 CLARK 2450.00
7788 SCOTT 3000.00
7839 KING 5000.00
7844 TURNER 1500.00
7876 ADAMS 1100.00
7900 JAMES 950.00
7902 FORD 3000.00
7934 MILLER 1300.00

14 rows selected

emp_view視圖定義之後，在此創建emp_view 視圖時，會提示名稱已經由現有對象使用，說明視圖的名稱不

允許重復。如果想依然使用emp_view視圖，可以使用：

create or replace view emp_view as select empno ,ename ,sal from emp 。雖然已經創

建了emp_view語句，仍然可以創建成功。系統會刪除emp_view視圖，在創建一個新的視圖。對視圖仍然可以

進行相關的插入更新操作，對視圖的操作都會返回到基表中，和直接對表的操作效果一樣。

例：對視圖進行插入刪除操作。將ename為JAMES的工資改為2000 ，插入一條新的數據。

SQL> update emp_view set sal = 2000 where ename = 'JAMES' ;

SQL> insert into emp_view values(7935 , 'zhangsan' , 5000);

下面看一下操作結果：

SQL> select * from emp_view where ename = 'JAMES' ;

EMPNO ENAME SAL
----- ---------- ---------
7900 JAMES 2000.00

SQL> select * from emp where ename = 'JAMES' ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7900 JAMES CLERK 7698 1981/12/3 2000.00 30

SQL> select * from emp_view where ename='zhangsan' ;

EMPNO ENAME SAL
----- ---------- ---------
7935 zhangsan 5000.00

SQL> select * from emp where ename='zhangsan';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7935 zhangsan 5000.00

上面使用DML語句進行了插入和更新操作。但是視圖使用DML時還有一些特別的規定：

當視圖定義中包含以下元素時，不能使用delete：

組函數、GROUP BY 、DISTINST 和 ROWNUM 偽列。

當視圖定義中包含以下元素時，不能使用update：

組函數、GROUP BY 、DISTINST 、 ROWNUM 偽列和列的定義表達式。

當視圖定義中包含以下元素時，不能使用insert：

組函數、GROUP BY 、DISTINST 、 ROWNUM 偽列、列的定義表達式和表中非空的列在視圖中未包括。

如果創建的視圖只是為了讓人浏覽的內容而不能修改或者插入新的數據的話，那麼需要在創建視圖的時候添加限制(with read only) 只讀的。

create or replace view emp_view
as select empno ,ename ,sal from emp
with read only

此時在執行update語句 update emp_view set sal = 2000 where ename = 'JAMES'，會出現
ORA-01733: 此處不允許虛擬列提示。

with check option ：不能更新視圖的創建條件。此處不在贅述。

前面在執行多表查詢的時候，有這樣的一條語句，將emp表和dept表進行聯立。

select empno ,ename , dname ,e.deptno
from emp e , dept d
where d.deptno = e.deptno

EMPNO ENAME DNAME DEPTNO
----- ---------- -------------- ------
7369 SMITH RESEARCH 20
7499 ALLEN SALES 30
7521 WARD SALES 30
7566 JONES RESEARCH 20
7654 MARTIN SALES 30
7698 BLAKE SALES 30
7782 CLARK ACCOUNTING 10
7788 SCOTT RESEARCH 20
7839 KING ACCOUNTING 10
7844 TURNER SALES 30
7876 ADAMS RESEARCH 20
7900 JAMES SALES 30
7902 FORD RESEARCH 20
7934 MILLER ACCOUNTING 10

14 rows selected

如果在開發的過程中，要多次使用上面的多表連接的語句，那麼肯定是非常麻煩的。所以此時可以將上面多表查

詢的結果建立成一個試圖。再次使用的時候就輕松多了。

create view view_emp
as
select empno ,ename , dname ,e.deptno
from emp e , dept d
where d.deptno = e.deptno

當執行select * from view_emp ;語句時，與上面的多表查詢的效果是一樣的。使用試圖可以簡化操作。

刪除視圖

基本語法：

DROP VIEW view_name ;

刪除視圖只是刪除用戶自己創建的視圖，不會對基表中的數據產生影響。

例：刪除上面定義的視圖emp_view 。

drop view emp_name ;

Rownum 偽列

上面提到了一個新的知識點rownum 偽劣，下面進行具體的解釋一下。rownum 有點類似與excel中的表示行

的序號。實際上這是一個列，偽劣，可以在每張表中出現。

例：查詢表emp中的rownum 。

SQL> select rownum , empno from emp ;

ROWNUM EMPNO
---------- -----
1 7369
2 7499
3 7521
4 7566
5 7654
6 7698
7 7782
8 7788
9 7839
10 7844
11 7876
12 7900
13 7902
14 7934
15 7935

15 rows selected

如果現在想要查詢工資從高到低的前五條的數據，那麼

SQL> select rownum , empno,sal from emp where rownum <= 5 order by sal desc;

ROWNUM EMPNO SAL
---------- ----- ---------
1 7935 5000.00
5 7566 2975.00
3 7499 1600.00
4 7521 1250.00
2 7369 800.00

觀察上面的數據，雖然是按照sal從高到低的排序，但是卻不是我們想高的最高的五條數據的排序。其實rownum和empno之間有某種關系，想要打破這中關系，可以使用下面的語句。

select rownum , empno,sal
from(
select rownum , empno,sal
from emp order by sal desc
)
where rownum <= 5

ROWNUM EMPNO SAL
---------- ----- ---------
1 7935 5000.00
2 7839 5000.00
3 7788 3000.00
4 7902 3000.00
5 7566 2975.00

上面的查詢結果才是我們想要的結果。

如果我們想要查詢3到8之間的數據呢？可以使用下面的數據

select rn ,empno ,sal from(
select rownum rn , empno,sal
from(
select rownum , empno,sal
from emp order by sal desc
)
)
where rn >=3 and rn <=8

RN EMPNO SAL
------ ----- ---------
3 7902 3000.00
4 7788 3000.00
5 7566 2975.00
6 7698 2850.00
7 7782 2450.00
8 7900 2000.00

6 rows selected