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 表示视图是只读的。 
删除视图的语法如下： 
DROP VIEW 视图名; 
删除视图者需要是视图的建立者或者拥有DROP ANY VIEW权限。视图的删除不影响基表，不会丢失数据。 
1．创建简单视图  
创建图书作者视图。 
步骤1：创建图书作者视图： 

Sql代码  

1. CREATE VIEW 图书作者(书名,作者)   
2.         AS SELECT 图书名称,作者 FROM 图书;  

输出结果： 
视图已建立。 
步骤2：查询视图全部内容 

Sql代码  

1. SELECT * FROM 图书作者;  

输出结果： 

Sql代码  

1. 书名                           作者  
2.         -------------------------------- --------------------  
3.         计算机原理                     刘勇  
4.          C语言程序设计                马丽  
5.         汇编语言程序设计               黄海明  

步骤3：查询部分视图： 

Sql代码  

1. SELECT 作者 FROM 图书作者;  

输出结果： 

Sql代码  

1. 作者  
2.         ----------  
3.         刘勇  
4.         马丽  
5.         黄海明  

说明：本训练创建的视图名称为“图书作者”，视图只包含两列，为“书名”和“作者”，对应图书表的“图书名称”和“作者”两列。如果省略了视图名称后面的列名，则视图会采用和表一样的列名。对视图查询和对表查询一样，但通过视图最多只能看到表的两列，可见视图隐藏了表的部分内容。 
创建清华大学出版社的图书视图。 
步骤1：创建清华大学出版社的图书视图： 

Sql代码  

1. CREATE VIEW 清华图书   

AS SELECT 图书名称,作者,单价 FROM 图书 WHERE 出版社编号= '01'; 
执行结果： 
视图已建立。 
步骤2：查询图书视图： 

Sql代码  

1. SELECT * FROM 清华图书;  

执行结果： 

Sql代码  

1. 图书名称                       作者             单价  
2. -------------------------------------------- ---------- -----------------------  
3.         计算机原理                     刘勇             25.3  

步骤3：删除视图： 

Sql代码  

1. DROP VIEW 清华图书;  

执行结果： 
视图已丢掉。  
说明：该视图包含了对记录的约束条件。 
2．创建复杂视图 
修改作者视图，加入出版社名称。 
步骤1：重建图书作者视图： 

Sql代码  

1. CREATE OR REPLACE VIEW 图书作者(书名,作者,出版社)   
2.         AS SELECT 图书名称,作者,出版社名称 FROM 图书,出版社   
3.         WHERE 图书.出版社编号=出版社.编号;  

输出结果： 
视图已建立。 
步骤2：查询新视图内容： 

Sql代码  

1. SELECT * FROM 图书作者;  

输出结果： 

Sql代码  

1. 书名                           作者       出版社  
2.         -------------------------------------------- ---------- ----------------------------  
3.         计算机原理               刘勇       清华大学出版社  
4.         C语言程序设计         马丽       电子科技大学出版社  
5.         汇编语言程序设计     黄海明     电子科技大学出版社  

说明：本训练中，使用了OR REPLACE选项，使新的视图替代了同名的原有视图，同时在查询中使用了相等连接，使得视图的列来自于两个不同的基表。 
创建一个统计视图。 
步骤1：创建emp表的一个统计视图： 

Sql代码  

1. CREATE VIEW 统计表(部门名,最大工资,最小工资,平均工资)  
2.         AS SELECT DNAME,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP E,DEPT D  
3.         WHERE E.DEPTNO=D.DEPTNO GROUP BY DNAME;  

执行结果： 
视图已建立。 
步骤2：查询统计表： 

Sql代码  

1. SELECT * FROM 统计表;  

执行结果： 

Sql代码  

1. 部门名       最大工资    最小工资    平均工资  
2.         -------------------------- --------------- ----------------- ------------------  
3.         ACCOUNTING          5000       1300        3050  
4.         RESEARCH             3000        800        2175  
5.         SALES                  2850        950   1566.66667  

说明：本训练中，使用了分组查询和连接查询作为视图的子查询，每次查询该视图都可以得到统计结果。 
创建只读视图 
创建只读视图要用WITH READ ONLY选项。 
创建只读视图。 
步骤1：创建emp表的经理视图： 

Sql代码  

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

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

Sql代码  

1. DELETE FROM manager;  

执行结果： 
ERROR 位于第 1 行: 
ORA-01752: 不能从没有一个键值保存表的视图中删除 
4．创建基表不存在的视图 
正常情况下，不能创建错误的视图，特别是当基表还不存在时。但使用FORCE选项就可以在创建基表前先创建视图。创建的视图是无效视图，当访问无效视图时，Oracle将重新编译无效的视图。 
使用FORCE选项创建带有错误的视图： 

Sql代码  

1. CREATE FORCE VIEW 班干部 AS SELECT * FROM　班级 WHERE 职务 IS NOT NULL;  

