oracle实战3(视图)

####################################################################################
创建和使用视图
####################################################################################

# 视图

在Oracle中有4种视图：
1、关系视图
2、内嵌视图
3、对象视图
4、物化视图

视图的作用：
1、强化安全
2、增强性能
3、隐藏复杂性
4、提供抽象
5、定制数据表示

conn oe/oe@test;

create view company_phone_book
as
select last_name ||','||first_name name,phone_number,email
from employees;

select name,phone_number from company_phone_book;


desc user_views;

select text from user_views where view_name='COMPANY_PHONE_BOOK';

CREATE OR REPLACE VIEW company_phone_book
as
select employee_id emp_id,last_name ||','||first_name name,phone_number,email
from employees;

# 建立视图

例1：为表emp建立视图dept20，此视图可以显示部门20的雇员和他们的年薪。

Create view dept20
As
select ename,deptno,job, sal*12 sal12
From emp where deptno=20;

例2：

Create view clerk (id_number, person, depart, position )
As select empno,ename,deptno,job
From emp where job='CLERK'
With check option constraint wco;

用户不能往clerk视图中作insert(或update)非'clerk'的记录。

insert into clerk values(7999,'angeos',10,'CLERK');

select * from clerk;

select empno,ename,deptno from emp where deptno=10;

insert into clerk values(7998,'angeos',10,'Manager');
提示插入的数据违反了WITH CHECK OPTION约束。

# 删除视图

drop view view_name;

# 视图中的约束

create view emps_hired_in_2000 as
select * from employees
where to_char(hire_date,'YYYY')='2000';

select count(*) from emps_hired_in_2000;

create view yearly_hire_totals as
select to_char(hire_date,'YYYY') year,count(*) total
from employees
group by to_char(hire_date,'YYYY')
order by to_char(hire_date,'YYYY');

select * from yearly_hire_totals;

# 试验：约束和安全

假设我们建立一个视图，这个视图可以基于用户的身份进行查询约束。在HR模式中使用
employees表时，我们注意到用户的EMAIL列，假设这一列不仅仅存放的是用户的EMAIL，
而且也是他们的数据库登录ID。
我们研究下面的问题：

首先以管理员身份登录：
conn sys/sysadmin@test as sysdba;
alter user hr account unlock;
alter user hr identified by hr;

然后以hr登录，建立视图：

create or replace view my_reports as
select last_name,first_name
from employees
where manager_id=(
select employee_id from employees
where email=user);

授权：

grant select on my_reports to public;

查询视图：
show user;
可以看到用户为HR
查询视图：
select * from my_reports;
无返回结果。？？？

由于HR不是雇员，所以，不会有结果返回。

以管理员身份登录数据库，创建用户SKING，他是公司的经理，并且使用SKING作为他的
电子邮件地址（假设）：

conn sys/sysadmin@keymen as sysdba;

create user sking identified by sking;

grant connect,resource to sking;

以新用户身份登录，查询视图：

conn sking/sking@keymen;

select * from hr.my_reports;
这次有返回结果。

由于视图的所有者是HR，所以我们需要在视图名称前加上模式限定。

# 联接视图

在HR模式中，我们查看一张视图：

desc EMP_DETAILS_VIEW;

set long 5000;

select text from user_views
where view_name='EMP_DETAILS_VIEW';

通过查询，我们看到这个视图联接了六张表。而且视图定义的末尾处，WITH READ ONLY选项
表明了，这个视图是只读视图。INSERT、UPDATE和DELETE语句都不能对这个视图执行。


# 验证视图

conn scott/tiger@test;

create table t(
id number,
data varchar2(20)
);

create view view_t as
select id view_id,data view_data
from t;


insert into t values(1,'ABC');
select * from view_t;

alter table t
modify(id number,data varchar2(255));

alter table t
add(data2 varchar2(100));

desc t;

select object_name,status
from user_objects
where object_name='VIEW_T';

你会发现视图无效了。！！！

