1>查询用户下所有的视图:
select * from user_views;
2>实际工作中关于视图的实例一:
create or replace view v_grzhxx as
select a.id id,a.grzh grzh,a.dwzh dwzh,c.zjlx, c.zjhm zjhm,f.personal_password mima,c.xingming xingming,a.grjcjs grjcjs,
decode((a.dwyjce+a.gryjce+a.bcyjce+a.czybte),0,'0.00',ltrim(to_char((a.dwyjce+a.gryjce+a.bcyjce+a.czybte),'9999999.99'))) yjce,(case when a.grzhzt in ('01','02') then a.grzhye else 0 end) grzhye,a.jzny jzny,
a.grzhzt grzhzt,a.grckzhhm lmkkh,(case when a.grckzhhm=' ' then '00' else '11' end) lmkzt,
a.khrq khrq,(case when c.xingbie='2' then '0' else '1' end) sex,c.sjhm sjh,a.zhsfdj zhdj from gjzf_gr_zz a inner join cr_gr c on a.grbh = c.grbh
inner join gjzf_dw_zz d on a.dwzh=d.dwzh inner join cr_dw e on d.dwbh= e.dwbh left join t_sy_wt_personal_user f on f.personal_grbh=c.grbh;3>实际工作中关于视图的实例二:
create or replace view v_dwzhxx as select a.id id,a.dwzh dwzh,b.dwmc dwmc,b.dwdz dwdz,b.dwfrdbxm dwfrdbxm,b.jbrxm jbrxm,b.jbrsjhm jbrsjhm, case when b.zcrq is null then null else to_char(b.zcrq,'yyyymmdd') end dwslrq,to_char(a.khrq,'yyyymmdd') dwkhrq, c.mc ssjg,d.mc gjyh,b.zzjgdm zzjgdm,f.personal_password mima,round(a.dwjcbl,2) dwjcbl,round(a.grjcbl,2) zgjcbl,a.dwjcrs dwjcrs, a.dwzhzt, a.jzny, dwzhye,' ' zgyxm,' ' zgysjh from gjzf_dw_zz a inner join cr_dw b on a.dwbh=b.dwbh left join bm_khjg c on a.jgbm=c.bm left join bm_yhbm d on a.yhbm=d.yhbm left join hr_yg_sjqx_dw e on a.dwzh=e.a003 left join t_sy_wt_personal_user f on e.userid=f.userid; 4>删除视图: drop view 视图名; 5>创建好视图之后可作为单表使用也可与已有表关联。
