视图用法详解

视图

OCP试题用到结论：

在创建视图时，对这种使用各种函数，或运算表达式的列，一定要起别名，如没有别名视图创建就会失败。

定义视图的列名可以省略，来自SELECT定义中的列名，如果定义视图的列名不省略，那个列的个数与SELECT定义中的列的个数要一致。

一、什么是视图：
  在ORACLE中，称视图为逻辑表。逻辑二字还是说明了视图不是真正的表。因为视图内根本就没有数据。视图的全部家当，也就是你创建视图时的SELECT语句。


二、创建视图：

1、创建视图的主法是：

 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  [(alias[, alias]...)]
 AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

2、例如，如下语句就创建了一个视图：

hr@OCM> CREATE VIEW     empvu30
  2   AS SELECT  employee_id, last_name, salary
  3      FROM    employees
  4      WHERE   department_id = 30;

View created.

视图的名字是empvu30。那它有什么用呢？很简单，如果以后你再想查看department_id（部门编号）为30的雇员信息，不必再使用“ SELECT  employee_id, last_name, salary FROM    employees WHERE   department_id = 30 ”，直接SELECT 视图即可：

hr@OCM> select * from empvu30;


EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        145 Russell                        14000
        146 Partners                       13500
        147 Errazuriz                      12000
        148 Cambrault                      11000
        149 Zlotkey                        10500

        150 Tucker                         10000

这样比你每次输入department_id = 30简单一些。我们可以将常用的比较复杂的查询操作像上面这样，建立为视图。这样每次查询视图就可看到结果，比输入复杂查询语句更省事。

 3、创建视图命令中的[OR REPLACE]的意义，REPLACE有替换的意思。它的主要作用是修改视图的定义，也就是修改视图内的SQL语句。比如说上面的empvu30，我想为empvu30中增加一个列first_name，命令如下：

hr@OCM>  CREATE OR REPLACE VIEW     empvu30
  2   AS SELECT  employee_id, first_name,last_name, salary
  3       FROM    employees
  4       WHERE   department_id = 30;


View created.

4、在创建视图时，对这种使用各种函数，或运算表达式的列，一定要起别名，如没有别名视图创建就会失败，例如：

   hr@OCM> CREATE OR REPLACE VIEW     empvu30 
  2     AS SELECT  department_id,count(*)
  3      FROM    employees
  4      GROUP BY department_id;
   AS SELECT  department_id,count(*)
                            *
ERROR at line 2:
ORA-00998: must name this expression with a column alias

根据Oracle的报错，给count(*)取个别名为count，例如：

hr@OCM> CREATE OR REPLACE VIEW     empvu30 
  2    AS SELECT  department_id,count(*) count
  3    FROM    employees
  4    GROUP BY department_id;


View created.

用desc查看视图empvu30的结构

hr@OCM> desc empvu30
 Name            Null?    Type
 -----------------------------

 DEPARTMENT_ID       NUMBER(4)  

  COUNT               NUMBER

 如果你没有指定别名的话，ORACLE将无法确定在视图中，它的列名是什么。因此，在创建视图时，对于这种复杂的表达式，一定要为它起别名。            

  

 5、当使用SELECT查询一个视图时，和查询表是一某一样的，我们就可以把视图当表一样去查询。SELECT语句中，所有适用于表特性，都同样适用性视图。比如我们可以增长条件：

hr@OCM> select * from empvu30  where department_id=30;


DEPARTMENT_ID      COUNT
------------- ----------
           30          6


显示empvu30视图中，部门编号（department_id)等于30的行。在使用这个命令时，我们完全可以把empvu30当作一个表，它有1行，两列，列名分别是DEPARTMENT_ID和COUNT。视图数据的来源，被称为视图的“基表”。视图的基表可以有多个。如果我根据连接命令创建一个视图，那么这个视图中的数据可能来自于多个表，这样它就有了多个基表。

基表中的数据变化时，视图也会跟着发生变化。例如，在EMPLOYEES表中我再向插入一行部门编号（department_id)等于30的部门新员工：

hr@OCM> insert into employees values(888,'Joe','guo','Joe@oracle.com',1388888888,sysdate,'AD_PRES',50000,null,200,30);


1 row created.


hr@OCM> commit;


Commit complete.

加了一行后，下面我再显示一下视图：

hr@OCM> select * from empvu30  where department_id=30;


