那些年我写过的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;

   7.相似博客推荐

      1:那些年我写过的存储过程与计划任务

      2:如何创建数据库链接DBLINK

      3:统计分析－存储过程