执行结果： 
警告: 创建的视图带有编译错误。 
视图的操作 
对视图经常进行的操作是查询操作，但也可以在一定条件下对视图进行插入、删除和修改操作。对视图的这些操作最终传递到基表。但是对视图的操作有很多限定。如果视图设置了只读，则对视图只能进行查询，不能进行修改操作。 
1．视图的插入  
视图插入练习。 
步骤1：创建清华大学出版社的图书视图： 

Sql代码  

1. CREATE OR REPLACE VIEW 清华图书   
2.         AS SELECT * FROM 图书 WHERE 出版社编号= '01';  

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

Sql代码  

1. INSERT INTO 清华图书 VALUES('A0005','软件工程','01','冯娟',5,27.3);  

执行结果： 
已创建 1 行。 
步骤3：显示视图： 

Sql代码  

1. SELECT * FROM 清华图书;  

执行结果： 

Sql代码  

1. 图书  图书名称                       出 作者             数量       单价  
2.         -------- ---------------------------------------- ----------- -------- ------------------------ --------------  
3.         A0001 计算机原理                     01 刘勇                5       25.3  
4. A0005 软件工程                       01 冯娟                5       27.3  

步骤4：显示基表 

Sql代码  

1. SELECT * FROM 图书;  

执行结果： 

Sql代码  

1. 图书  图书名称         出 作者       数量       单价  
2.         -------- ------------------------------------------ ------- ---------------- ----------------- ---------------  
3.         A0001 计算机原理           01 刘勇        5        25.3  
4.         A0002  C语言程序设计        02 马丽    1       18.75  
5.         A0003 汇编语言程序设计     02 黄海明       15       20.18  
6.         A0005 软件工程              01 冯娟        5        27.3  

说明：通过查看视图，可见新图书插入到了视图中。通过查看基表，看到该图书也出现在基表中，说明成功地进行了插入。新图书的出版社编号为“01”，仍然属于“清华大学出版社”。 
但是有一个问题，就是如果在“清华图书”的视图中插入其他出版社的图书，结果会怎么样呢？结果是允许插入，但是在视图中看不见，在基表中可以看见，这显然是不合理的。 
2．使用WITH CHECK OPTION选项 
为了避免上述情况的发生，可以使用WITH CHECK OPTION选项。使用该选项，可以对视图的插入或更新进行限制，即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新。比如“清华图书”视图的WHERE条件是出版社编号要等于“01”(01是清华大学出版社的编号)，所以如果设置了WITH CHECK OPTION选项，那么只有出版社编号为“01”的图书才能通过清华视图进行插入。 
使用WITH CHECK OPTION选项限制视图的插入。 
步骤1：重建清华大学出版社的图书视图，带WITH CHECK OPTION选项： 

Sql代码  

1. CREATE OR REPLACE VIEW 清华图书   
2.         AS SELECT * FROM 图书 WHERE 出版社编号= '01'  
3.         WITH CHECK OPTION;  

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

Sql代码  

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

执行结果： 
ERROR 位于第 1 行: 
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句 
说明：可见通过设置了WITH CHECK OPTION选项，“02”出版社的图书插入受到了限制。如果修改已有图书的出版社编号情况会如何？答案是将同样受到限制。要是删除视图中已有图书，结果又将怎样呢？答案是可以，因为删除并不违反WHERE条件。 
3．来自基表的限制 
除了以上的限制，基表本身的限制和约束也必须要考虑。如果生成子查询的语句是一个分组查询，或查询中出现计算列，这时显然不能对表进行插入。另外，主键和NOT NULL列如果没有出现在视图的子查询中，也不能对视图进行插入。在视图中插入的数据，也必须满足基表的约束条件。 
基表本身限制视图的插入。 
步骤1：重建图书价格视图： 

Sql代码  

1. CREATE OR REPLACE VIEW 图书价格   
2.         AS SELECT 图书名称,单价 FROM 图书;  

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

Sql代码  

1. INSERT INTO 图书价格 VALUES('Oracle数据库',39.8);  

执行结果： 
ERROR 位于第 1 行: 
ORA-01400: 无法将 NULL 插入 ("SCOTT"."图书"."图书编号") 
说明：在视图中没有出现的基表的列，在对视图插入时，自动默认为NULL。该视图只有两列可以插入，其他列将默认为空。插入出错的原因是，在视图中不能插入图书编号，而图书编号是图书表的主键，是必须插入的列，不能为空，这就产生了矛盾。 
视图的查看 
USER_VIEWS字典中包含了视图的定义。 
USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、删除。 
USER_OBJECTS字典中包含了用户的对象。 
可以通过DESCRIBE命令查看字典的其他列信息。在这里给出一个训练例子。 
查看清华图书视图的定义： 

Sql代码  

1. SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME='清华图书';  

执行结果： 

Sql代码  

1. TEXT  
2.         -----------------------------------------------------------------------------------------------  
3.         SELECT 图书名称,作者,单价 FROM 图书 WHERE 出版社编号='01'   

查看用户拥有的视图： 

Sql代码  

1. SELECT object_name FROM user_objects WHERE object_type='VIEW';  

执行结果： 

Sql代码  

1. OBJECT_NAME  
2.         ----------------------------------------------------------------------------------------------  
3.         清华图书  
4.         图书作者