create or replace procedure proc_backup_originaldata(retCode out varchar2, -- 返回码
retMsg out varchar2) --返回信息
is
v_now varchar2(20):=to_char(sysdate,'yyyy-mm-dd');
cursor c_backup is
select * from originaldata t where t.time <=v_now;
v_filename varchar2(50):=v_now||'.txt';
v_datastr varchar2(1000):='';
v_file utl_file.file_type;
begin
retCode:='1';
retMsg:='原始数据备份完成';
--备份原始数据，每十分钟一条，取平均值
insert into warehousemonitor--originaltempdata
(id,
temperature1,
temperature2,
temperature3,
temperature4,
temperature5,
temperature6,
temperature7,
temperature8,
temperature9,
temperature10,
humidity1,
humidity2,
humidity3,
humidity4,
humidity5,
humidity6,
humidity7,
humidity8,
humidity9,
humidity10,
concentration1,
concentration2,
concentration3,
time)
select t.id,
round(avg(t.temperature1),2),
round(avg(t.temperature2),2),
round(avg(t.temperature3),2),
round(avg(t.temperature4),2),
round(avg(t.temperature5),2),
round(avg(t.temperature6),2),
round(avg(t.temperature7),2),
round(avg(t.temperature8),2),
round(avg(t.temperature9),2),
round(avg(t.temperature10),2),
round(avg(t.humidity1),2),
round(avg(t.humidity2),2),
round(avg(t.humidity3),2),
round(avg(t.humidity4),2),
round(avg(t.humidity5),2),
round(avg(t.humidity6),2),
round(avg(t.humidity7),2),
round(avg(t.humidity8),2),
round(avg(t.humidity9),2),
round(avg(t.humidity10),2),
round(avg(t.concentration1),2),
round(avg(t.concentration2),2),
round(avg(t.concentration3),2),
max(t.time)
from originaldata t
where t.time <= v_now
group by t.id,
substr(t.time, 1, length(t.time) - 6),
trunc(to_char(to_date(t.time, 'yyyy-mm-dd hh24:mi:ss'), 'mi') / 10);
--生成报表数据
proc_generate_report(v_now);
--写文件
v_file:=utl_file.fopen('BAK',v_filename,'w');--写模式打开文件
for data in c_backup loop
v_datastr:=data.id||'|'||data.ipcode||
'|'||data.temperature1||'|'||data.humidity1||
'|'||data.temperature2||'|'||data.humidity2||
'|'||data.temperature3||'|'||data.humidity3||
'|'||data.temperature4||'|'||data.humidity4||
'|'||data.time||
'|'||data.temperature5||'|'||data.humidity5||
'|'||data.temperature6||'|'||data.humidity6||
'|'||data.temperature7||'|'||data.humidity7||
'|'||data.temperature8||'|'||data.humidity8||
'|'||data.temperature9||'|'||data.humidity9||
'|'||data.temperature10||'|'||data.humidity10||
'|'||data.concentration1||'|'||data.concentration2||'|'||data.concentration3||
'|'||data.backup1||'|'||data.backup2||'|';
utl_file.put_line(v_file,v_datastr);--向文件中写入行
end loop;
utl_file.fclose(v_file);--关闭文件句柄
--删除已备份数据
delete from originaldata t where t.time <=v_now;
commit;
exception when others then
retCode:=sqlcode;
retMsg:=sqlerrm||sysdate;
end proc_backup_originaldata;