oracle 视图基础 - [ORACLE]

oracle 视图基础 - [ORACLE]
Tag：oracle 视图 insert update
版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明
http://xiekeli.blogbus.com/logs/86041573.html

最近在OCP学习中，发现对oracle的视图有些基础概念的盲点，甚为汗颜，习而补漏，不为晚矣；

视图的概念

视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同的角度来观察一个(或多个)表。
视图有如下一些优点：

可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。
简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。
一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。
视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。
视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。


视图的创建


创建视图需要CREAE VIEW系统权限，视图的创建语法如下：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名[(别名1[，别名2...])]
AS 子查询
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY]
其中：
OR REPLACE 表示替代已经存在的视图。
FORCE表示不管基表是否存在，创建视图。
NOFORCE表示只有基表存在时，才创建视图，是默认值。
别名是为子查询中选中的列新定义的名字，替代查询表中原有的列名。
子查询是一个用于定义视图的SELECT查询语句，可以包含连接、分组及子查询。
WITH CHECK OPTION表示进行视图插入或修改时必须满足子查询的约束条件。后面的约束名是该约束条件的名字。
WITH READ ONLY 表示视图是只读的。



创建只读视图

创建只读视图要用WITH READ ONLY选项。
创建只读视图。
步骤1：创建emp表的经理视图：

Sql代码

CREATE OR REPLACE VIEW manager  
AS SELECT * FROM emp WHERE job= 'MANAGER' 
WITH READ ONLY; 

执行结果：
视图已建立。
步骤2：进行删除：

Sql代码

DELETE FROM manager;

执行结果：
ERROR 位于第 1 行:
ORA-01752: 不能从没有一个键值保存表的视图中删除



创建基表不存在的视图


正常情况下，不能创建错误的视图，特别是当基表还不存在时。但使用FORCE选项就可以在创建基表前先创建视图。创建的视图是无效视图，当访问无效视图时，Oracle将重新编译无效的视图。
使用FORCE选项创建带有错误的视图：

Sql代码

CREATE FORCE VIEW v_test AS SELECT * FROM　test ;


执行结果：
警告: 创建的视图带有编译错误。



视图的操作


对视图经常进行的操作是查询操作，但也可以在一定条件下对视图进行插入、删除和修改操作。对视图的这些操作最终传递到基表。但是对视图的操作有很多限定。如果视图设置了只读，则对视图只能进行查询，不能进行修改操作。

插入视图的条件:
1.如果视图是基于一个基础表产生的,那么这就称为非连接视图,所有的非连接视图都是可以更新的,也就是说可以在该视图上进行,INSERT,UPDATE,DELETE的操作.  
2.如果是连接视图,那就要遵守基本更新准则了.现在我只对INSERT准则做一下说明:在INSERT语句中不能显式或隐式的引用到任何非码保留基础表中的字段,如果在定义视图中使用了WITH CHECK OPTION子句,那就不能对视图执行INSERT操作.

注:码保留表,非码保留表的解释:  
在DEPT中,DEPT_NO是主码,EMP中,EMP_NO是主码  
然后建立连接视图:  
create view emp_dept as  
select emp.emp_no,emp.emp_name,emp.dept_no,dept.name  
from emp,dept  
where emp.dept_no=dept.dept_no  
这个视图中,EMP_NO仍然充当主码,所以EMP为码保留表,而DEPT中的DEPT_NO就不是主码了,所以为非码保留表.

多张表关联后需要向视图插入数据，建议使用替代触发器

create trigger [触发器名]
on [视图名]
instead of insert
as
begin
--声明变量；
--从inserted表中查出所有列的数据，分别赋给声明好的变量；
--用上面的数据向第一张表插入数据
--用上面的数据向第二张表插入数据
end

此外通过user_updatable_columns表能够查询到该表或者视图是否可更新。



使用WITH CHECK OPTION选项


为了避免上述情况的发生，可以使用WITH CHECK OPTION选项。使用该选项，可以对视图的插入或更新进行限制，即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新。比如 “清华图书”视图的WHERE条件是出版社编号要等于“01”(01是清华大学出版社的编号)，所以如果设置了WITH CHECK OPTION选项，那么只有出版社编号为“01”的图书才能通过清华视图进行插入。
使用WITH CHECK OPTION选项限制视图的插入。
步骤1：重建清华大学出版社的图书视图，带WITH CHECK OPTION选项：

Sql代码

CREATE OR REPLACE VIEW 清华图书 

AS SELECT * FROM 图书 WHERE 出版社编号= '01'

WITH CHECK OPTION;



执行结果：
视图已建立。
步骤2：插入新图书：

Sql代码

INSERT INTO 清华图书 VALUES('A0006','Oracle数据库','02','黄河',3,39.8);


执行结果：
ERROR 位于第 1 行:
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句
说明：可见通过设置了WITH CHECK OPTION选项，“02”出版社的图书插入受到了限制。如果修改已有图书的出版社编号情况会如何？答案是将同样受到限制。要是删除视图中已有图书，结果又将怎样呢？答案是可以，因为删除并不违反WHERE条件。



删除视图
删除视图的语法如下：
DROP VIEW 视图名;
删除视图者需要是视图的建立者或者拥有DROP ANY VIEW权限。视图的删除不影响基表，不会丢失数据。