create or replace view v_allinfo_stat_by_site as
select a.*,b.BLACK_COUNT,b.WHITE_COUNT,b.GRAY_COUNT,b.AUTHOR_COUNT,group_count,audit_count,vblog_org_count,valbum_count from v_video_stat_by_site a,
v_vblogger_stat_by_site b,
(select count(*) group_count,site_id from vblogger_group group by site_id) c ,
(select count(*) audit_count,site_id from audit_web_video group by site_id) d ,
(select count(*) vblog_org_count,site_id from vblogger_org group by site_id) e ,
(select count(*) valbum_count,site_id from valbum group by site_id) f
 where a.site_id = b.site_id(+) and
 a.site_id = c.site_id(+) and
 a.site_id = d.site_id(+) and
 a.site_id = e.site_id(+) and
 a.site_id = f.site_id(+)
 order by a.all_count desc;


c，d，e也是相當(dāng)于視圖
a、b本身就是視圖


存儲(chǔ)過(guò)程：
create or replace procedure UPDATE_VBLOGGER_MONTHLY_WEEKLY as
  v_now_date  NUMBER;
begin
--按月統(tǒng)計(jì)
   SELECT TO_NUMBER(TO_CHAR(sysdate,'YYYYMM'))INTO v_now_date FROM dual;
   update vblogger v set v.monthly_video_count=0;
   update vblogger v set v.weekly_video_count=0;
COMMIT;   
   update vblogger v set v.monthly_video_count=(select count(*) from web_video w where GET_TIME_FORMAT(1,w.video_day)=v_now_date and v.site_id=w.site_id and v.name=w.video_author group by w.site_id,w.video_author) ;
--按周統(tǒng)計(jì)
     SELECT TO_NUMBER(TO_CHAR(sysdate,'YYYYFMWW'))INTO v_now_date FROM dual;
     update vblogger v set v.weekly_video_count=(select count(*) from web_video w where GET_TIME_FORMAT(3,w.video_day)=v_now_date and v.site_id=w.site_id and v.name=w.video_author group by w.site_id,w.video_author) ;
COMMIT;
end;