ORACLE 进阶(二)视图详解
一、视图定义
视图(view
),也称虚表, 不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL
。
视图是从一个或多个实际表中获得的,这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中,与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些 Oracle 视图可以修改对应的基表,有些则不能(仅仅能查询)。
还有一种视图:物化视图(MATERIALIZED VIEW
),也称实体化视图,快照 (8i 以前的说法) ,它是含有数据的,占用存储空间。
tips: 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only
选项)
二、视图作用
提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名);
隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句。这也是
oracle
提供各种"数据字典视图"的原因之一,all_constraints
就是一个含有 2 个子查询并连接了 9 个表的视图(在catalog.sql
中定义);执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了;
提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见;
简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。
三、创建视图
要在当前方案中创建视图, 用户必须具有create view
系统权限; 要在其他方案中创建视图, 用户必须具有create any view
系统权限。视图的功能取决于视图拥有者的权限。
语法如下:
tips:
or replace
: 如果存在同名的视图, 则使用新视图"替代"已有的视图;force
: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限;column1,column2,...:视图的列名, 列名的个数必须与
select
查询中列的个数相同;如果select
查询包含函数或表达式, 则必须为其定义列名。此时,既可以用 column1, column2 指定列名,也可以在 select 查询中指定列名;4
with check option
:指定对视图执行的dml
操作必须满足“视图子查询”的条件,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据,必须是select
查询所能查询到的数据,否则不允许操作并返回错误提示。 默认情况下,在增删改之前"并不会检查"这些行是否能被 select 查询检索到;5
with read only
:创建的视图只能用于查询数据, 而不能用于更改数据;
3.1 创建简单视图
简单视图定义:是指基于单个表建立的,不包含任何函数、表达式和分组数据的视图。
对简单视图进行 DML 操作
基表也发生了相应的更改。
3.2 创建只读视图
只能查询,无法进行更改:
更新基表,只读视图也发生了相应的更改
3.3 创建检查约束视图 with check option
创建检查视图:对通过视图进行的增删改操作进行检查,要求增删改操作的数据必须是select
查询所能查询到的数据 20 号部门不在查询范围内,违反检查约束,所以无法插入;
所删除的数据在查询范围内,不违反检查约束
3.4 连接视图
3.4.1 连接视图定义
基于多个表所创建的视图,即,定义视图的查询是一个连接查询。 主要目的是为了简化连接查询;
3.4.2 创建连接视图
示例 1: 查询部门编号为 10 和 30 的部门及雇员信息
3.4.3 连接视图上的 DML 操作
在视图上进行的所有 DML 操作,最终都会在基表上完成;select
视图没有什么限制,但insert/delete/update
有一些限制;
3.4.4 键值保存表
如果连接视图中的一个“基表的键”(主键、唯一键)在它的视图中仍然存在,并且“基表的键”仍然是“连接视图中的键”(主键、唯一键);即,某列在基表中是主键|唯一键,在视图中仍然是主键|唯一键,则称这个基表为“键值保存表”。
一般地,由主外键关系的 2 个表组成的连接视图,外键表就是键值保存表,而主键表不是。
3.4.5 连接视图的更新准则
一、一般准则
任何 DML 操作,只能对视图中的键值保存表进行更新, 即,“不能通过连接视图修改多个基表”;
在 DML 操作中,“只能使用连接视图定义过的列”;
“自连接视图”的所有列都是可更新(增删改)的
二、insert 准则
在 insert 语句中不能使用“非键值保存表”中的列(包括“连接列”);
执行 insert 操作的视图,至少应该“包含”键值保存表中所有设置了约束的列;
如果在定义连接视图时使用了 WITH CHECK OPTION 选项,则“不能”针对连接视图执行 insert 操作;
三、update 准则
键值保存表中的列是可以更新的;
如果在定义连接视图时使用了
WITH CHECK OPTION
选项,则连接视图中的连接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的,连接列和共有列之外的 其他列是“可以”更新的;
四、delete 准则
如果在定义连接视图时使用了
WITH CHECK OPTION
选项,依然“可以”针对连接视图执行delete
操作;
3.4.6 可更新连接视图
如果创建连接视图的 select 查询“不包含”如下结构,并且遵守连接视图的“更新准则”,则这样的连接视图是“可更新”的:
集合运算符(
union,intersect,minus
)DISTINCT
关键字GROUP BY
,ORDER BY
,CONNECT BY
或START WITH
子句子查询
分组函数
需要更新的列不是由“列表达式”定义的
基表中所有
NOT NULL
列均属于该视图
3.5 创建复杂视图
复杂视图定义:是指包含函数、表达式、或分组数据的视图。主要目的是为了简化查询。主要用于执行查询操作,并不用于执行 DML 操作。注意:当视图的 select 查询中包含函数或表达式时,必须为其定义列别名。示例 1:查询目前每个岗位的平均工资、工资总和、最高工资和最低工资。
3.6 强制创建视图
强制视图定义:正常情况下,如果基表不存在,创建视图就会失败。但是可以使用 force 选项强制创建视图(前提:创建视图的语句没有语法错误!),此时该视图处于失效状态。
四、更改视图
在对视图进行更改(或重定义)之前,需要考虑如下几个问题:
由于视图只是一个虚表,其中没有数据,所以更改视图只是改变数据字典中对该视图的定义信息,视图的所有基础对象都不会受到任何影响。
更改视图之后,依赖于该视图的所有视图和
PL/SQL
程序都将变为INVALID
(失效)状态。如果以前的视图中具有
with check option
选项,但是重定义时没有使用该选项,则以前的此选项将自动删除。
4.1 更改视图的定义
方法——执行create or replace view
语句。这种方法代替了先删除(“权限也将随之删除”)后创建的方法,会保留视图上的权限,但与该视图相关的存储过程和视图会失效。示例 1:将视图改为改为只读
4.2 视图的重新编译
语法:alter view 视图名 compile;
作用:当视图依赖的基表改变后,视图会“失效”。为了确保这种改变“不影响”视图和依赖于该视图的其他对象,应该使用 alter view 语句“明确的重新编译”该视图,从而在运行视图前发现重新编译的错误。视图被重新编译后,若发现错误,则依赖该视图的对象也会失效;若没有错误,视图会变为“有效”。
权限:为了重新编译其他模式中的视图,必须拥有 alter any table 系统权限。注意:当访问基表改变后的视图时,oracle 会“自动重新编译”这些视图。示例 1:
思考:若上述代码修改的不是列长,而是表名,结果又会如何?<警告:更改的视图带有编译错误;视图状态:失效>
五、删除视图
可以删除当前模式中的任何视图;如果要删除其他模式中的视图,必须拥有 DROP ANY VIEW 系统权限;视图被删除后,该视图的定义会从词典中被删除,并且在该视图上授予的“权限”也将被删除。视图被删除后,其他引用该视图的视图及存储过程等都会失效。示例 1:drop view vw_test_tab;
六、查看视图
使用数据字典视图
dba_views
——DBA 视图描述数据库中的所有视图all_views
——ALL 视图描述用户“可访问的”视图user_views
——USER 视图描述“用户拥有的”视图dba_tab_columns
——DBA 视图描述数据库中的所有视图的列(或表的列)all_tab_columns
——ALL 视图描述用户“可访问的”视图的列(或表的列)user_tab_columns
——USER 视图描述“用户拥有的”视图的列(或表的列)示例 1:查询当前方案中所有视图的信息
示例 2:查询当前方案中指定视图(或表)的列名信息
七、在视图上执行 DML 操作的步骤和原理
第一步:将针对视图的 SQL 语句与视图的定义语句(保存在数据字典中)“合并”成一条 SQL 语句;
第二步:在内存结构的共享 SQL 区中“解析”(并优化)合并后的 SQL 语句;
第三步:“执行”SQL 语句;
示例:假设视图 v_emp 的定义语句如下:
当用户执行如下查询语句时:
oracle 将把这条 SQL 语句与视图定义语句“合并”成如下查询语句:
然后,解析(并优化)合并后的查询语句,并执行查询语句;
7.1 查询视图“可更新”(包括“增删改”)的列
使用数据字典视图
dba_updatable_columns
——显示数据库所有视图中的所有列的可更新状态all_updatable_columns
——显示用户可访问的视图中的所有列的可更新状态user_updatable_columns
——显示用户拥有的视图中的所有列的可更新状态
示例:
版权声明: 本文为 InfoQ 作者【No Silver Bullet】的原创文章。
原文链接:【http://xie.infoq.cn/article/eb1c37285a7e8d8d7131c8190】。文章转载请联系作者。
评论