--================================

-- SQL 基礎 --> 視圖 (CREATE VIEW)

--================================

?

視圖：

??? 從表中抽出來的邏輯上相關的數據集合

??? 視圖其實就是一條查詢 SQL 語句，用于顯示一個或多個表或其它視圖中相關數據。

??? 視圖將查詢的結果作為一個表來使用，因此視圖可以被看作是存儲的查詢或一個虛擬表

??? 視圖來源于表，所有對視圖數據的修改最終都會被反映到視圖的基表中，這些修改必須服從基表的完整性約束，并同樣會觸發定義

??? 在基表上的觸發器。 ( Oracle 支持在視圖上顯式的定義觸發器和定義一些邏輯約束）

?

使用視圖的好處：

??? 可把復雜的 SQL 語句簡單化

??? 可保證數據的安全性，限制對數據的訪問，因為它對表中的一些字段是隱藏的

??? 可使相同的數據以不同形式出現在不同的視圖中

?

視圖分類：

??? 簡單視圖

?????? 只從一個表中獲取數據

?????? 不包含函數

?????? 不包含分組數據

?????? 可通過該視圖進行 DML 操作

???

??? 復雜視圖

?????? 從多個表中獲取數據

?????? 包含函數

?????? 包含分組數據

?????? 不一定能通過視圖進行 DML 操作

???

創建視圖：

??? CREATE [OR REPLACE] ［ FORCE ｜ NOFORCE ］ VIEW view_name [alias [,alias] ... ]

??? AS subquery

??? [WITH CHECK OPTION]

??? [WITH READ ONLY]

?

??? OR REPLACE ???? 如果視圖存在，重建、修改這個視圖

??? FORCE ? ?????? 不管引用的表是否存在，都創建這個視圖

??? NOFORCE ??????? 只有當表存在的時候，才能創建這個視圖（默認方式）

??? WITH CHECK OPTION 只有子查詢能夠檢索出的行才能夠被插入 , 修改 , 或刪除。默認情況下對此不作檢查

???????????????????? 沒有指定約束名，系統會自動為約束命名，形式為 SYS_Cn 。

??? WITH READ ONLY ??? ??? 只讀 , 不可對視圖做 DML 操作

???

??? 需要注意的是，在子查詢中不能包含 ORDER BY , 子查詢可以是復雜的 SELECT 語句

?

?

修改視圖

? ?? 使用 CREATE OR REPLACE VIEW 子句修改視圖

???

刪除視圖：

??? DROP VIEW view_name

?

視圖中使用 DML 的規定：

??? 當視圖定義中含有以下元素之一不能使用 INSERT

??? 組函數

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

??? 列的定義為表達式

??? 表中非空的列，在視圖定義中未包括

?

??? 視圖定義含有以下元素不能使用 UPDATE

??? 組函數

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

??? 列的定義為表達式

?

??? 視圖包含以下元素不能 DELETE

??? 組函數

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

?

?

與視圖有關的數據字典：

??? DBA ＿ VIEWS

??? USER_VIEWS

?

-- 演示創建視圖 ?

??? SQL > create or replace view vw_emp as

??? ? 2 ? select empno , ename , sal from emp where sal > 2500 ;

?

??? View created .

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ??????????? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?????? ? 7902 FORD ???????????? 3000

?

??? SQL > update vw_emp set sal = 3500 where ename = 'FORD' ; ? -- 可以更新并且直接修改了基表

?

??? 1 row updated .

?

??? SQL > select * from emp where ename = 'FORD' ;

?

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 7902 FORD ?????? ANALYST ???????? 7566 03 - DEC - 81 ?????? 3500 ??????????????????? 20

?

??? SQL > insert into vw_emp select 9999 , 'Robinson' , 6000 from dual ; ? -- 可以插入并且直接修改了基表

?

??? 1 row created .

?

??? SQL > select * from emp where empno = 9999 ; ???????????????????

?

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ????? ??? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 9999 Robinson ??????????????????????????????????????? 6000

??? ?

??? 1 rows selected .

?

??? SQL > delete from emp where ename = 'Robinson' ; -- 可以刪除并且直接修改了基表

?

??? 1 row deleted .

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ??????????? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?????? ? 7902 FORD ???????????? 3500

?

??? SQL > rollback ;

?

??? Rollback complete .

?

?

-- 使用 with check option

??? -- 使用 WITH CHECK OPTION 子句確保 DML 只能在特定的范圍內執行，任何違反

??? --WITH CHECK OPTION 約束的請求都會失敗

???

??? SQL > create or replace view vw_emp ????????????????????

??? ? 2 ? as

??? ? 3 ? select empno , ename , sal from emp where sal ? > 2500

??? ? 4 ? with check option ;

?

??? View created .

?

??? SQL > insert into vw_emp select 9999 , 'Robinson' , 2000 from dual ; ? -- 不滿足條件 sal > 2500 不可插入

??? insert into vw_emp select 9999 , 'Robinson' , 2000 from dual

????????????? *

??? ERROR at line 1 :

??? ORA - 01402 : view WITH CHECK OPTION where - clause violation

?

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ??????????? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?????? ? 7902 FORD ???????????? 3000

?

??? SQL > delete from vw_emp where empno = 7902 ; ???? -- 滿足條件 sal > 2500 可以刪除 , 并且修改了基表

?

??? 1 row deleted .

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ??????????? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?

??? SQL > select ? * from emp ;

?

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 7369 SMITH ????? CLERK ?????????? 7902 17 - DEC - 80 ??????? 800 ??????????????????? 20

?????? ? 7499 ALLEN ????? SALESMAN ??????? 7698 20 - FEB - 81 ?????? 1600 ??????? 300 ???????? 30

?????? ? 7521 WARD ?????? SALESMAN ??????? 7698 22 - FEB - 81 ?????? 1250 ??????? 500 ???????? 30

?????? ? 7566 JONES ????? MANAGER ???????? 7839 02 - APR - 81 ?????? 2975 ??????????????????? 20

?????? ? 7654 MARTIN ???? SALESMAN ??????? 7698 28 - SEP - 81 ?????? 1250 ?????? 1400 ???????? 30

?????? ? 7698 BLAKE ????? MANAGER ???????? 7839 01 - MAY - 81 ?????? 2850 ??????????????????? 30

?????? ? 7782 CLARK ????? MANAGER ???????? 7839 09 - JUN - 81 ?????? 2450 ??????????????????? 10

?????? ? 7788 SCOTT ????? ANALYST ???????? 7566 19 - APR - 87 ?????? 3000 ??????????????????? 20

?????? ? 7839 KING ?????? PRESIDENT ??????????? 17 - NOV - 81 ?????? 5000 ??????????????????? 10

?????? ? 7844 TURNER ???? SALESMAN ??????? 7698 08 - SEP - 81 ?????? 1500 ????????? 0 ???????? 30

?????? ? 7876 ADAMS ????? CLERK ?????????? 7788 23 - MAY - 87 ?????? 1100 ??????????????????? 20

?

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 7900 JAMES ????? CLERK ?????????? 7698 03 - DEC - 81 ??????? 950 ??????????????????? 30

?????? ? 7934 MILLER ???? CLERK ?????????? 7782 23 - JAN - 82 ?????? 1300 ??????????????????? 10

?

??? 13 rows selected .

?

??? SQL > rollback ;

?

??? Rollback complete .

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ?????????? ? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?????? ? 7902 FORD ???????????? 3000

?

??? SQL > update vw_emp set sal = 2000 where empno = 7499 ; ?? -- 不滿足條件 , 不能更新

?

??? 0 rows updated .

?

--WITH READ ONLY 屏蔽 DML 操作

??? -- 可以使用 WITH READ ONLY 選項屏蔽對視圖的 DML 操作

??? -- 任何 DML 操作都會返回一個 Oracle server 錯誤

?

??? SQL > create or replace view vw_emp

??? ? 2 ? as

??? ? 3 ? select empno , ename , sal from emp where sal > 2500

??? ? 4 ? with read only ;

?

??? View created .

?

??? SQL > insert into vw_emp select 9901 , 'Robinson' , 3000 from dual ;

??? insert into vw_emp select 9901 , 'Robinson' , 3000 from dual

??? *

??? ERROR at line 1 :

??? ORA - 01733 : virtual column not allowed here

?

?

??? SQL > select * from vw_emp ;

?

?????? ? EMPNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????? ? 7566 JONES ??????????? 2975

?????? ? 7698 BLAKE ??????????? 2850

?????? ? 7788 SCOTT ??????????? 3000

?????? ? 7839 KING ???????????? 5000

?????? ? 7902 FORD ???????????? 3000

?

??? SQL > update vw_emp set sal = 8000 where empno = 7902 ;

??? update vw_emp set sal = 8000 where empno = 7902

????????????????? ? *

??? ERROR at line 1 :

??? ORA - 01733 : virtual column not allowed here

?

?

??? SQL > delete from vw_emp where empno = 7566 ;

??? delete from vw_emp where empno = 7566

????????????? *

??? ERROR at line 1 :

??? ORA - 01752 : cannot delete from view without exactly one key - preserved table

??? ?

??? -- 創建復雜視圖

??? SQL > create view vw_sum_emp ( name , minsal , maxsal , avgsal )

??? ? 2 ? as

??? ? 3 ? select dname , min ( e . sal ), max ( e . sal ), avg ( e . sal )

??? ? 4 ? from emp e

??? ? 5 ??? join dept d

??? ? 6 ????? on e . deptno = d . deptno

??? ? 7 ? group by dname ;

?

??? View created .

?

??? SQL > select * from vw_sum_emp ;

?

??? NAME ?????????????? MINSAL ???? MAXSAL ???? AVGSAL

??? -------------- ---------- ---------- ----------

??? ACCOUNTING ?????????? 1300 ?????? 5000 2916.66667

??? RESEARCH ????????????? 800 ?????? 3000 ?????? 2175

??? SALES ???????????????? 950 ?????? 2850 1566.66667

?

-- 查詢與視圖有關的數據字典

??? SQL > select view_name , text from user_views ;

?

??? VIEW_NAME ????????????????????? TEXT

??? ------------------------------ --------------------------------------------------------------------------------

??? VW_SUM_EMP ???????????????? ???? select dname , min ( e . sal ), max ( e . sal ), avg ( e . sal )

??????????????????????????? ?? from emp e

??????????????????????????????? ? join dept d

??????????????????????????????? ?? on e . deptno = d . deptno

??????????????????????????? ?? group by dname

?

??? VW_EMP ???????????????????????? select empno , ename , sal from emp where sal > 2500

??????????????????????????? ?? with read only

?

更詳細的創建視圖的語法：

??? http :// download . oracle . com / docs / cd / B19306_01 / server .102 / b14200 / statements_8004 . htm#SQLRF01504

???

更多參考：

???

Oracle 數據庫實例啟動關閉過程

?

Oracle 10g SGA 的自動化管理

?

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實例

?

Oracle 實例和 Oracle 數據庫 (Oracle 體系結構 )

?

SQL 基礎 --> 常用函數

?

SQL 基礎 --> 過濾和排序

?

SQL 基礎 -->SELECT 查詢

?

SQL 基礎--> 視圖(CREATE VIEW)