Oracle 视图 的应用

这几天 预习了一遍 Oracle , 此处把 Oralce 数据库 中  视图   的笔记 拿出来 分享 一下， 该文中 有的地方  或许存在 点错误， 希望 看到的朋友 帮我指正出来，谢谢。

--==============================================================

视图

视图是数据库中 特有的对象。 视图用于存储查询， 但不会存储数据（物化视图除外）。这是视图和数据表的重要区别。 可以利用视图进行查询、插入、更新和删除数据。

Oracle 中 有4 中视图：

1. 关系视图
2. 内嵌视图
3. 对象视图
4. 物化视图


-- 关系视图 ： 关系视图 是 四种视图中最简单， 同时也是最常用的视图。 我们可以将关系视图看作对简单或复杂的查询的定义。 它的输出可以看作一个虚拟的表， 该表的数据是由其他基础数据表提供的。
 由于关系视图并不存储真实的数据， 因此占用数据库资源也较少。
 
 
--先观察一下 源数据表 t_employees 
SQL> select * from t_employees;

EMPLOYEE_ID EMPLOYEE_NAME   EMPLOYEE_SEX    EMPLOYEE_SALARY STATUS     WORK_YEARS FIRST_NAME      LAST_NAME  PROVINCE   CITY       LOCATION
----------- --------------- --------------- --------------- ---------- ---------- --------------- ---------- ---------- ---------- ----------------------------
        107                                                 NEW                 1 小郭            东方       北京       北京市     朝阳区
        101 zhangSan        男                         6000 ACT                 3 三              张         北京       北京市     朝阳区
        102 liSi            男                         5500 CNN                 2 四              李         河北       秦皇岛     海港区
        103 songyanjun      男                         2000 EN                  1 延军            宋         河北       秦皇岛     海港区
        104 wangpeng        男                         2000 ACT                 1 朋              网         河北       秦皇岛     海港区
        105 hanjipeng       男                         2000 ACT                 1 鹏              韩         河北       福宁       海港区
        106 liangmingy      女                         2000 NEW                 1 雨              量         河北       福宁       海港区
        108 wangErMaZi      男                          123 jjj                 3 而马子          王         廊坊       廊坊       不明

已选择8行。



 
-- 建立关系视图：

--1. 建立关系视图 create view vw_employees as select employee_id, last_name || first_name as employee_name, province || '-' || city as location from employees
SQL> create view vw_employees as select employee_id, last_name || first_name as employee_name, province || '-' || city as location from t_employees;

视图已创建。



--2. 查看视图定义 select text from user_views where view_name='VW_EMPLOYEES';
SQL> select text from user_views where view_name='VW_EMPLOYEES';

TEXT
--------------------------------------------------------------------------------
select employee_id, last_name || first_name as employee_name, province || '-' ||



--3. 查询刚创建的视图
SQL> select * from vw_employees;

EMPLOYEE_ID EMPLOYEE_NAME             LOCATION
----------- ------------------------- ---------------------
        107 东方小郭                  北京-北京市
        101 张三                      北京-北京市
        102 李四                      河北-秦皇岛
        103 宋延军                    河北-秦皇岛
        104 网朋                      河北-秦皇岛
        105 韩鹏                      河北-福宁
        106 量雨                      河北-福宁
        108 王而马子                  廊坊-廊坊

已选择8行。




--======================

修改/删除 视图

--1. 修改视图（为视图 添加 新列 employee_salary）   create or replace view vw_employees as select employee_id, last_name || first_name as employee_name, province || '-' || city as location, employee_salary from t_employees 
SQL> create or replace view vw_employees as select employee_id, last_name || first_name as employee_name, province || '-' || city as location, employee_salary from t_emoyees;

视图已创建。

--2. 查询刚才修改的视图。
SQL> select * from vw_employees;

EMPLOYEE_ID EMPLOYEE_NAME             LOCATION              EMPLOYEE_SALARY
----------- ------------------------- --------------------- ---------------
        107 东方小郭                  北京-北京市
        101 张三                      北京-北京市                      6000
        102 李四                      河北-秦皇岛                      5500
        103 宋延军                    河北-秦皇岛                      2000
        104 网朋                      河北-秦皇岛                      2000
        105 韩鹏                      河北-福宁                        2000
        106 量雨                      河北-福宁                        2000
        108 王而马子                  廊坊-廊坊                         123

已选择8行。



--3. 删除视图   drop view vw_employees；

SQL> drop view vw_employees;

视图已删除。

SQL>








联接视图： 
--联接视图 在 上面的关系视图的举例中  我们 创建的视图 是基于 单个基础表的。  而在实际应用中， 大多数的视图是较为复杂的查询 。 这也是视图的一大优势， 封装复杂查询。
--例如：
--create or replace view vw_employee_salary as select e.employee_id, e.last_name || e.first_name employee_name, e.employee_salary+s.total_price*0.1 salary from employees e, t_sales s where e.employee_id=s.sale_by and s.sale_month='3月';

