oracle存储过程定时表,oracle定时器，调用存储过程，定时从n张表中取值新增到本地一张表中...

--创建新增本地数据库的存储过程

create or replace

procedure pro_electric_record as

begin

insert into electric_meter_record(id,basestation_id,name,meter_number,createtime)

select sys_guid(),substr(s.sname,0,36),s.sname,s.svalue,sysdate from rtdbvs s where s.sname like ‘%POWER_DQDL%‘;

commit;

end;

--拼接另一张表中的值

select sys_guid(),substr(s.sname,0,36),s.sname,s.svalue,sysdate from rtdbvs s where s.sname like ‘%POWER_DQDL%‘;

--查看存储过程

select * from user_source where type=‘PROCEDURE‘

--删除存储过程

drop procedure pro_electric_record

--dbms_scheduler创建job调度

--查询

select * from dba_scheduler_jobs;

--创建job

begin

dbms_scheduler.create_job(

job_name=>‘electric_meter_record_insert‘,

job_type=>‘STORED_PROCEDURE‘,

job_action=>‘pro_electric_record‘,

start_date=>sysdate,

repeat_interval=>‘FREQ=DAILY;BYHOUR=0;BYMINUTE=00;BYSECOND=0‘,

comments=>‘电表录入表‘,

auto_drop=>false

);

end;

--重点(查询N张表中的数据存储到本地一张表中)

insert into electric_meter_record(id,basestation_id,name,meter_number,createtime)

select sys_guid(),substr(s.sname,0,36),s.sname,s.svalue,sysdate from rtdbvs s where s.sname like ‘%POWER_DQDL%‘;

效果图：

原文：http://www.cnblogs.com/zrui-xyu/p/5609893.html