Oracle 基础——视图

　　在视图上可以使用DML语句修改数据并且能更新对应基表的数据，如INSERT,UPDATE,DELETE。视图上的DML语句有如下限制：

　　(1)只能修改一个底层的基表数据。

　　(2)如果违反了基表的约束条件，则无法更新视图。

　　(3)如果视图包含连接符、DISTINCT关键字、集合操作符、聚合函数或GROUP BY子句，则无法更新视图。

二、什么是键保留表

　　在底层表查询中，如果使用了联接查询，则联接的表分为键保留表和非键保留表。并且更新视图是只能更新键保留表的列，不能更新非键保留表的列。

　　键保留表：如果某一个表的主键可以作为这个联接查询出来的结果(view通常是几个表的联结查询结果)的主键,那么这个表就是键保留表，这个表的主键并非一定要出现在SELECTt出来的结果集中，但是如果其出现在结果集中,那么它必须可以满足作为这个结果集的主键的要求。

　　最重要的一点是，通过DML操作联结查询出来的视图的时候，有一个很重要的条件是操作的基表必须是键保留表。

三、创建、删除SQL的语法

　　创建视图：CREATE [OR REPLACE][FORCE] VIEW view_name AS SELECT_STATEMENT [WITH CHECK OPTION][WITH READ ONLY]

　　删除视图：DROP VIEW view_name

　　参数说明：

　　(1)view_name：创建的视图名称。

　　(2)SELECT_STATEMENT：查询的SQL语句。

　　(3)FORCE：如果要查询的表不存在，则会创建视图失败。使用FORCE可以强制创建视图，即使要查询的表不存在。

　　(4)WITH CHECK OPTION：如果查询的SQL语句限制了WHERE等条件，使用WITH CHECK OPTION只能更新(INSERT,UPDATE,DELETE)符合WHERE等条件的数据。

　　(5)WITH READ ONLY：使用WITH READ ONLY表示创建的视图只能查询，不能更新(INSERT,UPDATE,DELETE)。

四、效果演示

　　(1)创建视图VIEW_1,当查询的表不存在时，创建视图失败。然而使用FORCE后，可以强制创建视图成功有警告信息罢了，但是这个视图不存在而已。SQL语句如下，效果如图4-1所示

1 CREATE VIEW VIEW_1 AS SELECT * FROM USER_INFO;
2 CREATE FORCE VIEW VIEW_1 AS SELECT * FROM USER_INFO;

　　　　　　　　　　　　　　　　　　　　　　　　　　　图 4-1

　　(2)创建视图VIEW_2,并从EMP_BK表查询出来结果集。SQL语句如下，效果如图4-2所示

1 CREATE VIEW VIEW_2 AS SELECT * FROM EMP_BK;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　图 4-2

　　(3)创建视图VIEW_3，从EMP_BK查询COMM不为空的结果集并加上WITH CHECK OPTION。当UPDATE视图时，把COMM更新成100时，报错误信息不能更新视图。SQL语句如下，效果如图4-3所示

1 CREATE VIEW VIEW_3 AS SELECT * FROM EMP_BK WHERE COMM IS NULL WITH CHECK OPTION;
2 
3 UPDATE VIEW_3 SET COMM = 200;
4 
5 COMMIT;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　图 4-3

　　(4)创建视图VIEW_4，从EMP_BK查询并加上WITH READ ONLY。当更新(UPDATE,INSERT,DELETE)时，报错不能更新视图。SQL语句如下，效果如图4-4所示

1 CREATE VIEW VIEW_4 AS SELECT * FROM EMP_BK WITH READ ONLY;
2 
3 DELETE FROM VIEW_4 WHERE EMPNO = 7566;
4 
5 COMMIT;

　　　　　　　　　　　　　　　　　　　　　　　　　　　图 4-4

　　(5)创建视图VIEW_5，表EMP_BK联结查询表DEPT_BK，并查询EMP_BK表中的EMPNO列、ENAME列、JOB列、DEPTNO列别名为DETPNO_E，DEPT_BK表中的DEPTNO列别名为DEPTNO_D、DNAME列、LOC列。SQL语句如下，查询出来的结果集如图4-5所示，从结果集来看EMPNO列能唯一标识结果集并且EMPNO是EMP_BK表的主键，所以EMP_BK是键保留表，DEPT_BK是非键保留表。当尝试更新非键保留表时，则报错不能更新非键保留表；反之更新键保留表时，则能成功更新视图，效果如图4-6所示

 1 CREATE VIEW VIEW_5 AS 
 2 SELECT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO DEPTNO_E,D.DEPTNO AS DEPTNO_D,D.DNAME,D.LOC
 3 FROM EMP_BK E 
 4 INNER JOIN DEPT_BK D 
 5 ON E.DEPTNO = D.DEPTNO;
 6 
 7 --查询视图
 8 SELECT * FROM VIEW_5;
 9 
10 --更新非键保留表，更新失败
11 UPDATE VIEW_5 SET LOC = ‘SHEN ZHEN‘ WHERE DEPTNO_D = 20;
12 
13 --更新键保留表，更新成功
14 UPDATE VIEW_5 SET JOB = ‘ENGINEER‘ WHERE EMPNO = 7369;
15 COMMIT;
16 
17 --EMPNO为7369的JOB发生了改变
18 SELECT * FROM VIEW_5 WHERE EMPNO = 7369;　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　图 4-5

　　　　　　　　　　　　　　　　　　　　　　　　　　 图 4-6

Oracle 基础——视图

标签：失败   不为   tno   user   隐藏   结构   col   engine   dep