--我们先创建一个  类似的测试表 t_sales 
-- Create table
create table T_SALES
(
  sale_id     NUMBER not null,
  sale_by     NUMBER,
  sale_month  VARCHAR2(10),
  sale_day    VARCHAR2(10),
  sale_year   VARCHAR2(10),
  total_price NUMBER
)
tablespace SONGYANJUN
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_SALES
  add constraint PK_SALE_ID primary key (SALE_ID)
  using index 
  tablespace SONGYANJUN
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

--创建视图
SQL> create or replace view vw_employee_salary as select e.employee_id, e.last_name || e.first_name employee_name, e.employee_salary+s.total_price*0.1 salary from employees e, t_sales s where e.employee_id=s.sale_by and s.sale_month='3月';
视图已创建。
SQL> select * from vw_employee_salary;

EMPLOYEE_ID EMPLOYEE_NAME                 SALARY
----------- ------------------------- ----------
        102 李四                            5520





-- 编译视图
-- 编译视图 ： 编译视图 依赖于 基础表的存在而存在，当初基础表进行了 结构上的调整， 有可能会对视图产生影响。 如欲再次使用该视图， 必须对视图进行重新编译。
--例如：alter view vw_employee_salary compile;
SQL> alter view vw_employee_salary compile;

视图已变更。






--使用 force 选项强制创建 视图
 使用 force 选项强制创建 视图： 有时， 视图的基础表还没有创建， 但是仍然希望能够创建基于 不存在的数据表的视图。 这是因为， 数据表的预期创建这和视图的创建者并非同一用户。 
 视图创建这不希望 等待基础表创建这的工作完成之后 才进行 自己的工作。 假设， 此时视图创建者 已经对基础表的结构 有了清晰的概念， 例如： books 将被创建， 以存储 图书信息。

--例如： create or replace force view vw_ebooks as select book_name,author from ebooks;
SQL> create or replace force view vw_ebooks as select book_name,author from ebooks;

视图已创建。

SQL> select * from vw_ebooks;

BOOK_NAME            AUTHOR
-------------------- ----------
Java编程宝典         张三
Java基础             李四
Java打飞机           王五
JavaScript基础       趙六
CshapBeNet           二狗子
Cshap编程            傻蛋

已选择6行。




--=====================================

利用视图 更新 数据表

--通过视图， 不但可以对 基础表中的数据进行查询， 而且可以对数据表中的数据进行更新。 更新的方式非常简单 :  直接更新视图中的数据即可将 对基础表 进行相应的更新。
--当然， 并非视图中的所有列都能够 进行更新， 并反射到基础表中， 只有那些直接由基础表 获得的列可以进行更新操作。
--例如 ， 先头创建的 book_name 和 列 author; 而由基础表中的数据经过运算获得， 仅凭视图中的数据无法判断基础表中的数据情况的列， 不能进行更新， vw_employees 中的 name 列。


--select table_name, column_name, updatable, insertable, deletable from user_updatable_columns where table_name='VW_BOOKS';
--插入视图数据
--insert into vw_ebooks values ('千年一叹', '余秋雨')



--1. 我们先查一下  VW_EBOOKS  这个视图中的数据。
SQL> select * from vw_ebooks;

BOOK_NAME            AUTHOR
-------------------- ----------
Java编程宝典         张三
Java基础             李四
Java打飞机           王五
JavaScript基础       趙六
CshapBeNet           二狗子
Cshap编程            傻蛋

已选择6行。

--2. 我们 在 更新这个视图中的数据。
SQL> update vw_ebooks set book_name='C#' where author='二狗子';

已更新 1 行。


-- 我们在查看 验证一下  是否 这个视图中的数据 变更了。
SQL> select * from vw_ebooks;

BOOK_NAME            AUTHOR
-------------------- ----------
Java编程宝典         张三
Java基础             李四
Java打飞机           王五
JavaScript基础       趙六
C#                   二狗子
Cshap编程            傻蛋

已选择6行。


--我们在看一下  实体表中的数据 是否 也 同样跟着 变更了。
SQL> set linesize 150;
SQL> select * from ebooks;

    BOOKNO        EID BOOKNAME                       BOOK_NAME            AUTHOR
---------- ---------- ------------------------------ -------------------- ----------
      1006                                           Java编程宝典         张三
      1001          8 javaBook                       Java基础             李四
      1002          8 javaBook                       Java打飞机           王五
      1003          8 javaBook                       JavaScript基础       趙六
      1004          6 c# Book                        C#                   二狗子
      1005          8 c# Book                        Cshap编程            傻蛋

