视图的定义及使用

在所有进行的SQL语句之中，查询是最复杂的操作，而且查询还和具体的开发要求有关，那么在开发过程之中，程序员完成的并不是数据库的所有内容，而更多的是应该考虑到程序的设计结构。可是没有一个项目里面不会包含有复杂查询，那么程序员如何从复杂查询中解脱出来呢？

         所以在这种情况下就提出了视图的概念。利用视图可以实现复杂SQL语句的封装操作。从实际的开发来讲，一个优秀的数据库设计人员，除了要给出合理的数据表结构之外，还应该将所有可能使用到的查询封装好视图，一并交给开发者

视图依然属于DDL的定义范畴，所以视图的创建需要使用如下语法完成：

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询 ;

范例：创建视图

create view myview as select * from emp where deptno=10;

希望将10部门的所有雇员信息保存在视图之中。在Oracle 10g及以前的版本，scott是可以直接进行视图创建的，但是从Oracle 10G R2版本开始如果要想创建视图，那么就需要单独分配创建视图的权限。

范例：为scott分配创建视图的权限

conn sys/change_on_install as sysdba;
grant create view to scott;
conn scott/tiger

权限分配完成之后则可以进行视图的创建操作。视图本身属于数据库对象，所以要想查看视图的信息可以使用“user_views”数据字典完成。在这个数据字典里面可以查询到视图的具体语法

select * from user_views;


范例：查询视图

select * from myview;

于是下面可以继续利用视图包装一个复杂的SQL。

--删除视图
drop view myview;
--创建视图
create view myview
as
select d.deptno,d.dname,d.loc,temp.count
from dept d,(
select deptno dno,count(*) count
from emp
group by deptno) temp
where d.deptno=temp.dno(+);
select * from myview;

         由于myview视图的名称已经被占用了，所以理论上应该先删除，而后再创建一个新的视图。可是删除和创建之间有可能产生间隔。所以在实际之中，由于视图使用频率较高，而且直接与开发有关系，那么一般情况下不会选择删除后再重新创建，而是选择进行视图的替换。利用新的查询替换掉旧的查询。

create or replace view myview
  as
select d.deptno,d.dname,d.loc,temp.count
from dept d,(
select deptno dno,count(*) count
from emp
group by deptno) temp
where d.deptno=temp.dno(+);
select * from myview;
此时表示如果现在视图存在则进行替换，如果视图不存在则进行删除,sql以后被封装后只需要输入:
select * from myview;

 

         实际上视图中只是包含有查询语句的临时数据，并不是真实存在的，可是在默认情况下创建的视图是可以直接进行修改操作的。

create or replace view myview
  as
select * from emp where deptno=20;
select * from myview;

此时创建的myview视图之中，deptno=20是视图数据的存在依据，但是默认情况下，此依据是可以修改的。

范例：更新视图的中数据的部门编号

先查下emp表中的7369，发现deptno=20
update myview set deptno=30 where empno=7369;
更新myview表后，发现emp表中的7369发生了改变

发现此时更新了视图，结果导致emp数据表中的内容也发生了变化。所以为了保证视图的创建条件不能够被更新，则可以在创建视图的时候使用WITH CHECK OPTION子句

把7369更新回去

update emp set deptno=20 where empno=7369;

create or replace view myview
as
select * from emp where deptno=20
with check option;
--现在无法更新
update myview set deptno=30 where empno=7369;

此时使用了WITH CHECK OPTION子句可以保证视图的创建条件不被更新，如果使用了更新，则将出现如下的错误提示信息：ORA-01402: 视图 WITH CHECK OPTION where 子句违规“”。

范例：修改其他字段

update myview set sal=80000 where empno=7369;

发现换一个字段，又能更新操作成功了, 那么这样的做法同样也不合理

所以在一般创建视图的时候，由于里面都属于映射的数据，那么本质上就不建议其进行修改，最好的视图就是创建一个只读视图。使用WITH READ ONLY子句完成

create or replace view myview
as
select * from emp where deptno=20
with read only;
update myview set comm=200.0 where empno=7369;

现在就创建好了一个只读视图信息，于是再次发出修改操作时会出现“ORA-42399: 无法对只读视图执行 DML 操作”，这样就避免了通过视图的临时数据修改数据表真实数据的影响

 

如果现在只是单表查询，封装为视图就没有必要，那么下面写一个复杂查询包装。

create or replace view myview
as
select e.empno,e.ename,e.job,e.sal,d.dname,m.ename mname
from emp e,dept d,emp m
where e.deptno=d.deptno and e.mgr=m.empno(+);
 
select * from myview;

使用WITH READ ONLY数据将无法添加和修改,如果真要改，那么可以使用替代触发器完成

替代触发器我不会

 

 

------------------------------------------------------------------------------------------------------------------------------------------

                                                     索引

进入sys用户有可能会出现警告: 您不再连接到 ORACLE。

解决办法：在sqlplus scott/tiger用户里输入以下代码：

         conn sys as sysdba   然后会提示输入口令,我的sys用户的密码是change_on_install

       之后输入:commit;

之后就可以自由切换

至于为什么，你的set autotrace on代码输错了呗


现在来观察如下的一段程序代码

select * from emp where sal>1500;

         这是一条非常简单的查询语句。于是下面就通过此语句来分析一下数据库在这之中做了什么？为了可以方便观察，下面打开追踪器。切换到sys用户

conn sys/chage_on_install as sysdba;
--打开查询的追踪器
set autotrace on;
select * from scott.emp;

打开之后直接在sys用户中进行性能信息的查询，此时的查询除了会返回结果之外，还会返回给用户一些分析的信息。

 

返回结果有一个: TABLE ACCESS FULL

此时直接描述的是要进行全表扫描，就属于逐行扫描。而且最为关键的问题在于，如果说现在emp表的数据有50W条。可能在第20920条之后就没有任何的雇员记录可以满足于此条件，那么这个时候以上的语句会继续向后查询，很明显，这就是一种浪费。所以这个时候的性能一定不可能变快的。

那么已经知道了问题，那么该如何解决这样的查询呢？那么现在的第一个想法就是需要知道明确的数据排序。如果直接使用ORDER BY是排序，但是ORDER BY子句是整个查询语句之中最后执行的，也就是说此时还没到ORDER BY中。所以在这种情况下，数据的最好的排列是根据树排列。

         树的排列原则：选取一个数据作为根节点，比此节点大的数据放在右子树，比节点小的数据放在左子树。这样就可以实现排序。但是现在的问题是，选什么数据来操作呢？本程序使用的是sal字段，所以就应该利用sal来操作索引

select sal from scott.emp;

 

       所以这个时候就可以进行索引的创建以实现以上的操作结构。在整个的操作过程之中，如果要想创建索引，那么必须设置一个指定的字段

范例：为scott.emp表在sal字段上创建索引

create index emp_sal_ind on scott.emp(sal);

一旦索引创建完成之后，下面重新发出一次查询的指令

select * from scott.emp where sal>1500;

 

通过查询分析器可以发现，此时的查询不再使用全表扫描, 而是查询了所需要的范围的内容. 如果要想查询速度快，那么必须使用索引；

返回的内容:
TABLE ACCESS BY INDEX ROWID
 INDEX RANGE SCAN