Oracle 视图 的应用

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

视图

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

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 ===============================