target
掌握什么是视图
掌握视图的创建
掌握视图的相关操作
掌握视图的修改和删除
1. 什么是视图
1.1 认识视图
根据官方的文档可以这样理解视图：它是一个基于一个表或多个表的逻辑表，视图本身不包含任何数据。
通俗来说，可以把视图看成是虚拟的表，只是一个査询语句的结果，它的数据最终是从表中获取的，这些表通常称为源表或基表。当基表的数据发生变化时，视图里的数据同样发生变化。通常视图的数据源有下面三种情况：
- 单一表的子集。
- 多表操作结果集。
- 视图的子集。
1.2 视图的作用
-
使数据简化。
在表中很多数据对业务来说是冗余的，这时开发者会使用比较复杂的SQL 语句得到自己想要的。实际开发中不能要求毎个人都能做到这一点，所以，通常情况下由一个人把该复杂语句做成视图，其他人员直接调用该视图即可。这样对视图使用人员就简化了数据，隐藏了数据的复杂性。
-
使数据更加独立。
程序开发时，大多数是程序直接访问数据库的表，当这些表的结构随着业务的变化而不得不重新设计时会影响到程序（通常表一旦设计完成就很难再做修改），所以可以使得程序直接访问视图。这样视图就可以把程序和数据库的表隔离开来，降低开发者的劳动成本。
-
增加安全性。
视图可以査询表指定的列来展现给用户，而不必让使用者完全看见表的 、 所有字段。这种情况很多是一个公司提供给其他合作伙伴査询数据的接口，而视图通常也会设成只读属性。
1.3 视创建图的语法
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：表示是否强制创建视图。例如，在基表不存在的情况下就创建视图是有错误的，这时可以用FORCE关键词强制创建视图，然后再创建基表。Oracle中NOFORCE是默认值。
- ( schema. |view：这是视图的所属方案名称和视图本身的名称。
- [(alias,...) inline_constraint(s)]：视图字段的别名和内联约束。
- [out_ofJine_constraint(s)]：也是约束，是与inline, constraint(s)相反的声明方式。
- WITH READ ONLY：设置视图只读，这样的视图具有更高的安全性。
- WITH CHECK OPTION [ CONSTRAINT constraint )：—旦使用该限制，当对视图増加或修改数据时必须满足子査询的条件。也就是说，是把子査询的条件作为一个约束， 而constraint是这个约束的名称。
注意：大括号是必填语法。
1.4 为用户授创建视图权限
创建视图需要有Create View系统权限。
① 查看当前用户具有的权限：
select * from session_privs;
② 为用户授权：
授权需要先登录系统用户：
请输入用户名: sys
输入口令:as sysdba
授权语句：
grant create view to scott;
2. 视图的创建
可以直接使用SQL语句创建一个视图，也可以使用可视化工具来创建。
创建视图前先列出表EMP (员工表)和DEPT (部门表)的记 录，方便和视图做对比。
EMP表数据：
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表数据：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2.1 创建单表视图
创建视图展示2行，工作是”CLERK“的数据：
create or replace view simple_emp_view
as
select EMPNO,ENAME,JOB,SAL,DEPTNO
from emp
where job = 'CLERK'
and rownum < 3;
使用rownum来限制行数。
查看创建成功的视图：
SQL> select * from simple_emp_view;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7876 ADAMS CLERK 1100 20
2.2 创建多表视图
🌰：查询出工作是”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 创建基于视图的视图
前面创建的视图是基于表的，还可以创建基于视图的视图。很少使用。
🌰：
create or replace view vi_emp_view as
select ename,job,loc from multi_emp_view ;
2.4 创建没有源表的视图
在数据库开发过程中，如果设计工作做得很好，就可能出现在没有源表时先把视图创建出来的情况。因为根据设计，基表是肯定要建的。
create or replace view notable_emp_view as
select ename,job,sal from notable;
输出：
第 3 行出现错误:ORA-00942: 表或视图不存在
这里说明视图创建不成功。使用force关键字可以避免这种情况。该关键字表示创建视图时，无视源表是否存在。
create or replace force view notable_emp_view as
select ename,job,sal from notable;
输出：
警告: 创建的视图带有编译错误。
说明视图创建成功。
3. 查看视图
可以利用下面的语句查看当前用户下的所有视图：
select view_name from user_views;
在SQL plus中，当查询出来的列宽不够时可以使用下面的语句改变字段宽度：
col 列 format A15
查看视图的创建语法：
select text from all_views where view_name = '视图名';
注意：视图名要全部大写。
4. 操作视图数据的限制
视图增加或更新数据实际上是在操作视图的源表。
除此之外，视图本身可以设置更新限制条件。
4.1 视图read only设置
创建视图时，为了避免用户修改数据，可以把视图设成只读属性。
create or replace view sim_emp_view as
select ename,job,sal from emp where job='CLERK'
with read only;
当插入或者修改视图数据时，会提示”无法对只读视图执行DML操作“；
4.2 视图check option设置
在某些情况下允许修改视图的数据，修改数据的本质是修改视图源表的数据。
假如某个视图査询岀来的是年龄大于20的所有数据，如果为该视图增加一条年龄为10的记录，那么该记录将不会出现在视图中。显然这是不符合逻辑的。为了避免这种情况的发生，可以利用 CHECK OPTION选项来设置视图的检査约束。
CHECK OPTION选项表示视图启动了和子査询条件一样的约束。也就是说，如果对视图修改或插入的数据和査询条件不一致，那么该操作会被中止。
先创建一个emp_temp表：
create table emp_temp as select * from emp;
创建视图：
create or replace view simple_emp_view as
select ename,job,sal
from emp_temp
where job='CLERK'
with check option;
这时如果要增加或修改数据，就要符合where后面的条件，即工作是”CLERK“。
为了验证视图的check option设置是否生效，对视图进行MDL操作。
① 增加数据
insert into simple_emp_view(ename,job,sal)
values ('aa','SALESMAN',10000);
由于增加的数据job字段不是”CLERK“，会报错：
第 1 行出现错误:ORA-01402: 视图 WITH CHECK OPTION where 子句违规
② 修改数据
update simple_emp_viewset job='SALESMAN'
where ename='SMITH';
会报错：
第 1 行出现错误:ORA-01402: 视图 WITH CHECK OPTION where 子句违规
③ 删除数据
delete from simple_emp_view
where job = 'SALESMAN';
输出：
已删除0行。
说明删除没起作用。
5. 视图的修改
视图的修改比较特殊，不能像表一样修改，更准确地说它没有修改选项，可以覆盖原有视图，但这不会影响视图的使用。因为视图本身不包含数据，所以覆盖原视图时就不存在数据丢失的问题了。
6. 视图的删除
视图的删除和表的删除一样。
语法：
drop view [schema.]view [cascade constraints];
说明：
cascade constraints：删除视图时删除约束。
🌰：删除simple_emp_view视图
drop view simple_emp_view;
7. 案例
下面是4张表，是简单的二手车买卖的原型。
(1) 买卖记录表(tradrec)
| 字段名 | 注释 | 数据类型 |
|---|---|---|
| id | 序号 | varchar2(10) |
| carrgnum | 车牌号 | varchar2(8) |
| carbrandid | 商标编号 | 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)
| 字段名 | 注释 | 数据类型 |
|---|---|---|
| carbrandid | 品牌编码 | varchar(8) |
| carbrand | 汽车品牌名称 | varchar(20) |
建表语句：
create table carbrandtab(
carbrandid varchar(8) primary key,
carbrand varchar(20)
);
(3) 车辆车色表(carfettletab)
| 字段名 | 注释 | 数据类型 |
|---|---|---|
| carfettleid | 成色编码 | varchar(2) |
| carfettle | 成色 | varchar(20) |
create table carfettletab(
carfettleid varchar(2) primary key,
carfettle varchar(20)
);
(4) 二手车记录表(carinfo)
| 字段名 | 注释 | 数据类型 |
|---|---|---|
| carid | 编号 | varchar2(10) |
| carrgnum | 车牌号 | varchar2(8) |
| carbrandid | 品牌编号 | 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
);
要求：
🌰：列出售出车辆的明细列表，包括序号、车牌号、车品牌、售出日期、售出人、成色
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;
🌰：统计某个日期之后不同品牌的车主卖出的数量
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;
习题
一、选择题
下面有关视图的数据来源.叙述不正确的是（ ）.
A.视图数据是用户直接添加到视图中的
B.视图数据来源于单表
C.视图数据来源于多表
D.视图数据来源于其他视图-
创建视图带SUM函数，视图数据是否可以更新？（ ）
A.不可以
B.可以 -
视图中CHECK OPTION设置有什么作用？（ ）
A.没有实际作用
B.检査视图更新数据是否符合视图创建时的査询条件
C.检査数据是否有更新
D.不允许向基表中更新数据
二、简答题- 视图的约束是否和表的约束一样？
- 当对视图做删除数据操作时，如果视图里没有符合条件的数据，但基表存在符合条件的 数据，实际会出现什么情况？
