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;

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