select * from view_t;
你会发现可以查到数据了。Oracle对视图进行了重新编译处理！

我们也可以手工处理：
alter view view_t compile;



####################################################################################
创建和使用内嵌视图
####################################################################################

# 内嵌视图严格的来说不是模式对象,即它与普通关系视图不同。从根本上来讲，内嵌视图就是嵌入到父查询中的查询，能够
在任何可以使用表名称的地方使用。内嵌视图可以出现在SELECT语句的FROM子句中，以及INSERT
INTO、UPDATE，甚至是DELETE FROM语句中。
内嵌视图是临时的－－他只存在于父查询的运行期间，但是它可以让开发者有能力在整个查询的
任何部分中使用视图结果。

# 建立内嵌视图

conn hr/hr@keymen;

select department_name,count(*),to_char((count(*)/total_emp.cnt)*100,'90.99')||'%'
pct from departments,employees,
(select count(*) cnt from employees) total_emp
where departments.department_id = employees.department_id
group by department_name,total_emp.cnt;

工作原理：
为了计算各个部门雇佣工作人员的比例，我们需要两部分信息。首先，我们需要雇员的整体
数量，通过语句获得：
select count(*) from employees;

其次：我们需要各个部门中雇员得数量：
select department_name,count(*)
from departments,employees
where departments.department_id = employees.department_id
group by department_name;

我们需要建立一个计算雇员整体数量得内嵌视图。

(select count(*) cnt from employees) total_emp;

整体描述：

select department_name,count(*),to_char((count(*)/total_emp.cnt)*100,'90.99')||'%'
pct from departments,employees,
(select count(*) cnt from employees) total_emp;

# 试验：谁是公司得前5名员工？

select last_name,hire_date from employees
order by hire_date;

select last_name,hire_date from employees
where rownum<6
order by hire_date;



select last_name,hire_date
from (select last_name,hire_date from employees order by hire_date)
where rownum<=5;


####################################################################################
创建和使用对象视图
####################################################################################

# 对象视图
Oracle是面向对象的数据库产品。随着Oracle8i的出现，Oracle融入了两个概念，进而
可以让开发者建立对象类型、建立这种类型的对象，并且在数据库表中存储对象实例。

# 试验：建立对象视图

create type employee_type is object(
employee_id number,
name varchar2(47),
email varchar(25),
phone_number varchar2(20)
);

create view ov_company_phone_book of employee_type
with object oid(employee_id) as
select e.employee_id,e.last_name ||','||e.first_name,
e.email,e.phone_number
from employees e;

desc ov_company_phone_book;

select view_type_owner,view_type,oid_text
from user_views
where view_name='OV_COMPANY_PHONE_BOOK';





####################################################################################
创建和使用物化视图
####################################################################################

# 物化视图

conn oe/oe@keymen;

desc orders;

select count(*) from orders;

create table my_orders as
select * from orders;

insert into my_orders
select * from my_orders;

执行对次，使得表中有107520行数据。

select count(*) from my_orders;


@ c:\oracle\ora92\rdbms\admin\utlxplan.sql;
CONN sys/sysadmin@keymen as sysdba;
@ c:\oracle\ora92\sqlplus\admin\plustrce.sql;

grant plustrace to public; --授权任何用户都可以执行查询计划



对Oracle 10g数据库

@ C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql;

@ C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql;
grant plustrace to public; --授权任何用户都可以执行查询计划

set autotrace on;
set timing on;
select customer_id,count(*) total_orders
from my_orders
group by customer_id;

建立物化视图

set timing off;
create materialized view my_orders_mv2
build immediate
refresh on commit
enable query rewrite
as
select customer_id,count(*) total_orders
from my_orders
group by customer_id;

set timing on;
select * from my_orders_mv2;

通过对比发现，在my_orders视图中，通过1000多次计算才得出结果，而在my_orders_mv
视图中，只计算了不到10次就获得了结果，查询的性能得到了极大的优化。