pkg_ken_si_deletion.pck

数据迁移使用的ETL程序包

create or replace package PKG_KEN_SI_DELETION is

  -- Author  : ADMINISTRATOR
  -- Created : 2006-9-11 9:23:19
  -- Purpose : delete SI information on catalog database

  -- Public type declarations

  -- Public constant declarations

  -- Public variable declarations

  -- Public function and procedure declarations
  procedure del_eiem;
  procedure del_eiemk;
  procedure del_eiemv;

end PKG_KEN_SI_DELETION;
/
create or replace package body PKG_KEN_SI_DELETION is

  procedure del_eiem as
    type RowArray is table of rowid index by binary_integer;
    type SubscrNoArray is table of number(20) index by binary_integer;
  
    l_RowId    RowArray;
    l_SubscrNo SubscrNoArray;
    l_count    number;
    l_TotalRec number;
  
  BEGIN
    select count(1) into l_TotalRec from KSU_SILIST;
    select ceil(l_TotalRec / 10000) into l_count from dual;
  
    while l_count > 0 loop
      select subscr_no, rowid bulk collect
        into l_SubscrNo, l_RowId
        from KSU_SILIST
       where rownum < 10001
         and eiem_status ='N';
    
      forall i in 1 .. l_SubscrNo.count
        delete from arbor.external_id_equip_map where subscr_no = l_SubscrNo(i);
    
      forall i in 1 .. l_RowId.count
        update KSU_SILIST set eiem_status = 'S' where rowid = l_RowId(i);
      commit;
      l_count := l_count - 1;
    end loop;
  
  EXCEPTION
    when others then
      dbms_output.put_line('Error EIEM:');     
      dbms_output.put_line(substrb(sqlerrm, 1, 200));
      ROLLBACK;
  END del_eiem;

  /**************************************************/

  procedure del_eiemk as
    type RowArray is table of rowid index by binary_integer;
    type SubscrNoArray is table of number(20) index by binary_integer;
  
    l_RowId    RowArray;
    l_SubscrNo SubscrNoArray;
    l_count    number;
    l_TotalRec number;
  
  BEGIN
    select count(1) into l_TotalRec from KSU_SILIST;
    select ceil(l_TotalRec / 10000) into l_count from dual;
  
    while l_count > 0 loop
      select subscr_no, rowid bulk collect
        into l_SubscrNo, l_RowId
        from KSU_SILIST
       where rownum < 10001
         and eiemk_status ='N';
    
      forall i in 1 .. l_SubscrNo.count
        delete from arbor.external_id_equip_map_key
         where subscr_no = l_SubscrNo(i);
    
      forall i in 1 .. l_RowId.count
        update KSU_SILIST set eiemk_status = 'S' where rowid = l_RowId(i);
      commit;
      l_count := l_count - 1;
    end loop;
  
  EXCEPTION
    when others then
      dbms_output.put_line('Error EIEMK:');     
      dbms_output.put_line(substrb(sqlerrm, 1, 200));
      ROLLBACK;
  END del_eiemk;

  /***********************************************************/

  procedure del_eiemv as
    type RowArray is table of rowid index by binary_integer;
    type SubscrNoArray is table of number(20) index by binary_integer;
  
    l_RowId    RowArray;
    l_SubscrNo SubscrNoArray;
    l_count    number;
    l_TotalRec number;
  
  BEGIN
    select count(1) into l_TotalRec from KSU_SILIST;
    select ceil(l_TotalRec / 10000) into l_count from dual;
  
    while l_count > 0 loop
      select subscr_no, rowid bulk collect
        into l_SubscrNo, l_RowId
        from KSU_SILIST
       where rownum < 10001
         and eiemv_status ='N';
    
      forall i in 1 .. l_SubscrNo.count
        delete from arbor.external_id_equip_map_view
         where subscr_no = l_SubscrNo(i);
    
      forall i in 1 .. l_RowId.count
        update KSU_SILIST set eiemv_status = 'S' where rowid = l_RowId(i);
      commit;
      l_count := l_count - 1;
    end loop;
  
  EXCEPTION
    when others then
      dbms_output.put_line('Error EIEMV:');    
      dbms_output.put_line(substrb(sqlerrm, 1, 200));
      ROLLBACK;
  END del_eiemv;

begin
  -- Initialization
  null;
end PKG_KEN_SI_DELETION;
/