oracle中视图的使用

一、视图的定义

      视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

二、视图的作用

     在不依赖数据库基表的前提下，将需要的数据通过多个数据库基表联合查询后，得到目标数据集合，创建虚拟表。

三、视图的使用

视图基本语法：

CREATE[OR REPLACE][FORCE][NOFORCE]VIEW view_name

[(column_name)[,….n]]

AS

[子查询]

[WITH CHECK OPTION[CONSTRAINT constraint_name]]

[WITH READ ONLY]

说明:

view_name :  视图的名字

column_name: 视图中的列名  

REPLACE:  如果创建视图时, 已经存在此视图, 则重新创建此视图, 相当于覆盖

FORCE:  强制创建视图, 无论的视图所依赖的基表否存在或是否有权限创建

NOFORCE:  只有基表存在且具有创建视图权限时, 才可以创建视图

WITH CHECK OPTION  指出在视图上所进行的修改都要符合子查询所指定的限制条件

WITH READ ONLY  只允许查看视图 四、视图查询实列

create or replace view t_push_mptarget_smarttg as --app推送 select f.* from (select tg.eht as push_target, t.serial_number, t.channel_key from tetg_ywgx tg left join t_push_mp_smarttg_bj t on tg.dyid_fid = t.serial_number where tg.zt = '0' and to_date(tg.jsrq, 'yyyymmdd') >= to_date(sysdate, 'yyyymmdd') and tg.ywlx = '9' and tg.gxlx = '2' and t.channel_key = 'huacai_app_push' ) f left join (select tg.eht as push_target, t.serial_number, t.channel_key from (select a.eht, a.dyid_fid from tetg_ywgx a left join t_adviser_short_push b on a.eht = b.eno where (b.plant_cycle_notice = '0' or b.remind_notice = '0') and a.zt = '0' and to_date(a.jsrq, 'yyyymmdd') >= to_date(sysdate, 'yyyymmdd') and a.ywlx = '9' and a.gxlx = '2') tg left join t_push_mp_smarttg_bj t on tg.dyid_fid = t.serial_number where t.serial_number = '314' and t.super_bztype_key = '1001' and t.channel_key = 'huacai_app_push' ) d on f.push_target = d.push_target where d.push_target is null union --短信推送 select f.* from (select tg.sj as push_target, t.serial_number, t.channel_key from tetg_ywgx tg left join t_push_mp_smarttg_bj t on tg.dyid_fid = t.serial_number where tg.zt = '0' and to_date(tg.jsrq, 'yyyymmdd') >= to_date(sysdate, 'yyyymmdd') and tg.ywlx = '9' and tg.gxlx = '2' and t.channel_key = 'sms01' ) f left join (select tg.sj as push_target, t.serial_number, t.channel_key from (select a.eht,a.sj, a.dyid_fid from tetg_ywgx a left join t_adviser_short_push b on a.eht = b.eno where (b.plant_cycle_sms = '0' or b.remind_sms = '0') and a.zt = '0' and to_date(a.jsrq, 'yyyymmdd') >= to_date(sysdate, 'yyyymmdd') and a.ywlx = '9' and a.gxlx = '2') tg left join t_push_mp_smarttg_bj t on tg.dyid_fid = t.serial_number where t.serial_number = '314' and t.super_bztype_key = '1001' and t.channel_key = 'sms01' ) d on f.push_target = d.push_target where d.push_target is null with read only;

上面实列是初版，因为公司原因，不能放最终版本，见谅