新建过程和JOb的脚本
create or replace procedure Pro_cal_cltx is
-----------------------------------------
-- Author : wang chaoqun
-- Created : 2010-9-25 18:38:03
-- Purpose : 用来预计算车辆通行表，每小时执行一次，每天晚上多索引进行（分钟、小时、天、月表）
--修改人：
--修改内容：
--修改时间：
-----------------------------------------
v_day date;
v_yesterday date;
begin
---参数初始化
select to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd'),
to_date(to_char(sysdate - 1, 'yyyymmdd'), 'yyyymmdd')
into v_day, v_yesterday
from dual;
if to_char(sysdate, 'HH24') = '01' then
---分钟表
delete from traffic_min where tjsj >= v_yesterday;
insert into traffic_min nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd HH24:') ||
to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
'yyyy-MM-dd HH24:mi'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_yesterday
group by to_char(jgsj, 'yyyy-MM-dd HH24:') ||
to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
road,
fxbh,
cdh;
---小时表
delete from traffic_hour where tjsj >= v_yesterday;
insert into traffic_hour nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd HH24'), 'yyyy-MM-dd HH24'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_yesterday
group by to_char(jgsj, 'yyyy-MM-dd HH24'), road, fxbh, cdh;
---天表
delete from traffic_day where tjsj >= v_yesterday;
insert into traffic_day nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_yesterday
group by to_char(jgsj, 'yyyy-MM-dd'), road, fxbh, cdh;
---月表
delete from traffic_month
where tjsj >=
to_date(to_char(add_months(v_day, -1), 'yyyy-MM') || '-01',
'yyyy-MM-dd');
insert into traffic_month nologging
select to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from traffic_day
where tjsj >=
to_date(to_char(add_months(v_day, -1), 'yyyy-MM') || '-01',
'yyyy-MM-dd')
group by to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),road, fxbh, cdh;
----索引重建
-----------------
else
---分钟表
delete from traffic_min where tjsj >= v_day;
insert into traffic_min nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd HH24:') ||
to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
'yyyy-MM-dd HH24:mi'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_day
group by to_char(jgsj, 'yyyy-MM-dd HH24:') ||
to_char(floor(to_number(to_char(jgsj, 'MI')) / 5) * 5),
road,
fxbh,
cdh;
---小时表
delete from traffic_hour where tjsj >= v_yesterday;
insert into traffic_hour nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd HH24'), 'yyyy-MM-dd HH24'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_day
group by to_char(jgsj, 'yyyy-MM-dd HH24'), road, fxbh, cdh;
---天表
delete from traffic_day where tjsj >= v_yesterday;
insert into traffic_day nologging
select to_date(to_char(jgsj, 'yyyy-MM-dd'), 'yyyy-MM-dd'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from cltx
where jgsj >= v_yesterday
group by to_char(jgsj, 'yyyy-MM-dd'), road, fxbh, cdh;
---月表
delete from traffic_month
where tjsj >=
to_date(to_char(v_day, 'yyyy-MM') || '-01', 'yyyy-MM-dd');
insert into traffic_month nologging
select to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),
road,
fxbh,
cdh,
round(sum(clsd) / count(1), 1),
count(1) ll,
1,
0,
0
from traffic_day
where tjsj >=
to_date(to_char(v_day, 'yyyy-MM') || '-01', 'yyyy-MM-dd')
group by to_date(to_char(tjsj, 'yyyy-MM') || '-01', 'yyyy-MM-dd'),road, fxbh, cdh;
end if;
commit;
end Pro_cal_cltx;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(job => X,
what => 'begin
Pro_cal_cltx;
end;',
next_date => to_date('16-11-2010 10:30:29',
'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/24');
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
commit;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17179887/viewspace-678192/ ，如需转载，请注明出处，否则将追究法律责任。
