需求
每小时进行一次查询统计数据，并将数据插入到自定义的表当中
效果
创建存储过程
create or replace procedure summary_data as
v_dcppv number;
v_jd12h number;
v_dby number;
v_xby number;
v_bj number;
v_bn number;
v_zdzf number;
v_wxzf number;
v_jfzf number;
v_yhdj number;
v_dgzs number;
v_tds number;
v_tds2 number;
begin
--单次
select count(1)
into v_dcppv
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (0, 1)
and order_time IN (48, 30)
and a.user_id like '116%';
--酒店
select count(1)
into v_jd12h
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (0)
and order_time IN (12)
and a.user_id like '116%';
--单包月
select count(1)
into v_dby
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (2)
and order_time IN (1)
and a.paytype <> '0'
and a.user_id like '116%';
--续包月
select count(1)
into v_xby
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (2)
and order_time IN (0)
and a.user_id like '116%';
--包季
select count(1)
into v_bj
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (2)
and order_time IN (3)
and a.user_id like '116%';
--包年
select count(1)
into v_bn
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and order_type in (2)
and order_time IN (12)
and a.user_id like '116%';
--账单支付13
select count(1)
into v_zdzf
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and a.paytype = '1'
and a.user_id like '116%';
--微信支付7
select count(1)
into v_wxzf
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and a.paytype = '3'
and a.user_id like '116%';
--积分支付1
select count(1)
into v_jfzf
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and a.paytype = '2'
and a.user_id like '116%';
--一户多机2
select count(1)
into v_yhdj
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and a.paytype = '0'
and a.user_id like '116%';
--订购总数23
select count(1)
into v_dgzs
from iptvsp_wt_req a
where servicecode = 'JK0008'
and a.instime between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and orderstatus = 1
and a.paytype <> '0008'
and a.user_id like '116%';
--退订数7
--6
select count(1)
into v_tds
from iptvsp_order a
where a.end_date between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and a.order_state = '0002'
and a.user_id like '116%'
and a.pic_type <> '0008'
order by a.start_date desc;
--1
select count(1)
into v_tds2
from iptvsp_order_his a
where a.end_date between trunc(sysdate, 'hh') - 1 / 24 and
trunc(sysdate, 'hh') - 1 / 86400
and a.order_state = '0002'
and a.user_id like '116%'
and a.pic_type <> '0008'
order by a.start_date desc;
dbms_output.put_line('单次PPV：' || v_dcppv);
dbms_output.put_line('酒店：' || v_jd12h);
dbms_output.put_line('单包月：' || v_dby);
dbms_output.put_line('续包月：' || v_xby);
dbms_output.put_line('包季：' || v_bj);
dbms_output.put_line('包年：' || v_bn);
dbms_output.put_line('账单支付：' || v_zdzf);
dbms_output.put_line('微信支付：' || v_wxzf);
dbms_output.put_line('积分支付：' || v_jfzf);
dbms_output.put_line('一户多机：' || v_yhdj);
dbms_output.put_line('订购总数：' || v_dgzs);
dbms_output.put_line('当前退订数：' || v_tds);
dbms_output.put_line('历史退订数：' || v_tds2);
v_tds := v_tds + v_tds2;
dbms_output.put_line('退订数总数：' || v_tds);
-- dbms_output.put_line('退订数：' || v_tds+v_tds2);
insert into tmp_slb2
(start_date,
end_date,
dcppv,
jd12h,
dby,
xby,
bj,
bn,
zdzf,
wxzf,
jfzf,
yhdj,
dgzs,
tds)
values
(trunc(sysdate, 'hh') - 1 / 24,
trunc(sysdate, 'hh') - 1 / 86400,
v_dcppv,
v_jd12h,
v_dby,
v_xby,
v_bj,
v_bn,
v_zdzf,
v_wxzf,
v_jfzf,
v_yhdj,
v_dgzs,
v_tds);
commit;
end;
执行存储过程
SQL> set serveroutput on
SQL> exec summary_data;
创建自定义表
将存储过程执行的查询数据插入到自定义表中
-- Create table
create table TMP_SLB2(start_date date,
end_date date,
dcppv number(8),
jd12h number(8),
dby number(8),
xby number(8),
bj number(8),
bn number(8),
zdzf number(8),
wxzf number(8),
jfzf number(8),
yhdj number(8),
dgzs number(8),
tds number(8)
) tablespace IPTVDB pctfree 10 initrans 1 maxtrans 255 storage(initial 192K next 8K
minextents 1
maxextents
unlimited);
-- Add comments to the columns
comment on column TMP_SLB2.start_date is '开始时间';
comment on column TMP_SLB2.end_date is '截止时间';
comment on column TMP_SLB2.dcppv is '单次ppv';
comment on column TMP_SLB2.jd12h is '酒店12h';
comment on column TMP_SLB2.dby is '单包月';
comment on column TMP_SLB2.xby is '续包月';
comment on column TMP_SLB2.bj is '包季';
comment on column TMP_SLB2.bn is '包年';
comment on column TMP_SLB2.zdzf is '账单支付';
comment on column TMP_SLB2.wxzf is '微信支付';
comment on column TMP_SLB2.jfzf is '积分支付';
comment on column TMP_SLB2.yhdj is '一户多机';
comment on column TMP_SLB2.dgzs is '订购总数';
comment on column TMP_SLB2.tds is '退订数';
创建定时任务
定时任务每小时执行一次存储过程
declare
summary_data_job number;
begin
dbms_job.submit(summary_data_job, 'summary_data;', sysdate, 'sysdate + 1/24');
end;
任务的4个参数
| 字段 | 描述 |
|---|---|
| summary_data_job | 任务名字，没啥卵用，会自动生成job ID |
| summary_data | 任务执行存储过程的名字 |
| sysdate | 任务执行的开始时间 |
| sysdate + 1/24 | 任务重复执行的时间间隔，每小时执行一次 |
运行任务
先查看任务ID，再运行对应的任务
select * from user_jobs;
begin
dbms_job.run(43);
end;
参考
本文由博客一文多发平台 OpenWrite 发布！