Oracle月表转总表

create or replace procedure ExportBill is
m_Sql varchar2(2048);
m_cherror varchar2(256);

v_nowtime number(10);
v_starttime number(10);
v_tmpriqi varchar2(32);
v_tablename varchar2(32);
v_day varchar2(32);
v_nday number(3);
v_hour varchar2(32);
v_nhour number(3);

begin

select to_number((sysdate -To_date('1970-01-01 08-00-00', 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) into v_nowtime from dual;
v_starttime := v_nowtime - 86400 * 5;

SELECT TO_CHAR(v_nowtime/86400 + TO_DATE('19700101080000','YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') into v_tmpriqi FROM DUAL;
v_day := substr(v_tmpriqi,7,2);
v_nday := to_number(v_day);
v_hour := substr(v_tmpriqi,9,2);
v_nhour := to_number(v_hour);

v_tmpriqi := substr(v_tmpriqi,0,6);
v_tablename := 'mp_bill' || v_tmpriqi;

if (v_nhour = 12) then
--删除100天前的数据
m_Sql := 'delete from mp_bill where datetime < ' || to_char(v_nowtime-86400*100);
begin
execute immediate m_Sql;
commit;
exception
when others then
m_cherror := substr(SQLERRM, 1, 200);
end;
end if;

if (v_nday < 5) then
--取本月数据
m_Sql := 'merge into mp_bill mb using (
select a.mp_id,a.power_type,a.datetime,a.total_bill,a.apex_bill,a.peak_bill,a.flat_bill,a.valley_bill,a.quality_code from ' || v_tablename || ' a where a.datetime > ' || to_char(v_starttime) || ') ins
on (mb.mp_id = ins.mp_id and mb.power_type = ins.power_type and mb.datetime = ins.datetime)
when matched then
update set mb.total_bill = ins.total_bill,mb.apex_bill = ins.apex_bill,mb.peak_bill = ins.peak_bill,mb.flat_bill = ins.flat_bill,mb.valley_bill = ins.valley_bill,mb.quality_code = ins.quality_code
when not matched then
insert (mb.mp_id,mb.power_type,mb.datetime,mb.total_bill,mb.apex_bill,mb.peak_bill,mb.flat_bill,mb.valley_bill,mb.quality_code)
values (ins.mp_id,ins.power_type,ins.datetime,ins.total_bill,ins.apex_bill,ins.peak_bill,ins.flat_bill,ins.valley_bill,ins.quality_code)';
begin
execute immediate m_Sql;
commit;
exception
when others then
m_cherror := substr(SQLERRM, 1, 200);
end;
--取上月数据
SELECT TO_CHAR(v_starttime/86400 + TO_DATE('19700101080000','YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') into v_tmpriqi FROM DUAL;
v_tmpriqi := substr(v_tmpriqi,0,6);
v_tablename := 'mp_bill' || v_tmpriqi;

m_Sql := 'merge into mp_bill mb using (
select a.mp_id,a.power_type,a.datetime,a.total_bill,a.apex_bill,a.peak_bill,a.flat_bill,a.valley_bill,a.quality_code from ' || v_tablename || ' a where a.datetime > ' || to_char(v_starttime) || ') ins
on (mb.mp_id = ins.mp_id and mb.power_type = ins.power_type and mb.datetime = ins.datetime)
when matched then
update set mb.total_bill = ins.total_bill,mb.apex_bill = ins.apex_bill,mb.peak_bill = ins.peak_bill,mb.flat_bill = ins.flat_bill,mb.valley_bill = ins.valley_bill,mb.quality_code = ins.quality_code
when not matched then
insert (mb.mp_id,mb.power_type,mb.datetime,mb.total_bill,mb.apex_bill,mb.peak_bill,mb.flat_bill,mb.valley_bill,mb.quality_code)
values (ins.mp_id,ins.power_type,ins.datetime,ins.total_bill,ins.apex_bill,ins.peak_bill,ins.flat_bill,ins.valley_bill,ins.quality_code)';
begin
execute immediate m_Sql;
commit;
exception
when others then
m_cherror := substr(SQLERRM, 1, 200);
end;
else
--取本月数据
m_Sql := 'merge into mp_bill mb using (
select a.mp_id,a.power_type,a.datetime,a.total_bill,a.apex_bill,a.peak_bill,a.flat_bill,a.valley_bill,a.quality_code from ' || v_tablename || ' a where a.datetime > ' || to_char(v_starttime) || ') ins
on (mb.mp_id = ins.mp_id and mb.power_type = ins.power_type and mb.datetime = ins.datetime)
when matched then
update set mb.total_bill = ins.total_bill,mb.apex_bill = ins.apex_bill,mb.peak_bill = ins.peak_bill,mb.flat_bill = ins.flat_bill,mb.valley_bill = ins.valley_bill,mb.quality_code = ins.quality_code
when not matched then
insert (mb.mp_id,mb.power_type,mb.datetime,mb.total_bill,mb.apex_bill,mb.peak_bill,mb.flat_bill,mb.valley_bill,mb.quality_code)
values (ins.mp_id,ins.power_type,ins.datetime,ins.total_bill,ins.apex_bill,ins.peak_bill,ins.flat_bill,ins.valley_bill,ins.quality_code)';
begin
execute immediate m_Sql;
commit;
exception
when others then
m_cherror := substr(SQLERRM, 1, 200);
end;
end if;

end ExportBill;