DEPARTMENT_ID      COUNT
------------- ----------

           30          7

部门号为30号的现在有7个人了。也就是说，基表发生了什么变化，不需要任何操作，视图马上就可以反应出来。
这就是视图，我们先不深入的讨论它，仅从外观看，是看不出它和表有什么区别的。在SELECT时，你根据看不出来你SELECT的是一个表，还是一个视图。其实很多时候，我们都在SELECT视图，但是我们都以为在SELECT表。

我们还可以以视图为基表，再创建视图，比如上面的例子，我想把显示结果中的部门号和人数和DEPARTMETS表等值连接起来显示部门名称：

hr@OCM> select a.DEPARTMENT_ID,DEPARTMENT_NAME,COUNT from empvu30 a,departments b  where a.DEPARTMENT_ID=b.DEPARTMENT_ID and a.department_id=30;


DEPARTMENT_ID DEPARTMENT_NAME                     COUNT
------------- ------------------------------ ----------

           30 Purchasing                              7

连接的命令还是稍有点长的，下面我根据此命令，再创建一个视图：

hr@OCM> CREATE OR REPLACE VIEW v_emp30 as 
  2  select a.DEPARTMENT_ID,DEPARTMENT_NAME,COUNT 
  3  from empvu30 a,departments b
  4  where a.DEPARTMENT_ID=b.DEPARTMENT_ID and a.department_id=30;

View created.

视图的名字是v_emp30，名字是根据需要随变定的，如果觉得我这个复杂，可以换成简单的，主要是我想当我看到这个名字时，就能知道这个视图涉及两个表EMPLOYEES和DEPARTMETNS。

好了，下面我们显示一下这个视图，它的输出结果，将和上面的那条连接SELECT语句一样：

hr@OCM> SELECT * FROM  v_emp30;


DEPARTMENT_ID DEPARTMENT_NAME                     COUNT
------------- ------------------------------ ----------
           30 Purchasing                              7


这样，以后我想查看30号部门的总人数。直接查看这个视图就行了。
这个视图的基表有DEPARTMETNS表和empvu30视图，而empvu30的基表是EMPLOYEES表。也就是说一个视图可以作为其它视图的基表。再强调一下，在进行SELECT操作时，我们可以把视图当作表一样去用，我们干脆暂时称视图为视图表。我们可以认为视图表的数据，来自于基表。那么，既然视图是表了，我基于这个表再创建其它视图当然是没有问题的。


三、视图的特点：
1、简化查询操作，一个视图可能代表一串非常复杂的查询操作。
 我们可以将经常用到的多表联合查询出来的数据，或特定的结果集定义为视图，这样就起到了模块化数据的作用。我们在使用这些数据时直接查询该视图就可以，而不用到处写长长的SQL语句，这样也起到易维护的作用。
 2、限制数据访问。
 比如：对于不同的用户，我们只提供部分数据给他。这样，我们就可以在视图中限定结果集，然后返回该视图给他。这样，无论用户怎么对视图定义查询条件，他也不能查询出我们不想提供给他的数据。
 3、相同的数据不同的展现。
 报表数据的展现
 4、提供特定的数据。
 视图可以让用户或者程序开发人员只看到他们所需要的数据，而不需要把表中的所有信息与字段暴露出来，这样增强了数据的安全性。


四、视图的分类
ORACLE把视图分为两类，简单视图与复杂视图，如下：

特性	简单视图	复杂视图
表的数目	一个	多个
是否包含函数	否	是
是否分组	否	是

我们上面的例子中的empvu30就是一个简单视图，而v_emp30肯定就是一个复杂视图了。
通常，我们可以对简单视图使用DML命令，而复杂视图则不能使用DML命令，只能查询。不过从9i开始，对于基于多表的连接创建的视图，虽然也是复杂视图，但是也可以对它进行DML操作了。
下面我们来看一下对视图进行DML时的情况。


五、视图与DML

视图只是一条SQL语句，在查询视图时，ORACLE会把查询语句与视图内的SQL语句互相融合。如果是对执行DML操作，ORACLE照样会将视图的SQL语句与DML命令相融合，例如，我们更新视图empvu30：

hr@OCM> CREATE VIEW     empvu30
  2  AS SELECT  employee_id, last_name, salary
  3   FROM    employees
  4  WHERE   department_id = 30;


View created.


