target
掌握什么是視圖
掌握視圖的創(chuàng)建
掌握視圖的相關(guān)操作
掌握視圖的修改和刪除
1. 什么是視圖
1.1 認(rèn)識(shí)視圖
根據(jù)官方的文檔可以這樣理解視圖：它是一個(gè)基于一個(gè)表或多個(gè)表的邏輯表，視圖本身不包含任何數(shù)據(jù)。
通俗來說，可以把視圖看成是虛擬的表，只是一個(gè)査詢語句的結(jié)果，它的數(shù)據(jù)最終是從表中獲取的，這些表通常稱為源表或基表。當(dāng)基表的數(shù)據(jù)發(fā)生變化時(shí)，視圖里的數(shù)據(jù)同樣發(fā)生變化。通常視圖的數(shù)據(jù)源有下面三種情況：
- 單一表的子集。
- 多表操作結(jié)果集。
- 視圖的子集。
1.2 視圖的作用
-
使數(shù)據(jù)簡化。
在表中很多數(shù)據(jù)對(duì)業(yè)務(wù)來說是冗余的，這時(shí)開發(fā)者會(huì)使用比較復(fù)雜的SQL 語句得到自己想要的。實(shí)際開發(fā)中不能要求毎個(gè)人都能做到這一點(diǎn)，所以，通常情況下由一個(gè)人把該復(fù)雜語句做成視圖，其他人員直接調(diào)用該視圖即可。這樣對(duì)視圖使用人員就簡化了數(shù)據(jù)，隱藏了數(shù)據(jù)的復(fù)雜性。
-
使數(shù)據(jù)更加獨(dú)立。
程序開發(fā)時(shí)，大多數(shù)是程序直接訪問數(shù)據(jù)庫的表，當(dāng)這些表的結(jié)構(gòu)隨著業(yè)務(wù)的變化而不得不重新設(shè)計(jì)時(shí)會(huì)影響到程序（通常表一旦設(shè)計(jì)完成就很難再做修改），所以可以使得程序直接訪問視圖。這樣視圖就可以把程序和數(shù)據(jù)庫的表隔離開來，降低開發(fā)者的勞動(dòng)成本。
-
增加安全性。
視圖可以査詢表指定的列來展現(xiàn)給用戶，而不必讓使用者完全看見表的 、 所有字段。這種情況很多是一個(gè)公司提供給其他合作伙伴査詢數(shù)據(jù)的接口，而視圖通常也會(huì)設(shè)成只讀屬性。
1.3 視創(chuàng)建圖的語法
create [or replace ] [ noforce| force] view
[schma.]view
[(alias,...) inline_constraint(s)]
[out_of_line_constraint(s)]
as subquery
[
with { read only | check option [CONSTRAINT constraint]]}
];
語法說明：
- OR REPLACE：表示新建視圖可以覆蓋同名視圖。
- noforce| force：表示是否強(qiáng)制創(chuàng)建視圖。例如，在基表不存在的情況下就創(chuàng)建視圖是有錯(cuò)誤的，這時(shí)可以用FORCE關(guān)鍵詞強(qiáng)制創(chuàng)建視圖，然后再創(chuàng)建基表。Oracle中NOFORCE是默認(rèn)值。
- ( schema. |view：這是視圖的所屬方案名稱和視圖本身的名稱。
- [(alias,...) inline_constraint(s)]：視圖字段的別名和內(nèi)聯(lián)約束。
- [out_ofJine_constraint(s)]：也是約束，是與inline, constraint(s)相反的聲明方式。
- WITH READ ONLY：設(shè)置視圖只讀，這樣的視圖具有更高的安全性。
- WITH CHECK OPTION [ CONSTRAINT constraint )：—旦使用該限制，當(dāng)對(duì)視圖増加或修改數(shù)據(jù)時(shí)必須滿足子?xùn)嗽兊臈l件。也就是說，是把子?xùn)嗽兊臈l件作為一個(gè)約束， 而constraint是這個(gè)約束的名稱。
注意：大括號(hào)是必填語法。
1.4 為用戶授創(chuàng)建視圖權(quán)限
創(chuàng)建視圖需要有Create View系統(tǒng)權(quán)限。
① 查看當(dāng)前用戶具有的權(quán)限：
select * from session_privs;
② 為用戶授權(quán)：
授權(quán)需要先登錄系統(tǒng)用戶：
請(qǐng)輸入用戶名: sys
輸入口令:as sysdba
授權(quán)語句：
grant create view to scott;
2. 視圖的創(chuàng)建
可以直接使用SQL語句創(chuàng)建一個(gè)視圖，也可以使用可視化工具來創(chuàng)建。
創(chuàng)建視圖前先列出表EMP (員工表)和DEPT (部門表)的記 錄，方便和視圖做對(duì)比。
EMP表數(shù)據(jù)：
SQL> set linesize 200
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
DEPT表數(shù)據(jù)：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2.1 創(chuàng)建單表視圖
創(chuàng)建視圖展示2行，工作是”CLERK“的數(shù)據(jù)：
create or replace view simple_emp_view
as
select EMPNO,ENAME,JOB,SAL,DEPTNO
from emp
where job = 'CLERK'
and rownum < 3;
使用rownum來限制行數(shù)。
查看創(chuàng)建成功的視圖：
SQL> select * from simple_emp_view;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7876 ADAMS CLERK 1100 20
2.2 創(chuàng)建多表視圖
??：查詢出工作是”CLERK“，并且部門在”NEW YORK“的員工信息
create or replace view multi_emp_view as
select e.ename,e.job,e.sal,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.job='CLERK' and d.loc='NEW YORK';
2.3 創(chuàng)建基于視圖的視圖
前面創(chuàng)建的視圖是基于表的，還可以創(chuàng)建基于視圖的視圖。很少使用。
??：
create or replace view vi_emp_view as
select ename,job,loc from multi_emp_view ;
2.4 創(chuàng)建沒有源表的視圖
在數(shù)據(jù)庫開發(fā)過程中，如果設(shè)計(jì)工作做得很好，就可能出現(xiàn)在沒有源表時(shí)先把視圖創(chuàng)建出來的情況。因?yàn)楦鶕?jù)設(shè)計(jì)，基表是肯定要建的。
create or replace view notable_emp_view as
select ename,job,sal from notable;
輸出：
第 3 行出現(xiàn)錯(cuò)誤:ORA-00942: 表或視圖不存在
這里說明視圖創(chuàng)建不成功。使用force關(guān)鍵字可以避免這種情況。該關(guān)鍵字表示創(chuàng)建視圖時(shí)，無視源表是否存在。
create or replace force view notable_emp_view as
select ename,job,sal from notable;
輸出：
警告: 創(chuàng)建的視圖帶有編譯錯(cuò)誤。
說明視圖創(chuàng)建成功。
3. 查看視圖
可以利用下面的語句查看當(dāng)前用戶下的所有視圖：
select view_name from user_views;
在SQL plus中，當(dāng)查詢出來的列寬不夠時(shí)可以使用下面的語句改變字段寬度：
col 列 format A15
查看視圖的創(chuàng)建語法：
select text from all_views where view_name = '視圖名';
注意：視圖名要全部大寫。
4. 操作視圖數(shù)據(jù)的限制
視圖增加或更新數(shù)據(jù)實(shí)際上是在操作視圖的源表。
除此之外，視圖本身可以設(shè)置更新限制條件。
4.1 視圖read only設(shè)置
創(chuàng)建視圖時(shí)，為了避免用戶修改數(shù)據(jù)，可以把視圖設(shè)成只讀屬性。
create or replace view sim_emp_view as
select ename,job,sal from emp where job='CLERK'
with read only;
當(dāng)插入或者修改視圖數(shù)據(jù)時(shí)，會(huì)提示”無法對(duì)只讀視圖執(zhí)行DML操作“；
4.2 視圖check option設(shè)置
在某些情況下允許修改視圖的數(shù)據(jù)，修改數(shù)據(jù)的本質(zhì)是修改視圖源表的數(shù)據(jù)。
假如某個(gè)視圖査詢岀來的是年齡大于20的所有數(shù)據(jù)，如果為該視圖增加一條年齡為10的記錄，那么該記錄將不會(huì)出現(xiàn)在視圖中。顯然這是不符合邏輯的。為了避免這種情況的發(fā)生，可以利用 CHECK OPTION選項(xiàng)來設(shè)置視圖的檢査約束。
CHECK OPTION選項(xiàng)表示視圖啟動(dòng)了和子?xùn)嗽儣l件一樣的約束。也就是說，如果對(duì)視圖修改或插入的數(shù)據(jù)和査詢條件不一致，那么該操作會(huì)被中止。
先創(chuàng)建一個(gè)emp_temp表：
create table emp_temp as select * from emp;
創(chuàng)建視圖：
create or replace view simple_emp_view as
select ename,job,sal
from emp_temp
where job='CLERK'
with check option;
這時(shí)如果要增加或修改數(shù)據(jù)，就要符合where后面的條件，即工作是”CLERK“。
為了驗(yàn)證視圖的check option設(shè)置是否生效，對(duì)視圖進(jìn)行MDL操作。
① 增加數(shù)據(jù)
insert into simple_emp_view(ename,job,sal)
values ('aa','SALESMAN',10000);
由于增加的數(shù)據(jù)job字段不是”CLERK“，會(huì)報(bào)錯(cuò)：
第 1 行出現(xiàn)錯(cuò)誤:ORA-01402: 視圖 WITH CHECK OPTION where 子句違規(guī)
② 修改數(shù)據(jù)
update simple_emp_viewset job='SALESMAN'
where ename='SMITH';
會(huì)報(bào)錯(cuò)：
第 1 行出現(xiàn)錯(cuò)誤:ORA-01402: 視圖 WITH CHECK OPTION where 子句違規(guī)
③ 刪除數(shù)據(jù)
delete from simple_emp_view
where job = 'SALESMAN';
輸出：
已刪除0行。
說明刪除沒起作用。
5. 視圖的修改
視圖的修改比較特殊，不能像表一樣修改，更準(zhǔn)確地說它沒有修改選項(xiàng)，可以覆蓋原有視圖，但這不會(huì)影響視圖的使用。因?yàn)橐晥D本身不包含數(shù)據(jù)，所以覆蓋原視圖時(shí)就不存在數(shù)據(jù)丟失的問題了。
6. 視圖的刪除
視圖的刪除和表的刪除一樣。
語法：
drop view [schema.]view [cascade constraints];
說明：
cascade constraints：刪除視圖時(shí)刪除約束。
??：刪除simple_emp_view視圖
drop view simple_emp_view;
7. 案例
下面是4張表，是簡單的二手車買賣的原型。
(1) 買賣記錄表(tradrec)
| 字段名 | 注釋 | 數(shù)據(jù)類型 |
|---|---|---|
| id | 序號(hào) | varchar2(10) |
| carrgnum | 車牌號(hào) | varchar2(8) |
| carbrandid | 商標(biāo)編號(hào) | varchar2(8) |
| bgdate | 交易日期 | date |
| bargainer | 交易者 | varchar2(10) |
| remark | 備注 | varchar2(200) |
建表語句：
create table tradrec(
id varchar2(10) primary key,
carrgnum varchar2(8),
carbrandid varchar2(8),
bgdate date,
bargainer varchar2(10),
remark varchar2(200)
);
(2) 汽車品牌表(carbrandtab)
| 字段名 | 注釋 | 數(shù)據(jù)類型 |
|---|---|---|
| carbrandid | 品牌編碼 | varchar(8) |
| carbrand | 汽車品牌名稱 | varchar(20) |
建表語句：
create table carbrandtab(
carbrandid varchar(8) primary key,
carbrand varchar(20)
);
(3) 車輛車色表(carfettletab)
| 字段名 | 注釋 | 數(shù)據(jù)類型 |
|---|---|---|
| carfettleid | 成色編碼 | varchar(2) |
| carfettle | 成色 | varchar(20) |
create table carfettletab(
carfettleid varchar(2) primary key,
carfettle varchar(20)
);
(4) 二手車記錄表(carinfo)
| 字段名 | 注釋 | 數(shù)據(jù)類型 |
|---|---|---|
| carid | 編號(hào) | varchar2(10) |
| carrgnum | 車牌號(hào) | varchar2(8) |
| carbrandid | 品牌編號(hào) | varchar2(8) |
| carfettleid | 成色編碼 | varchar2(2) |
| flag | 是否手售出 | varchar2(1) |
建表語句：
create table carinfo(
carid varchar2(10),
carrgnum varchar2(8),
carbrandid varchar2(8),
carfettleid varchar2(2),
flag varchar2(1),
primary key(carid),
constraint fk_carinfo_carbrand foreign key(carbrandid)
references carbrandtab(carbrandid) on delete cascade,
constraint fk_carinfo_carfettletab foreign key(carfettleid)
references carfettletab(carfettleid) on delete cascade
);
要求：
??：列出售出車輛的明細(xì)列表，包括序號(hào)、車牌號(hào)、車品牌、售出日期、售出人、成色
create or replace view tradrec_detail_view as
select tc.id,tc.carrgnum,cb.carbrand,tc.bgdate,tc.bargainer,cfb.carfettle
from tradrec tc,carinfo co,carbrandtab cb,carfettletab cfb
where tc.carbrandid = cb.carbrandid
and co.carfettleid = cfb.carfettleid
and tc.carrgnum = co.carrgnum
order by cb.carbrand;
??：統(tǒng)計(jì)某個(gè)日期之后不同品牌的車主賣出的數(shù)量
create or replace view tradrec_stat_view as
select cb.carbrand,count(carrgnum) as quantity
from tradrec tc,carbrandtab cb
where tc.bgdate > to_date('2020-5-1','YYYY-MM-DD')
and tc.carbrandid = cb.carbrandid
group by cb.carbrand;
習(xí)題
一、選擇題
下面有關(guān)視圖的數(shù)據(jù)來源.敘述不正確的是（ ）.
A.視圖數(shù)據(jù)是用戶直接添加到視圖中的
B.視圖數(shù)據(jù)來源于單表
C.視圖數(shù)據(jù)來源于多表
D.視圖數(shù)據(jù)來源于其他視圖-
創(chuàng)建視圖帶SUM函數(shù)，視圖數(shù)據(jù)是否可以更新？（ ）
A.不可以
B.可以 -
視圖中CHECK OPTION設(shè)置有什么作用？（ ）
A.沒有實(shí)際作用
B.檢査視圖更新數(shù)據(jù)是否符合視圖創(chuàng)建時(shí)的査詢條件
C.檢査數(shù)據(jù)是否有更新
D.不允許向基表中更新數(shù)據(jù)
二、簡答題- 視圖的約束是否和表的約束一樣？
- 當(dāng)對(duì)視圖做刪除數(shù)據(jù)操作時(shí)，如果視圖里沒有符合條件的數(shù)據(jù)，但基表存在符合條件的 數(shù)據(jù)，實(shí)際會(huì)出現(xiàn)什么情況？
