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;