Oracle学习之视图的创建、使用和注意事项

202-01-25  Oracle学习之视图的创


一、Oracle版本

Oracle    11.2.0.1.0 - 64bit

Tool        SQLPlus

二、视图是啥

1. 视图View,为了方便使用,是复杂查询的封装或别名,视图的定义存在于数据字典中。。

2. 使用视图的目的和好处坏处。

目的:限制对数据库的访问,将复杂查询封装,针对用户的需求提供独立的需求(不必将全部数据展示给用户)

好处:增加基本表数据的安全性,因为创建语句用到的关键字或者组函数等原因,可以限制用户对视图的DML操作。

坏处:使用视图会增加数据的负担,是对数据库性能的下降。即使视图的查询语句再简单,在表象下可能是成百上千行的复杂查询。

3. 注意点:视图只是一个定义,并不存储自己的数据,数据还是存放在基本表中的

                   存储数据的视图叫快照。

三、创建和使用视图

1. 给scott用户(我练习时用的scott账户)授权,请教了DBA才知道在10g之后的版本中,scott用户默认的创建视图权限被回收了。

2. 创建视图然后使用之:

登录:以管理员身份运行cmd--->sqlplus /nolog

以DBA身份登录数据库ORCL实例

SQL>conn sys/sys@orcl as sysdba; 

授予scott创建视图权限

SQL>grant create view to scott;

再切换登录到scott用户

SQL>conn scott/tiger

创建视图:

SQL>create view v1 as select deptno,min(sal) sals from emp group by deptno;

查看视图的结构:

SQL>desc v1;

查询视图v1的全部数据:

SAL>select * from v1;

 

补充:强制创建视图,需要使用强制创建视图的语法,Force关键字。

1. 前面说到了视图是存在于数据字典中的定义,所以视图可以先于基本表存在

语法:create force view v2 as select * from tt;

会报错(不用管):警告: 创建的视图带有编译错误。

2. 查询视图v2的状态是INVALID,是无效或失效状态

select object_name,object_type,status from user_objects where object_name = 'V2';

3. 创建表tt

create table tt as select * from emp;

再查看视图v2的状态,仍旧是INVALID

4. "激活"视图,需要调用一次

select * from v2;

5. 第三次查看视图v2的状态,发现已经是VALID,有效的、合法的。

四、视图的执行过程

1. 数据库先查找视图v2的定义

select * from v2;

2. 数据库执行查找到的定义,这里正是说明了视图是下降了数据库性能。

五、修改和删除视图

1、修改视图。只需要修改视图的定义,不需要更改基本表
语法:create or replace view v3 as select * from dept;

查询视图v3:select * from v3;

修改视图v3:create or replace view v3 as select * from dept where deptno = 10;

查询v3,查询的结果已经发生了改变,证明视图修改成功了。

2. 删除视图。只需要从数据字典中将视图的定义清除。

语法:drop view v3;

六、对视图进行DML操作

当创建视图的语句中包含以下情况时,对视图的DML可能会受到限制(√表示可操作,x表示不可操作)

 

序号情况deleteupdateinsert
1创建视图时语句中包含组函数:
min(),max(),count(*),avg(),sum()
xxx
2创建视图时语句中包含:
group by 子句
xxx
3创建视图时语句中使用了去重关键字:
distinct 关键字
xxx
4创建视图时语句中包含rownum伪列xxx
5创建视图时语句中包含有表达式的列:
如case…when表达式
xx
6基本表中有 not null 的列,
但该列没有出现在视图的定义中
x

 

 

 

 

 

 

 

 

 

 

 

创建视图是添加Check 检查约束。

待检查选项约束,用于约束where条件,以保证在执行update和insert操作时,被选中的行数据不会发生改变。

语法1:with check option [constraint 约束名]

只读约束。

语法2:with read only

适用范围:两种约束都适用于视图或者基于其它视图创建的视图

对比:

    1、两者都用于限制通过视图执行的update和insert操作

    2、语法1约束存在时,只有满足条件时才允许进行DML操作;语法2约束存在时,再任何情况下都不允许DML操作,限制只读。

语法1操作示例:

SQL> create or replace view tt1 as select * from emp where deptno = 10 with check option constraint tt1_ck;

视图已创建。

SQL> update tt1 set deptno = 11;
update tt1 set deptno = 11
       *
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTION where 子句违规

语法2操作示例:

SQL> create or replace view v4 as select * from emp with read only;

SQL> update v4 set ename = 'H' where ename = 'HELLO';
update v4 set ename = 'H' where ename = 'HELLO';
第 1 行出现错误:
ORA-42399: 无法对只读视图执行 DML 操作

七、其它知识点

1. 内嵌式视图。这个是在公司DBA写的书中看到的,才明白视图我也是经常使用的。

select ename,sal from emp,(select deptno,avg(sal) salary from emp group by deptno) a

 where emp.deptno = a.deptno and emp.sal > a.salary;
这行语句中,a就是内嵌视图,只作用于当前语句。

2. rownum伪列

rownum是从1开始的,当第一条对应的行不满足条件时,会被过滤掉,然后第一条就变成了1,所以使用伪列需要从1开始。

rownum并不存在于数据表中,它是数据库计算出来的,是根据数据从数据库中取出来的顺序来排序的,是一个序列。

rownum使用时注意事项:

1). 在不使用视图的情况下只能使用 < 或 !=;

2). 如要使用between...and 、=、>=、>,就必须要借助视图来实现了。

示例1---无视图:

SQL>select count(*) from emp;

  COUNT(*)
----------
         15

SQL> select * from emp where rownum >2;

未选定行

SQL> select count(*) from emp where rownum <=3;

  COUNT(*)
----------
         3

emp表中明明有15行数据,当使用>2时却是未选定行,使用<=3时,选中了三行。

示例2---视图

SQL> select * from (select rownum,ename from emp order by 1) where rownum between 2 and 5;

未选定行

SQL> select rm,ename from (select rownum rm,ename from emp order by 1) where rm between 2 and 5;

        RM ENAME
---------- ----------
         2 SMITH
         3 ALLEN
         4 WARD
         5 JONES

SQL> select rm,ename from (select rownum rm ,ename from emp) where rm = 4;

        RM ENAME
---------- ----------
         4 WARD

通过给伪列rownum定义别名,变非法为合法,创建了一个在当前SQL上下文中的临时视图,可以使用=、between...and等运算符了。

 

视图还蛮好玩儿。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页