前言:
那些年我写过的存储过程和计划任务,先将其写成博客,归纳总结,方便以后查看。
目录:
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.相似博客推荐