配合JOB定时调度使用
/*******************************************************
author:zuozewei
time :2018/01/15
功能:抽取z_ddwpt表所有测点最新数据插入z_his表内
说明:通过定时任务来完成自动插入任务,插入recordtime为当前插入系统时间
eg:exec get_ddwpt_proc();
********************************************************/
create or replace procedure get_ddwpt_proc
is
v_sysdate TIMESTAMP; --插入记录时间
v_columnvalue1 varchar2(500); --字段1
cursor mycur is
with tb1 as(
select max(t.gathertime) gathertime,t.columnvalue2
from z_ddwpt t
group by t.columnvalue2
)
select t1.gathertime,t2.recordtime,t2.columnvalue1,t1.columnvalue2,t2.columnvalue3
from z_ddwpt t2 inner join tb1 t1
on t2.gathertime = t1.gathertime and t2.columnvalue2 = t1.columnvalue2;
emp_row z_ddwpt%rowtype;
begin
--取当前系统时间
select systimestamp into v_sysdate from dual;
FOR emp_row IN mycur
loop
--对columnvalue1保留4位小数
v_columnvalue1 :=round(emp_row.columnvalue1,4);
--插入数据
insert into z_his
(id,
schemaid,
gathertime,
recordtime,
columnvalue1,
columnvalue2,
columnvalue3)
values
(sys_guid(),
'd6_006_0001',
emp_row.gathertime,
v_sysdate,
v_columnvalue1,
emp_row.columnvalue2,
emp_row.columnvalue3);
end loop ;
commit ;
end;
/