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 <= 5 order by sal desc;

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 <= 5

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 <=8

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

<=8的操作了。

注意:

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