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

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

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

?

視圖：

??? 從表中抽出來的邏輯上相關(guān)的數(shù)據(jù)集合

??? 視圖其實(shí)就是一條查詢 SQL 語句，用于顯示一個(gè)或多個(gè)表或其它視圖中相關(guān)數(shù)據(jù)。

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

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

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

?

使用視圖的好處：

??? 可把復(fù)雜的 SQL 語句簡單化

??? 可保證數(shù)據(jù)的安全性，限制對數(shù)據(jù)的訪問，因?yàn)樗鼘Ρ碇械囊恍┳侄问请[藏的

??? 可使相同的數(shù)據(jù)以不同形式出現(xiàn)在不同的視圖中

?

視圖分類：

??? 簡單視圖

?????? 只從一個(gè)表中獲取數(shù)據(jù)

?????? 不包含函數(shù)

?????? 不包含分組數(shù)據(jù)

?????? 可通過該視圖進(jìn)行 DML 操作

???

??? 復(fù)雜視圖

?????? 從多個(gè)表中獲取數(shù)據(jù)

?????? 包含函數(shù)

?????? 包含分組數(shù)據(jù)

?????? 不一定能通過視圖進(jìn)行 DML 操作

???

創(chuàng)建視圖：

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

??? AS subquery

??? [WITH CHECK OPTION]

??? [WITH READ ONLY]

?

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

??? FORCE ? ?????? 不管引用的表是否存在，都創(chuàng)建這個(gè)視圖

??? NOFORCE ??????? 只有當(dāng)表存在的時(shí)候，才能創(chuàng)建這個(gè)視圖（默認(rèn)方式）

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

???????????????????? 沒有指定約束名，系統(tǒng)會(huì)自動(dòng)為約束命名，形式為 SYS_Cn 。

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

???

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

?

?

修改視圖

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

???

刪除視圖：

??? DROP VIEW view_name

?

視圖中使用 DML 的規(guī)定：

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

??? 組函數(shù)

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

??? 列的定義為表達(dá)式

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

?

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

??? 組函數(shù)

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

??? 列的定義為表達(dá)式

?

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

??? 組函數(shù)

??? GROUP BY 、 ORDER BY

??? DISTINCT

??? ROWNUM

?

?

與視圖有關(guān)的數(shù)據(jù)字典：

??? DBA ＿ VIEWS

??? USER_VIEWS

?

-- 演示創(chuàng)建視圖 ?

??? 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 只能在特定的范圍內(nèi)執(zhí)行，任何違反

??? --WITH CHECK OPTION 約束的請求都會(huì)失敗

???

??? 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 選項(xiàng)屏蔽對視圖的 DML 操作

??? -- 任何 DML 操作都會(huì)返回一個(gè) Oracle server 錯(cuò)誤

?

??? 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

??? ?

??? -- 創(chuàng)建復(fù)雜視圖

??? 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

?

-- 查詢與視圖有關(guān)的數(shù)據(jù)字典

??? 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

?

更詳細(xì)的創(chuàng)建視圖的語法：

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

???

更多參考：

???

Oracle 數(shù)據(jù)庫實(shí)例啟動(dòng)關(guān)閉過程

?

Oracle 10g SGA 的自動(dòng)化管理

?

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實(shí)例

?

Oracle 實(shí)例和 Oracle 數(shù)據(jù)庫 (Oracle 體系結(jié)構(gòu) )

?

SQL 基礎(chǔ) --> 常用函數(shù)

?

SQL 基礎(chǔ) --> 過濾和排序

?

SQL 基礎(chǔ) -->SELECT 查詢

?

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