oracle建立定时job调用存储过程更新表数据

–2017.8.24建立定时job，调用存储过程，更新表格count_bzld，count_wfy，count_yfy(示例)

1.将count_bzld，count_wfy，count_yfy中字段名换为 ‘QX’+‘QXDM’

2.插入数据
insert into COUNT_BZLD(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_BZLD group by qxdm);

insert into COUNT_YFY(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_YFY group by qxdm);

insert into COUNT_WFY(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_WFY group by qxdm);

3.建立存储过程
create or replace procedure pro_updatecount as
begin

insert into COUNT_BZLD(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_BZLD group by qxdm);

insert into COUNT_YFY(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_YFY group by qxdm);

insert into COUNT_WFY(COUNTDATE,QX5069)
select trunc(sysdate),sum(decode(qxdm,‘5069’,COUNTDATA,0) )QX5069
from (select qxdm,count(1) as countdata from address_WFY group by qxdm);
end;

4.建立定时job,定时调用存储过程

begin
dbms_scheduler.create_job(
job_name => ‘BZDZK_YINGSHANG.JOB_UPDATECOUNT’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘pro_updatecount’,
start_date => to_date(‘2017-08-24 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’),
repeat_interval => ‘freq=daily;interval=1’,
enabled => true,
comments => ‘统计面板’);
end;