oracle 视图使用rownum,Oracle数据对象--视图

1、为什么使用视图(1)限制数据的存取：用户只能看到基表的部分信息。

(2)使得复杂的查询变得容易：视图中的数据可能来自多个表。

(3)使得数据相对比较独立：从项目开发的角度，模块对应视图，模块包含多个表，模块发生变化后只需修改相应的视图，对应的表的结构无需修改。

(4)代表对同一数据的不同视角：不同部门的员工只能看到本部门的信息。

2、创建视图CREATE VIEW empvu10

AS SELECT empno, ename,

job

FROM emp1

WHERE deptno=30

/ (定义视图的查询中不能使用ORDER BY子句)

3、查询视图视图定义的SELECT语句保存在LONG型的TEXT字段中。

使用SET LONG

150增加LONG型最大显示宽度，默认为80

select * from user_views

/

语法说明

CREATE [OR

REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]...)]

AS

subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ

ONLY];

OR REPLACE             如果视图已存在，相当于修改视图

FORCE | [NOFORCE]

无论基表是否存在都建立视图 [只有基表存在能创建]

WITH CHECK OPTION

通过视图执行的INSERT和UPDATE操作不能创建该视图检索不到的数据行

WITH READ ONLY      禁止对视图执行DML操作

4、建立复杂的视图

//不能通过修改视图修改基表的数据

CREATE VIEW

dept_sum_vu

(dname,minsal,maxsal,avgsal)

AS SELECT

d.dname,MIN(e.sal),

MAX(sal),AVG(sal)

FROM emp e,dept d

WHERE

e.deptno=d.deptno

GROUP BY d.dname

/

数据来自多个表，查询中使用了分组函数

5、WITH CHECK OPTION选项示例

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM emp

WHERE

deptno=20

WITH CHECK OPTION CONSTRAINT empvu20_ck

/

查询生成的视图索引

select

constraint_name,constraint_type from user_constraints where

constraint_type='V'

/

通过视图更新员工部门号

UPDATE empvu20

SET

deptno=10

WHERE empno=7902

/

UPDATE empvu20

*

ERROR at line

1:

ORA-01402: view WITH CHECK OPTION where-clause

violation

违反了约束，因为部门号更新为10后，该视图不能再检索到该行。

同样，在该视图上也不能insert非20号部门的员工。

6、TOP-N(利用内联视图实现)错误的     (先得到3行，为这3行排序)

select

sal

from emp1

where rownum <= 3

order by sal desc

/

正确的

(先对所有数据排序，再取前三行)

select sal

from ( select sal from emp1 order by sal

desc)

where rownum < 4

/

7、视图的DML操作含有如下情况，则不能删除视图中的数据:

含有聚组函数

含有GROUP BY

子句

含有 DISTINCT 关键字

含有ROWNUM

这个伪列

含有如下情况，不能修改该视图中的数据:

上面提到的任何一种情况。

列是由表达式来进行定义的

含有如下情况，不能增加该视图中的数据:

上面提到的任何一种情况。

在基表中包含有

NOT NULL约束的列，然而该列并没有在视图中出现

原则：只要视图中的数据不是来自基表的原始数据，就不能对该数据做DML操作。

DML示例：建立视图包含rownumcreate or replace view

e1_m_v

as

select ename, sal, hiredate from emp1

where

job='MANAGER'

and   rownum < 3

/

对该视图做DML操作

update e1_m_v set

sal=3000 where ename='BLAKE'

/

delete from e1_m_v where

ename='BLAKE'

/

ERROR at line 1:

ORA-01732: data manipulation operation

not legal on this view