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