hr@OCM> update empvu30 set salary=salary+100;


7 rows updated.


hr@OCM> commit;


Commit complete.


这条语句的执行步骤和上面的查询差不多，ORACLE先将视图empvu30换为它的SQL：
update （SELECT  employee_id, last_name, salary FROM    employees WHERE   department_id = 30）set  salary=salary+100;
对于这样的DML语句，不应该陌生，在第七讲“操纵数据”中我们讲过。也就是说，对视图的更新，ORACLE会将它转化为其基表的更新。因为视图中又没有数据，数据都在基表中，更新视图，最终还是要更新基表。
   在对视图进行DML时，ORACLE要把你的DML转化成对基表的DML。如果转化操作ORACLE无法完成，对视图的DML就会失败。例如：

我实验一个包含GROUP BY、组函数的视图。视图的创建SQL语句如下：

hr@OCM>  CREATE OR REPLACE VIEW    v_emp_depart
  2    AS SELECT  department_id,count(*) count
  3    FROM    employees
  4    GROUP BY department_id;


View created.


hr@OCM> update v_emp_depart set count=8 where department_id=60;
update v_emp_depart set count=8 where department_id=60
       *
ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

凡是这样带有要把基表中多行合为一行的视图，改变视图中的一行，ORACLE都无法确定这一行对应基表中几行，这样的视图，都无法进行任何DML操作。会对多行进行合并的，也就只有分组、组函数和取消重复行DISTINCT选项。DISTINCT是取消重复行，其实就是将多个重复的行，合为一个，在视图中的一行，也有可能对应基表的中的若干行。 除了这种情况之外，其他类型的视图，至少都可以进行某种DML操作。例如，当非空列不在视图中时，不能向视图插入行。但可以更新或删除。
这一点是为什么？考虑一下！其实非常简单，如果表有A、B、C三列，其中C列有非空约束。视图甲包含表的A、B两列。向视图中插入行，其实就是向表插入行，但只对表的A、B两列赋值，因为C列不包含在视图中，因此向视图中插入行，不会对表C的赋值。而C列是非空的。这就会引起违返非空约束的错误。
不过此进对视图的更新和删除是可以的。
通过这一点，又为我们提个醒，把我上面说过的话，进一步完善一下：“在我们对视图作DML时，要考虑一下ORACLE是否能把此DML转化成针对基表的DML。如果不行的话，对视图的DML操作就会失败”。再加一句，“还要考虑对视图的DML在转化为对基表的DML后，是否会违反基表的约束，如果有违反，对视图的DML同样会失败”。
对什么样的视图可以进行什么样的DML，是很灵活的，我们掌握总的规则就行，这总的规则就是我们刚才所说的：“在我们对视图作DML时，要考虑一下ORACLE是否能把此DML转化成针对基表的DML。如果不行的话，对视图的DML操作就会失败。还要考虑对视图的DML在转化为对基表的DML后，是否会违反基表的约束，如果有违反，对视图的DML同样会失败”
最后还有一点，就是ORACLE中有两点硬性规则，视图中如果包含这两点，在对视图进行DML时，也会有所限制。所谓硬性规则，也就是ORACLE的中规定，不需要原因。这两点硬性规定是：

1、当视图中含有ROWNUM时，视图不能进行任何的DML。

gyj@OCM>  create or replace view v_t3_rn as select rownum rn,id,name,salary from t3;


View created.


gyj@OCM> select * from v_t3_rn;


        RN         ID NAME           SALARY
---------- ---------- ---------- ----------
         1          1 gyj1             5000
         2          1 gyj11            5000
         3          2 gyj2             6000
         4          2 gyj22            6000
         5          3 gyj3             7000
 
（1）update操作，报错：

gyj@OCM> update v_t3_rn set rn=10 where rn=1;
update v_t3_rn set rn=10 where rn=1
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


（2）delete操作，报错：

gyj@OCM> delete from v_t3_rn where rn=1;
delete from v_t3_rn where rn=1
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


（3）insert操作，报错

gyj@OCM> insert into v_t3_rn values(10,10,'gyj10',9000);
insert into v_t3_rn values(10,10,'gyj10',9000)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

这就是ROWNUM，它像列一样，你可以显示它，但它并不是真正意义上的列。它的值是在你输出表数据时，临时加上的。从表中输出的第一行，它的ROWNUM值是1，以后每从表中输出一行，ROWNUM的值加1。

