Oracle 普通视图 （Oracle Standard Views）

     视图（views）是一种基于表的”逻辑抽象”对象，由于它是从表衍生出来的，因此和表有许多相同点，我们可以和对待表一样对其进行查询/更新操作。但视图本身并不存储数据，也不分配存储空间。

    本文只讨论普通的视图（Standard Views）。Oracle的视图类型还有对象视图（Object Views）物化视图（Materiallized Views）和分析视图（Analytic Views）等。

目录

一、视图的作用

二、视图创建语法

三、基础视图

3.1 基础视图创建

3.2 自定义视图列名

3.3 强行建立视图

3.4 通过视图更新基表数据

3.5 通过with check option限制数据插入

3.6 通过with read only选项限制视图只读

四、连接视图

4.1 连接视图的更新规则

4.1.1 键值保留表（key-preserved table）

4.1.2 通过视图判断连接视图中可更新的列

4.2 with check option的影响

一、视图的作用

视图本质是存储的在数据字典中的”查询”，其底层有一系列“基表”（Base Tables）或其他视图。因此视图是基表的逻辑抽象，其最基本的作用就是隐藏底层的复杂的数据结构。

Oracle本身就提供了很多数据字典和性能视图（v$视图），通过这些视图我们可以很方便的查询某一类信息，而根本不需要知道底层真实的数据结构。

视图的主要功能有：

• 简化逻辑，通过视图对数据进行归纳后，可以隐藏底层数据的复杂性
• 访问控制，通过视图可以限制用户仅访问表的一部分
• 数据抽象，用户可以对视图的列重名或增加自己的逻辑，而不影响基表
• 应用隔离，当应用通过视图访问数据时，我们对基表增加列等维护操作时，不会影响应用程序

二、视图创建语法

视图是通过 create view 语句创建的，基础语法为（方括号代表可以忽略的选项，斜线代表选择）：

create [or repalce] [force/noforce] view [schema.]view_name [(column_alias ….)]
as
query_definition
[with read only/check option]

主要选项解释：

• or replace: 当视图已存在时，使用此选项可以重建，通常在修改视图定义时使用。
• force: 强行建立视图定义，即使视图的基表不存在或者没有权限，默认是noforce。
• schema: 可以在别人的schema中定义视图，但必须有create any view权限，省略时视图创建在自己的schema。
• column_alias: 建立视图时，可以重命名列。
• with read only: 视图不可更新。
• with check option: 无法通过视图更新或插入视图看不到的数据。

注：上面仅包含了最常用的视图创建选项，更完整的视图定义语法请参阅官方文档。

三、基础视图

通过示例可以更好的理解视图各选项的含义，下面以Oracle自带的sample_schema中的hr.employees作为基表演示视图的创建。

3.1 基础视图创建

创建一个基础视图（仅包含一张基表），仅包含employee_id, first_name, last_name, salary列

create view v_emp as
select employee_id, first_name, last_name, salary from employees;

desc v_emp;

3.2 自定义视图列名

更新刚才的视图，列名重命名为a,b,c,d:

create or replace view v_emp(a, b, c, d) as
select employee_id, first_name, last_name, salary from employees;

desc v_emp;

3.3 强行建立视图

通过force选项创建一个基表不存在的视图：

create force view v_not_exist as select * from employeesssssss;

这里我们基于一个不存在的表强行建立了一个视图，因此有一个警告。在视图基表存在之前，该视图无法使用。

3.4 通过视图更新基表数据

更新v_emp视图，限制仅显示employee_id>=200的数据

create or replace view v_emp as
select employee_id, first_name, last_name, salary from employees where employee_id>=200;

select * from v_emp;

通过视图将employee_id为206的数据更新为99999：

update v_emp set salary=99999 where employee_id=206;

select * from v_emp;

通过视图对基表的更新会受到基表的约束（例如视图看不到的列如果有非空约束，那么会阻止通过视图插入数据），同时如果用户对基表没有DML权限，那么也无法通过视图更新基表。

3.5 通过with check option限制数据插入

通过v_emp视图虽然只能看到employee_id>=200的数据，我们却可以通过它插入employee_id<200的数据，但是视图自己是无法看到该数据的：

insert into v_emp values(20,'Vincent','Chen',12345);    -- 通过视图插入employee_id=20的数据

select employee_id, salary from employees where employee_id=20;  -- 查询基表，数据插入成功

select * from v_emp;  -- 而视图自己是看不到数据的

更新视图，新增with check option选项，限制其无法插入视图看不到的数据（报错：违反视图定义中的where子句）：

create or replace view v_emp as
select employee_id, first_name, last_name, salary from employees where employee_id>=200 with check option;

