####################################################################################
创建和使用视图
####################################################################################
# 视图
在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次就获得了结果,查询的性能得到了极大的优化。
oracle实战3(视图)
最新推荐文章于 2021-04-03 00:50:17 发布