已选择6行。

SQL>


-- OK ， 这样就说明了 ，视图是 可以被 修改的。






--===============

with  check  option选项

--创建　视图时，　with check option  是常用选项之一。 一旦使用了 该选项， 那么 Oracle将保证视图在数据库更新之后与更新之前呃结果集相同。

--create or replace view vw_books as select * from ebooks where bookno=1005;
SQL> create or replace view vw_books as select * from ebooks where bookno=1005;

视图已创建。

SQL> select * from vw_books;

    BOOKNO        EID BOOKNAME                       BOOK_NAME            AUTHOR
---------- ---------- ------------------------------ -------------------- ----------
      1005          8 c# Book                        Cshap编程            傻蛋


--update vw_books set bookno=1006;
SQL> update vw_books set book_id=1006;

已更新 1 行。

SQL> select * from vw_books;

    BOOKNO        EID BOOKNAME                       BOOK_NAME            AUTHOR        BOOK_ID
---------- ---------- ------------------------------ -------------------- ---------- ----------
      1005          8 c# Book                        Cshap编程            傻蛋             1006



--create or replace view vw_books as select * from ebooks where book_id=1006 with check option
SQL> create or replace view vw_books as select * from ebooks where book_id=1005 with check option;

视图已创建。

SQL> select * from vw_books;

未选定行





--==============================

关系视图小结

1. 保障数据安全性
2. 数据整合
3. 数据透明性


--================================

内嵌视图

关系视图座位查询定义，  一旦创建， 既可存在于 数据库中， 并可被多次使用。 但有时 ，需要某个视图座位过度结果集， 但在一次使用之后， 便不再需要， 此时不宜创建关系视图。 因为关系视图占用数据库资源， 而且也会增加维护成本。 此时应该选择使用 内嵌视图。

-- 内嵌视图 简介： 
-- 内嵌视图 也是  视图，  只是不会利用 create view 进行 显示创建 。 也不能 在数据库中查询到 其 相关信息。 一般情况下， 被嵌套在 查询语句中 使用， 因此称为 内嵌视图。 其功能类似于子查询。 
-- 当然 ， 内嵌视图 也可以 出现在更新、 插入和删除语句中。 内嵌视图具有临时性， 他只在 被嵌入语句的执行期间有效， 并可以在被嵌入语句的任何地方 使用该视图。


-- 内嵌视图的使用： 

--1. 利用内嵌视图 进行查询  ： select * from (select employee_id, last_name || first_name as employee_name, employee_salary from t_employees order by employee_salary desc) where rownum<=3; // rownum<=3 为取出 3行的数据

SQL> select * from (select employee_id, last_name || first_name as employee_name, employee_salary from t_employees order by employee_salary desc) where rownum<=3;

EMPLOYEE_ID EMPLOYEE_NAME             EMPLOYEE_SALARY
----------- ------------------------- ---------------
        107 东方小郭
        101 张三                                 6000
        102 李四                                 5500

SQL>


--2.  利用 内嵌视图更新数据表
--。。。。。。。。



内嵌视图小结：

1. 内嵌视图与临时表、 关系视图的使用场景。
2. 分辨内嵌视图 与子查询。



--=============================

对象视图

对象视图： 数据库都是在关系型 数据库， 但是 ， 面对对象编程的思想早已深入人心。 Oracle数据库不仅可以通过关系表来存储数据， 同样， 也可以创建对象， 以对象的方式进行数据存储。 
--关系视图是由 关系表进行查询获得的， 而对对象视图则是对 对象机型查询获得的。

--对象视图简介：  
--说道 对象视图 ，自然应该首先提到 对象。 Oracle中的对象 仍然是一个逻辑的概念， 虽然可以从对象中获取数据， 就像数据真实存储与对象中 一样。 
--但是在对象的概念之下， 数据仍然是 存储与关系表中。 要创建对象， 首先要建立对象类型， 类似于 Java 或 C#中 类的概念。



--先创建一个  类似的表 吧
-- Create table
create table EMPLOYEES
(
  employee_id   NUMBER,
  employee_name VARCHAR2(20),
  location      VARCHAR2(50),
  first_name    VARCHAR2(10),
  last_name     VARCHAR2(10),
  province      VARCHAR2(10),
  city          VARCHAR2(10),
  salary        NUMBER
)
tablespace SONGYANJUN
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );

--之后插入了一条 测试数据 ， 我们查询一下。
SQL> select * from employees;

EMPLOYEE_ID EMPLOYEE_NAME        LOCATION                                           FIRST_NAME LAST_NAME  PROVINCE   CITY           SALARY
----------- -------------------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------
        101 员工名字             东方明珠城B                                        延军       宋         河北       秦皇岛           2000