insert into v_emp values(21,'Vincent','Chen',12345);   -- 通过视图插入employee_id=21的数据

3.6 通过with read only选项限制视图只读

如果视图创建时增加的with read only选项，那么禁止通过视图插入或更新数据（报错：只读视图无法更新）：

create or replace view v_emp as
select employee_id, first_name, last_name, salary from employees where employee_id>=200 with read only;

update v_emp set salary=99999 where employee_id=206;

四、连接视图

如果视图的查询定义中包含多张表连接，那么就是连接视图（Join Views）。下面建立基于hr.departments和hr.employees的连接视图，两个基表的表结构如下，两张表可以通过department_id进行连接：

4.1 连接视图创建

create view v_dept_emp as
select d.department_id, d.department_name, e.employee_id, e.first_name, e.salary
from departments d, employees e
where d.department_id = e.department_id
and d.department_id = 20;

4.1 可更新连接视图（updatable join views）

如果连接视图没有通过 with read only 选项约束，那么就是可更新连接视图（updatable join views）。虽然可以更新，但对其更新受到一定的规则约束。

通用约束：对于可更新视图的更新，每次最多涉及一张基表。即通过连接视图更新时，每次只能更新来自同一张基表中的列，不能涉及多张基表。

4.1.1 键值保留表（key-preserved table）

除了每次只能影响一张基表的约束，可更新视图也并不是所有的列都可以更新。

这里我们先要了解一个概念：保留键值表（Key-preserved table），如果某张基表的键连接后，其键值也可以作为最终连接结果的键，那么它就是键值保留表。

定义比较晦涩，我们通过上面视图定义来解释。在视图的两张基表employees（雇员 别名e）和departments（部门 别名d）中，两张表的主键分别是employee_id和department_id，我们的连接条件是 d.department_id=e.department_id，这里employees去连接departments表的主键（d.department_id），那么employees就是键值保留表。在最终的结果中，employees表连接其他表的主键或唯一键，会保证自己的每行在连接结果中最多出现一次，而departments表是通过主键（或唯一键）进行连接的。

即键值保留表会去连接别人的主键（或唯一键），由于连接的是别人的主键/唯一键，所以连接后，自己的行在最终结果中不会重复，因此可以保持唯一（键值保留）。从视图查询结果上我们也可以看到：employees表去连接departments表的主键/唯一键，那么最终结果一定不会导致employees表的记录重复，那么employees的记录可以在最终视图中保持唯一。而departments表的记录可以看到在最终结果中出现了重复（这里就算结果不重复，departments表也不是键值保留表，因为它无法保证不重复）。

select * from v_dept_emp;

理解了键值保留表（key-preserved table），可更新视图的另一个约束就是：可以更新的列要来自键值保留表。

上面示例中，departments表不是键值保留表，我们去更新来自departments表的列时就会报错：不能更改来自非键值保留表的列（ORA-01779）：

update v_dept_emp set department_name='abc' where department_id=20;

而employees是键值保留表，来自它的列可以更新成功（这里我们只是把更新的列从department_name换成了first_name，where条件保持相同）：

update v_dept_emp set first_name='abc' where department_id=20;

4.1.2 通过视图判断连接视图中可更新的列

如果通过键值保留表的概念判断连接视图中哪些列可以更新比较困难，那么Oracle也提供了更简便的方式，直接通过视图dba/all/user_updatable_columns查询连接视图中的可更新列：

select table_name, column_name, updatable from user_updatable_columns where table_name='V_DEPT_EMP';

通过updatable列我们也可以看到来自employees表（key-preserved table）的三列是可以更新的。

4.2 with check option的影响

如果连接视图在定义时增加了with check option，那么所有连接条件中的列都不可以更新（即使其来自键值保留表）。

我们将上面v_dept_emp视图中的department_id列来源从departments表修改为employees表（d.department_id => e.department_id），由于employees是键值保留表，因此从视图user_updatable_columns我们可以看到department_id是可以更新的。

create or replace view v_dept_emp as
select e.department_id, d.department_name, e.employee_id, e.first_name, e.salary
from departments d, employees e
where d.department_id = e.department_id
and d.department_id = 20;

select table_name, column_name, updatable from user_updatable_columns where table_name='V_DEPT_EMP';

我们将视图查询保持不变，仅增加一个with check option选项，department_id依然来自键值保留表employees，我们再次查询视图，发现department_id变为不可更新（因为它是连接条件 d.department_id = e.department_id 中的列）。

create or replace view v_dept_emp as
select e.department_id, d.department_name, e.employee_id, e.first_name, e.salary
from departments d, employees e
where d.department_id = e.department_id
and d.department_id = 20
with check option;

select table_name, column_name, updatable from user_updatable_columns where table_name='V_DEPT_EMP';