create or replace procedure proc_generate_report(in_date in varchar2) is
v_retCode varchar2(20);
v_retMsg varchar2(500);
begin
v_retCode:= '';
v_retMsg:= '';
insert into view_for_report
select t.id,
max(w.wid) wid,
max(w.wname) wname,
substr(max(t.time), 1, 4) year,
substr(max(t.time), 6, 2) month,
substr(max(t.time), 9, 2) day,
round(avg(temperature1), 2) temperature1,
round(avg(humidity1), 2) humidity1,
round(avg(temperature2), 2) temperature2,
round(avg(humidity2), 2) humidity2,
round(avg(temperature3), 2) temperature3,
round(avg(humidity3), 2) humidity3,
round(avg(temperature4), 2) temperature4,
round(avg(humidity4), 2) humidity4,
round(avg(temperature5), 2) temperature5,
round(avg(humidity5), 2) humidity5,
round(avg(temperature6), 2) temperature6,
round(avg(humidity6), 2) humidity6,
round(avg(temperature7), 2) temperature7,
round(avg(humidity7), 2) humidity7,
round(avg(temperature8), 2) temperature8,
round(avg(humidity8), 2) humidity8,
round(avg(temperature9), 2) temperature9,
round(avg(humidity9), 2) humidity9,
round(avg(temperature10), 2) temperature10,
round(avg(humidity10), 2) humidity10
from originaldata t, warehouse_new w
where t.id = w.client_id
and t.time <= in_date
group by t.id;
commit;
exception
when others then
v_retCode := sqlcode;
v_retMsg := sqlerrm || sysdate;
insert into operationlog
(id, time, behavior, state, reason)
values
(operationlog_id_seq.nextval,
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
'生成报表数据',
0,
v_retMsg);
commit;
end proc_generate_report;