Oracle 学习笔记 11

本次必须学习一个全新的概念-- 视图 (VIEW)。在前面的笔记中曾提到过，数据对象包括：表、视图、序列、索引和同义 词。前面的笔记都是对表的想剖析，那么本次笔记就对视图的世界进行深入的剖析。 视图是通过对一个表或者是多个表查询得到的，是从表中抽出的

本次必须学习一个全新的概念-- 视图 (VIEW)。在前面的笔记中曾提到过，数据对象包括：表、视图、序列、索引和同义词。前面的笔记都是对表的想剖析，那么本次笔记就对视图的世界进行深入的剖析。

      视图是通过对一个表或者是多个表查询得到的，是从表中抽出的逻辑上相关的数据集合。但是视图是一种虚表，它建立在已经存在表的基础之上，也可以理解为表中导出的“表”。视图定义所依据的表称为基表，有点类似与JAVA中的父类(基类)。

     利用视图可以控制对数据的访问，简化查询，可以避免直接对数据库中真实存在的表进行直接访问，提高数据库的安全性。

创建视图

 创建视图语法格式：    

CREATE [OR ERPLACE ]  VIEW   view_name   

as  子查询 [WITH READ ONLY] | [WITH CHECK OPTION]；

子查询可以是复杂的语句，如多个表之间的连接，子查询可以使用单行函数。可以通过给子查询中的列定义别名

来为视图的列定义成想要的名字。

例：创建视图，依赖emp表。

SQL> create view emp_view 
  2  as select empno ,ename ,sal from emp ;
 
View created

在用soctt用户执行上述语句的时候，可能会提示，没有创建视图的权限。可用下面的操作解决。

Oracle - OraClient10g_home1 --> Oracle EnterPrise Manager Console -->连接身份DBA

-->安全性-->用户-->scott --> 双击-->系统 --> 添加create any view 权限-->确定

现在对视图进行查询。

SQL> select * from  emp_view ;
 
EMPNO ENAME            SAL
----- ---------- ---------
 7369 SMITH         800.00
 7499 ALLEN        1600.00
 7521 WARD         1250.00
 7566 JONES        2975.00
 7654 MARTIN       1250.00
 7698 BLAKE        2850.00
 7782 CLARK        2450.00
 7788 SCOTT        3000.00
 7839 KING         5000.00
 7844 TURNER       1500.00
 7876 ADAMS        1100.00
 7900 JAMES         950.00
 7902 FORD         3000.00
 7934 MILLER       1300.00
 
14 rows selected

emp_view视图定义之后，在此创建emp_view 视图时，会提示名称已经由现有对象使用，说明视图的名称不

允许重复。如果想依然使用emp_view视图，可以使用 ：

create or replace view emp_view as select empno ,ename ,sal from emp 。虽然已经创

建了emp_view语句，仍然可以创建成功。系统会删除emp_view视图，在创建一个新的视图。对视图仍然可以

进行相关的插入更新操作，对视图的操作都会返回到基表中，和直接对表的操作效果一样。

例：对视图进行插入删除操作。将ename为JAMES的工资改为2000 ，插入一条新的数据。

SQL> update emp_view set sal = 2000 where ename = 'JAMES' ;

SQL> insert into emp_view values(7935 , 'zhangsan' , 5000);

下面看一下操作结果：

SQL> select * from emp_view where ename = 'JAMES' ;
 
EMPNO ENAME            SAL
----- ---------- ---------
 7900 JAMES        2000.00
 
SQL> select * from emp  where ename = 'JAMES' ;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 1981/12/3     2000.00               30

SQL> select * from emp_view  where ename='zhangsan' ; 
 
EMPNO ENAME            SAL
----- ---------- ---------
 7935 zhangsan     5000.00
 
SQL> select * from emp where ename='zhangsan';
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7935 zhangsan                                 5000.00           

上面使用DML语句进行了插入和更新操作。但是视图使用DML时还有一些特别的规定：

当视图定义中包含以下元素时，不能使用delete：

组函数、GROUP BY 、DISTINST 和 ROWNUM 伪列。

当视图定义中包含以下元素时，不能使用update：

组函数、GROUP BY 、DISTINST 、 ROWNUM 伪列和列的定义表达式。

当视图定义中包含以下元素时，不能使用insert：

组函数、GROUP BY 、DISTINST 、 ROWNUM 伪列、列的定义表达式和表中非空的列在视图中未包括。

