视图View
视图View
1 数据库对象
视图是逻辑数据集，他并没有真正数据
2 为什么使用视图
视图的优点：
- 限制数据访问
- 简化查询
- 数据独立性
- 避免重复访问相同的数据
通过视图访问数据，与基表数据隔开
静态数据字典视图：DBA_ 、ALL_、USER_、动态性能视图V$
3 简单视图和复杂视图
简单视图从明细表中取得数据，不使用函数，不进行聚合
复杂视图可以联接明细表，使用函数或进行聚合
简单视图通常能接受DML语句，复杂视图则不能
4 创建视图的基本语法
[or replace]：假如视图已经存在，可以覆盖
问：建表的时候create table可以or replace吗？
答：no，可以用create or replace的对象有：functions，procedures，packages，types，synonyms，trigger and views，没有table，也没有sequence
[force]：即使子查询中明细表不存在，也创建视图
[noforce]：默认值，如果明细表不存在，则引发错误
[with check option]：加约束进行检查，对视图进行DML操作时，检查创建时的where条件
[with read only]：只能进行查询，不能通过视图修改基表
需求：创建一个简单视图（创建视图之前用户需要拥有可以创建视图的权限）
conn scott/tiger
create or replace view salvu30
as select empno employee_number,ename name,sal salary
from emp
where deptno=30;
conn / as sysdba
grant create view to scott;
conn scott/tiger
create or replace view salvu30
as select empno employee_number,ename name,sal salary
from emp
where deptno=30;
回收该权限命令：revoke create view from scott;
5 查询视图
【原理说明】oracle访问user_views数据字典，找到视图的子查询并执行，返回数据
访问视图，实际是访问基表，视图是存放在数据字典中的一条子查询
col VIEW_NAME for a15
col text for a60
select VIEW_NAME,TEXT from user_views;
6 视图的数据更新
对比不加别名的视图（加别名以后，里面的列名称也变了）：
create or replace view empvu11
as select empno,ename,job
from emp where deptno=10;
create or replace view empvu10(employee_number,employee_name,job_title)
as select empno,ename,job from emp where deptno=10;
select * from empvu10;
update empvu10 set employee_name ='test' where employee_number=7782;
select * from emp;
7 创建复杂视图
需求：创建复杂视图，查询各部门名称、工资的最小值、最大值、平均值
create view dept_sum_vu
(name,minsal,maxsal,avgsal)
as select d.dname,min(e.sal),max(e.sal),avg(e.sal)
from emp e,dept d
where e.deptno = d.deptno
group by d.dname;
select * from dept_sum_vu;
7.1 【扩展知识】：创建视图查询表空间使用情况
create view tablesp_usage as
select a.tablespace_name as tablespace_name,
to_char(a.total/1024/1024,99999999) as total_mb,
to_char((a.total-b.free)/1024/1024,99999999) use_mb,
to_char(b.free/1024/1024,99999999) as free_mb,
to_char(((total-free)/total)*100,999.99) as "Used %"
from
(select tablespace_name,sum(bytes) as total from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) as free from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 5 desc;
select * from tablesp_usage;
8 对视图进行DML操作的规则
8.1 delete
视图上执行DML操作的规则 --- delete
- 可以在简单视图上执行DML操作
- 当视图定义中包含以下元素之一时不能删除行：
- 组函数
- group by子句
- distinct关键字
- rownum伪列
8.2 update
视图上执行DML操作的规则 --- update
- 当视图定义中包含以下元素之一时不能修改数据：
- 组函数
- group by子句
- distinct关键字
- rownum伪列
- 表达式定义的列
8.3 insert
视图上执行DML操作的规则 --- insert
- 当视图定义中包含以下元素之一时不能插入数据：
- 组函数
- group by子句
- distinct关键字
- rownum伪列
- 表达式定义的列
- 表中非空的列在视图定义中未包括
9 with check option限定操作范围
10 with read only禁止DML操作
- 可以使用with read only选项屏蔽对视图的DML操作
- 任何DML操作都会返回一个Oracle server错误
在创建视图时使用with read only选项，禁止通过视图DML操作
create or replace view empvu10(employee_number,employee_name,job_title)
as select empno,ename,job from emp where deptno=10 with read only;
delete from empvu10;
11 删除视图
语法：drop view view_name;
删除视图只是删除视图的定义，并不会删除基表的数据
12 总结
视图是从数据库表中或者其他视图中获取的数据的集合
- 视图是一个逻辑的结果集，没有自己的数据
- 视图是存放在数据字典中的一条子查询
