时间戳

首页 > 技术文章 > 视图View

视图View

jsxxd 2021-05-17 15:30 原文

视图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 总结

视图是从数据库表中或者其他视图中获取的数据的集合

  • 视图是一个逻辑的结果集，没有自己的数据
  • 视图是存放在数据字典中的一条子查询

推荐阅读