---====
-- 创建 和使用  对象 视图 ：  利用 基础的对象类型  employee 来创建 对象视图。  那么视图中的列 将与  employee_type 中的属性保持 一致。
create type employee is object(employee_id number, employee_name varchar2(20), location varchar2(50), salary number)  
create or replace view ov_employees of employee  with object oid(employee_id) as select employee_id, last_name || first_name, province || city, salary from employees;
--=====
尼玛的， 为啥 无法执行啊， 我勒个去， 为什么执行成功了， 但是神马 都效果都没出来啊？？？？？
--======







--=======================================

物化视图


前面所叙述的 三种 视图 ：  关系视图、内嵌视图  和 对象视图， 实际头饰通过定制查询 ， 并利用 查询定义来 获取 数据。 三种视图都不会直接存储数据， 每次 操作时， 都会进行 编译

--下面 描述一下   物化视图。


物化视图 简介： 
   物化视图 是物理化视图的简称， 顾名思义， 该视图存储实际数据， 因此， 会占用一定的数据库空间。 在这一点上， 更接近于 临时表。 但不像临时表那样在某个特定的时机  会 删除数据。 
   物化视图中 的数据  是 可以重用的， 因此， 经常应用与 读取频繁的场合。
   
   物化视图对于大数据表 的处理显的尤为重要。 为了统计一个拥有 百万级记录的数据表的 总和  以及 平均值 问题， 将耗费大量的数据库资源和时间 。
   可以通过 物化视图 改善这一状况。 即： 对表进行一次统计， 并将统计结果存储在物化视图中， 那么， 以后的每次查询直接查询该视图即可。
   
   --1. 创建  物化视图：
   
   create materialized view mv_monthly_sales
   build immediate
   refresh on commit
   enable query rewrite
   as 
   select sale_month,sum(price*quantity) from employee_sale_details group by sale_month;
   
   -- 2. 查看 物化视图信息
   
   select object_name, object_type,status from user_objects where object_name='MV_MONTHLY_SALES';
   
   
   
-- 物化视图的数据加载

--在上面的创建 物化视图的例子语句中 ， 
--SQL 语句中使用了   build immediate 。 该选项用于立即 加载 物化视图的数据。 
--也就是说，  在创建 物化视图的同时， 立即根据定义从基础表中 获取数据， 并将数据添加到物化视图中。 另外 一个可用 选项为 build deffered ， 表示延迟载入数据。

使用 延迟加载 是 必要的。 
--有时 ， 物化视图的基础表数据量 巨大， 载入数据会耗费 大量资源。 直接使用立即加载策略， 在数据库使用的高峰期， 会造成客户端延迟。 但是，后续的开发工作可能使用到 该 物化视图， 那么可以采用延迟加载数据的策略。



--==========================

物化视图的数据更新

-- 物化视图的数据 应该 根据 基础表的更新而更新。  同上创建物化视图的例子中：  
SQL语句 中使用了 refresh on commit ， 要求 Oracle实现 自动更新功能。 即基础表的数据 更新 被 提交后， 应该自动更新物化视图的数据。



--======================

 查询 重写

同上例子创建物化视图 的SQL语句 例子中 ， 使用  了  enable query rewrite 选项。 该选项 用于 启用 查询重写。 

-- 查询重写是指：  Oracle 对基础表 的查询，  按照优化原则， 查找恰当的物化视图， 如果获得 优化视图， 则将查询转化为对 物化视图的查询。

select sale_month, sum(quantity * price) sale_quantity from employee_sale_details group by sale_month;

-- 删除物化视图 ， 再次查看 SQL 语句的花费。

drop materialized view mv_monthly_sales;


--===================================

视图 小结：

介绍了 Oracle中的 4 中视图 ：  关系视图、 内嵌视图、 对象视图 和物化视图。 。

--其中  
关系视图 是最常用的视图。  关系视图在 安全性、 组合数据以及 增强型方面都有着 即为广泛的应用。 

内嵌视图 则无须 真正创建一个视图对象， 而 更接近于临时视图， 具有运行时有效的特点， 使内嵌视图 使用 起到了非常 灵活、方便。


组装数据；
封装复杂查询；
提供建模模型；
提高相应速度。




--=====   视图 实例  =====--

主要讲述了 视图的创建和使用。 在Oracle 数据库中 ， 本身存在着 若干已 创建的视图。 这些视图可以用来获取许多重要的信息。

--例如： 为了获得数据库中， 某个表的所有列， 既可以利用视图 user_tab_cols 。

-- 该例子 演示了 如何获的 某个表/视图的所有列

select table_name, column_name, data_type from user_tab_cols where lower(table_name) = 'books';

select table_name, column_name, data_type from user_tab_cols where lower(table_name) = 'vw_employee_salary';



--======================= 视图小结OVER ===============================

Over！！！