2、视图中含有运算表达式时，对运算表达式的列，不能更新。不能插入新行，但可以删除行，例如：

     如下创建视图：

     gyj@OCM> select * from t2;


        ID        AGE NAME
   ---------- ---------- --------------------
         1         30 Joe


    创建一个视图v_t2
    gyj@OCM> create view v_t2 as select id,age+10 age from t2;


    View created.


    可以如下删除行：


   gyj@OCM>   delete from v_t2;


    1 row deleted.


   删除后，基表中的数据都没有了:
    gyj@OCM> select * from t2;


    no rows selected
   
    gyj@OCM>  rollback;


    Rollback complete.


    不能插入新行：


   gyj@OCM> insert into v_t2 values(2,28);
insert into v_t2 values(2,28)
*
ERROR at line 1:

ORA-01733: virtual column not allowed here

 不能更新带有运算表达式的表，也叫虚拟列
 

   但可以更新其他不包含运算表达式的列：

    gyj@OCM> update v_t2 set id=2 where id=1;


1 row updated.


gyj@OCM> rollback;


Rollback complete.


3、只读视图

 有些视图并不希望用户通过它更改基表中的行，可以把这些视图建为只读视图：命令如下：

 gyj@OCM>  create or replace view v_t2 as select id,age,name from t2 with read only;


 View created.
 
 在创建视图命令的最后，加上WITH READ ONLY，就是将视图创建为只读视图。这样，无论对视图进行什么DML操作，都不能成功：

 （1）update操作，报错：

gyj@OCM>  update v_t2 set age=age+1 where id=1;
 update v_t2 set age=age+1 where id=1
                 *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

（2）insert操作，报错

gyj@OCM>  insert into v_t2 values(2,28,'Tom');
 insert into v_t2 values(2,28,'Tom')
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

（3）delete操用，报错

gyj@OCM> delete from v_t2;
delete from v_t2
            *
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view




4、WITH CHECK OPTION
 还记得这个选项吗，在第七章有过讲述。如果视图中有条件，此选项保证了你只能在视图的条件之内，对视图进行DML。
 例如我创建如下视图：
gyj@OCM> create or replace view v_t3 as select id, name,salary from t3 where salary>=7000 with check option;


View created.

视图中的条件是salary>=7000，因此，我插入一个要salary小于7000的行，将会报出错误：

gyj@OCM> insert into v_t3 values(5,'gyj5',1000);
insert into v_t3 values(5,'gyj5',1000)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

 而插入一个大于7000的行，可以成功插入：

gyj@OCM> insert into v_t3 values(5,'gyj5',7500);

1 row created.

   其他的更新、删除我就不再试了，只要DML的结果满足SALARY大于等7000，DML就可以正常进行。


六、删除视图
 没有用的视图可以删掉，命令非常简单：


gyj@OCM> drop view v_t3;


View dropped.


gyj@OCM> select * from v_t3;
select * from v_t3
              *
ERROR at line 1:
ORA-00942: table or view does not exist


gyj@OCM> select * from t3;


        ID NAME           SALARY
---------- ---------- ----------
         1 gyj1             5000
         1 gyj11            5000
         2 gyj2             6000
         2 gyj22            6000
         3 gyj3             7000
         3 gyj33            7000
         4 gyj4             8000
         4 gyj44            8000
         5 gyj5             7500


9 rows selected.


视图被删除了，但视图所对应的基表还在。

ORACLE中DROP 命令专门用来删除各种对象。DROP TABLE是删除表。DROP VIEW是删除视图，我们以后，还会用到其他的删除对象命令。

七、视图的缺点
虽然视图可以给我们带来种种便利，但不意味着我们就可以滥用它。因为视图其实就是一段SQL语句，所以它的结果都是每次调用时动态生成的。如果不合理的定义视图，必然带来性能上的损耗。
下面是我们在创建视图应该要注意的几点：
1、操作视图会比直接操作基础表要慢，所以我们尽量避免在大型表上创建视图。
2、尽量不要创建嵌套视图，就是在视图中使用视图。 这样在查询时，会多次重复访问基础表，带来性能损耗。
3、尽量在视图只返回所需的信息，尽量不要在视图使用不需要访问的表。
4、在大型表或者复杂定义的视图，可以使用存储过程代替。
5、频繁使用的视图，可以使用索引视图来代替。