--================================
-- 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)建視圖的語法:
???
更多參考:
???
Oracle 數(shù)據(jù)庫實(shí)例啟動(dòng)關(guān)閉過程
?
?
使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實(shí)例
?
Oracle 實(shí)例和 Oracle 數(shù)據(jù)庫 (Oracle 體系結(jié)構(gòu) )
?
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
