那些年我寫過的oracle存儲過程和計劃任務

前言：   

    那些年我寫過的存儲過程和計劃任務，先将其寫成部落格，歸納總結，友善以後檢視。

目錄：

   1. 建立綁定使用者數存儲過程

2. 建立業務統計資料存儲過程

3. 建立通路量存儲過程

   4. 計劃任務：明天0晨執行定時任務

   5. 建立注冊使用者數存儲過程

   6. 建立計劃任務：明天淩晨定時執行存儲過程

   7. 相似部落格推薦

---------------------全局庫--------------------------------
1：建立綁定使用者數存儲過程
create or replace procedure bind_statis_pro is
begin
  delete from w_bind_statistics@dbltest;
  insert into w_bind_statistics@dbltest
    select t.organ_code,
           '01',
           to_date(to_char(t.bind_time, 'yyyy-mm-dd'), 'yyyy-mm-dd'),
           count(0)
      from w_busi_user_bind t
     where to_char(t.bind_time, 'yyyy-mm-dd') <
           to_char(sysdate, 'yyyy-mm-dd')
     group by t.organ_code, to_char(t.bind_time, 'yyyy-mm-dd');
  commit;
end;

2：建立業務統計資料存儲過程

create or replace procedure busi_statis_pro is
begin
  delete from w_busi_statistics@dbltest;
  insert into w_busi_statistics@dbltest
    select s.area_no,
           '01',
           to_date(s.req_date, 'yyyy-mm-dd'),
           nvl(a.count, 0),
           nvl(b.count, 0),
           nvl(c.count, 0),
           nvl(d.count, 0),
           nvl(e.count, 0),
           nvl(f.count, 0),
           nvl(g.count, 0)
    
      from (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   count(0) count
              from pub_queue_app a
             where to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no, to_char(a.req_date, 'yyyy-mm-dd')) S,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '001'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) A,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '003'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) B,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '005'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) C,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '006'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) D,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '007'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) E,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '008'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) F,
           
           (select a.area_no,
                   to_char(a.req_date, 'yyyy-mm-dd') req_date,
                   a.type_code,
                   count(0) count
              from pub_queue_app a
             where a.type_code = '009'
               and to_char(a.req_date, 'yyyy-mm-dd') <
                   to_char(sysdate, 'yyyy-mm-dd')
             group by a.area_no,
                      to_char(a.req_date, 'yyyy-mm-dd'),
                      a.type_code) G
    
     where s.area_no = a.area_no(+)
       and s.area_no = b.area_no(+)
       and s.area_no = c.area_no(+)
       and s.area_no = d.area_no(+)
       and s.area_no = e.area_no(+)
       and s.area_no = f.area_no(+)
       and s.area_no = g.area_no(+)
       and s.req_date = a.req_date(+)
       and s.req_date = b.req_date(+)
       and s.req_date = c.req_date(+)
       and s.req_date = d.req_date(+)
       and s.req_date = e.req_date(+)
       and s.req_date = f.req_date(+)
       and s.req_date = g.req_date(+)
     order by to_date(s.req_date, 'yyyy-mm-dd') desc;

  commit;
end;

3：建立通路量存儲過程

create or replace procedure visit_statis_pro is
begin
  delete from w_visit_statistics@dbltest;
  insert into w_visit_statistics@dbltest
    select '01',
           to_date(to_char(t.visit_first_action_time, 'yyyy-mm-dd'),
                   'yyyy-mm-dd'),
           sum(t.visit_total_actions)
      from w_log_visit t
     where to_char(t.visit_first_action_time, 'yyyy-mm-dd') <
           to_char(sysdate, 'yyyy-mm-dd')
     group by to_char(t.visit_first_action_time, 'yyyy-mm-dd');
  commit;
end;

4：計劃任務：明天0晨執行定時任務

declare
  v_job number;
begin
  sys.dbms_job.submit( v_job,
                       'bind_statis_pro;busi_statis_pro;visit_statis_pro;',
                      sysdate,
                       'sysdate+1');
                       
  commit;
end;


---------------------------------使用者庫----------------------------------------------------
5：建立注冊使用者數存儲過程
create or replace procedure regist_statis_pro is
begin
  insert into w_regist_statistics@dbltest
    select t.area_no,
           '01',
           to_date(to_char(t.reg_date, 'yyyy-mm-dd'), 'yyyy-mm-dd'),
           count(0)
      from w_users t
     where to_char(t.reg_date, 'yyyy-mm-dd') <
           to_char(sysdate, 'yyyy-mm-dd')
    
     group by t.area_no, to_char(t.reg_date, 'yyyy-mm-dd');

  commit;
end;

6:建立計劃任務：明天淩晨定時執行存儲過程

declare
  v_job number;
begin
  sys.dbms_job.submit( v_job,
                       'regist_statis_pro;',
                      sysdate,
                       'sysdate+1');
                       
  commit;
end;