如果创建的视图只是为了让人浏览的内容而不能修改或者插入新的数据的话，那么需要在创建视图的时候添加限制(with read only) 只读的 。

create or replace view emp_view
as select empno ,ename ,sal from emp 
with read only

此时在执行update语句 update emp_view set sal = 2000 where ename = 'JAMES'，会出现
ORA-01733: 此处不允许虚拟列提示 。

with check option ：不能更新视图的创建条件。此处不在赘述。

前面在执行多表查询的时候，有这样的一条语句，将emp表和dept表进行联立。

select empno  ,ename , dname ,e.deptno
from emp e , dept d 
where d.deptno  = e.deptno 

EMPNO ENAME      DNAME          DEPTNO
----- ---------- -------------- ------
 7369 SMITH      RESEARCH           20
 7499 ALLEN      SALES              30
 7521 WARD       SALES              30
 7566 JONES      RESEARCH           20
 7654 MARTIN     SALES              30
 7698 BLAKE      SALES              30
 7782 CLARK      ACCOUNTING         10
 7788 SCOTT      RESEARCH           20
 7839 KING       ACCOUNTING         10
 7844 TURNER     SALES              30
 7876 ADAMS      RESEARCH           20
 7900 JAMES      SALES              30
 7902 FORD       RESEARCH           20
 7934 MILLER     ACCOUNTING         10
 
14 rows selected

如果在开发的过程中，要多次使用上面的多表连接的语句，那么肯定是非常麻烦的。所以此时可以将上面多表查

询的结果建立成一个试图。再次使用的时候就轻松多了 。

create view view_emp
as
select empno  ,ename , dname ,e.deptno
from emp e , dept d 
where d.deptno  = e.deptno 

当执行select * from view_emp ;语句时，与上面的多表查询的效果是一样的。使用试图可以简化操作。

删除视图

基本语法：

DROP VIEW view_name ;

删除视图只是删除用户自己创建的视图，不会对基表中的数据产生影响。

例：删除上面定义的视图emp_view 。

drop view emp_name ;

 

Rownum 伪列

    上面提到了一个新的知识点rownum 伪劣，下面进行具体的解释一下。rownum 有点类似与excel中的表示行

的序号。实际上这是一个列，伪劣，可以在每张表中出现。

例：查询表emp中的rownum 。

SQL> select rownum , empno from emp ;
 
    ROWNUM EMPNO
---------- -----
         1  7369
         2  7499
         3  7521
         4  7566
         5  7654
         6  7698
         7  7782
         8  7788
         9  7839
        10  7844
        11  7876
        12  7900
        13  7902
        14  7934
        15  7935
 
15 rows selected

如果现在想要查询工资从高到低的前五条的数据，那么

SQL> select rownum , empno,sal from emp where rownum  
    ROWNUM EMPNO       SAL
---------- ----- ---------
         1  7935   5000.00
         5  7566   2975.00
         3  7499   1600.00
         4  7521   1250.00
         2  7369    800.00

观察上面的数据，虽然是按照sal从高到低的排序，但是却不是我们想高的最高的五条数据的排序。其实rownum和empno之间有某种关系，想要打破这中关系，可以使用下面的语句。

select rownum , empno,sal
from(
   select rownum , empno,sal
    from emp order by sal desc
)
where rownum

 

    ROWNUM EMPNO       SAL
---------- ----- ---------
         1  7935   5000.00
         2  7839   5000.00
         3  7788   3000.00
         4  7902   3000.00
         5  7566   2975.00

上面的查询结果才是我们想要的结果。

如果我们想要查询3到8之间的数据呢 ？可以使用下面的数据

select rn ,empno ,sal from(
  select rownum rn , empno,sal
  from(
    select rownum , empno,sal
    from emp order by sal desc
  )
)
where rn >=3 and rn

 

 RN        EMPNO       SAL
------ ----- ---------
     3  7902   3000.00
     4  7788   3000.00
     5  7566   2975.00
     6  7698   2850.00
     7  7782   2450.00
     8  7900   2000.00
 
6 rows selected

              上面给rownum 起了一个别名，将这个伪劣化为了一个实实在在存在的列，便可以进行 where rn > = 3 和 rn

    操作了。

注意:

对 ROWNUM只能使用 或 而用=, >, >= 都将不能返回任何数据。