`
roway
  • 浏览: 50385 次
  • 性别: Icon_minigender_1
  • 来自: 西安
社区版块
存档分类
最新评论

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次就获得了结果,查询的性能得到了极大的优化。

分享到:
评论

相关推荐

    oracle动态视图文档(详细版)

    3. **查询动态视图**:讨论如何在SQL查询中使用动态视图,以及如何结合WHERE子句、JOIN操作和聚合函数进行复杂查询。 4. **动态视图的应用场景**:通过实例展示动态视图在数据库管理、性能监控、权限控制等方面的...

    深度解析Oracle 实战与提高

    本资料“深度解析Oracle 实战与提高”旨在帮助读者深入理解Oracle的内部机制,提升数据库管理和开发技能。 首先,Oracle数据库的基础知识包括数据模型、SQL语言以及数据库体系结构。数据模型是理解数据库系统的基础...

    oracle实战练习

    3. **数据库对象**:表、视图、索引、存储过程、函数和触发器是Oracle数据库中的基本对象。你需要知道如何创建、修改和删除这些对象,以及它们在数据库设计中的作用。 4. **PL/SQL**:Oracle的PL/SQL是一种过程化...

    ORACLE实战练习

    Oracle实战练习是数据库管理员和开发人员提升技能的重要途径。Oracle是一种广泛应用的关系型数据库管理系统,以其高效、稳定和功能强大著称。在这个“Oracle实战练习”中,我们可以期待一系列的题目和PPT,旨在帮助...

    ORACLE全集实战课程

    ### ORACLE全集实战课程知识点概述 #### 一、Oracle数据库基础 1. **数据库概念与原理** - 数据库的基本定义:一种用于存储、管理数据的系统。 - 数据库管理系统(DBMS):用于创建、维护数据库的软件工具。 - ...

    精通Oracle核心技术和项目实战之视图.pptx

    Oracle视图是数据库系统中的一个重要概念,它是一种虚拟的表,由SELECT查询语句定义,基于一个或多个实际的表(或视图)。视图不存储任何数据,它的数据来源于基表,即被查询的原始表。视图的存在旨在简化复杂的查询...

    Oracle数据库实战培训

    ### Oracle数据库实战培训知识点详解 #### 一、Oracle数据库特点 1. **数据的结构化**:Oracle数据库采用关系模型,所有的数据都被组织成表格的形式,每一列具有明确的数据类型,这种结构化的特性使得数据管理和...

    -玩转oracle实战教程(韩顺平

    - 视图: 基于一个或多个表的虚拟表。 - 索引: 加速数据检索速度的结构。 - 存储过程: 预编译的 SQL 代码集合。 #### 七、Oracle 数据库管理最佳实践 - **性能监控**: - 使用 Oracle 提供的工具来监控数据库...

    利用oracle系统表或视图猜解字段

    本项目旨在通过实验模拟,了解并掌握如何利用Oracle的系统表和视图来获取数据库字段的相关知识。 二. 项目目标 1. 学习并熟悉Oracle数据库中的系统表和视图,特别是与字段信息相关的。 2. 掌握如何通过SQL语句查询...

    oracle 10g经典实战

    oracle10g经典实战第3章_数据库的操作.ppt oracle10g经典实战第4章_数据库的查询和视图.ppt oracle10g经典实战第5章_PLSQL介绍.ppt oracle10g经典实战第6章_存储过程和触发器.ppt oracle10g经典实战第7章_系统安全...

    Oracle经典实战

    《Oracle经典实战》是电子工业出版社推出的一部深入讲解Oracle 10g数据库管理的实践教程,涵盖了数据库的方方面面,旨在帮助读者掌握Oracle的核心技术。本教程通过一系列PPT章节,详细介绍了Oracle 10g的基本概念、...

    Oracle PL/SQL实战(待续)

    在这个"Oracle PL/SQL实战(待续)"的主题中,我们将深入探讨这个语言的核心概念和实战技巧。 在Oracle数据库中,PL/SQL提供了丰富的控制结构,如循环、条件判断,以及异常处理机制,使得开发者能够编写复杂的业务...

    oracle经验集,实战的必备资料

    当遇到性能下降、错误提示或系统异常时,经验集可能会分享如何利用Oracle的错误日志、告警日志、跟踪文件和V$视图来定位问题,以及如何通过重建索引、调整初始化参数或修改SQL语句来解决问题。 最后,文档可能还...

    韩顺平玩转oracle实战教程1-7讲

    本教程“韩顺平玩转Oracle实战教程1-7讲”将引领初学者逐步深入理解Oracle的核心概念和技术,为IT专业人士提供宝贵的实战经验。 1. Oracle基础: Oracle数据库的基础包括SQL语言、数据模型、数据库结构和对象。SQL...

    ORACLE SQL入门与实战经典

    不过,根据标题《ORACLE SQL入门与实战经典》,我们可以推断出这是一本关于Oracle SQL语言学习和应用的书籍。Oracle SQL是Oracle数据库的核心技术之一,用于数据查询、定义、更新和管理。接下来,将详细介绍Oracle ...

    Oracle ADF开发实战指南

    ### Oracle ADF开发实战指南知识点解析 #### 一、Oracle ADF简介 - **Oracle Application Development Framework (ADF)** 是一种用于快速开发企业级Java应用程序的框架。它提供了一种简化的方式来构建复杂的用户...

Global site tag (gtag.js) - Google Analytics