`
longzhun
  • 浏览: 371926 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

自己曾经写的同步数据的存储过程

阅读更多

-----------------------------------------------------
spool pkg_syn_to_siss.log

prompt
prompt Creating package PKG_SYN_TO_SISS
prompt ================================
prompt
create or replace package Pkg_SYN_TO_SISS is

  -- Author  : LONGZHUN
  -- Created : 2010-9-28 15:33:56
  -- Purpose :

  -- Public type declarations
--同步idecl通用平台数据:按单采集、非按单采集、激活信息、计费方案
PROCEDURE PRO_TY_FEE_BILL;
PROCEDURE PRO_TY_FEE_BILL_ALLDECLARE;
PROCEDURE PRO_TY_IDECL_ACTIVITY;
PROCEDURE PRO_TY_FEE_SCHEME_MAIN;
PROCEDURE PRO_TY_FEE_CORP_PROP;
--同步idecl东莞平台数据:按单采集、非按单采集、激活信息、计费方案
PROCEDURE PRO_DG_FEE_BILL;
PROCEDURE PRO_DG_FEE_BILL_ALLDECLARE;
PROCEDURE PRO_DG_IDECL_ACTIVITY;
PROCEDURE PRO_DG_FEE_SCHEME_MAIN;
PROCEDURE PRO_DG_FEE_CORP_PROP;
--模拟发单
procedure pro_postbill;
end Pkg_SYN_TO_SISS;
/

prompt
prompt Creating package body PKG_SYN_TO_SISS
prompt =====================================
prompt
create or replace package body Pkg_SYN_TO_SISS is


---------------------------------------
--通用版
---------------------------------------

---------------------------------------
--同步按单计费 FEEBILL
---------------------------------------
PROCEDURE PRO_TY_FEE_BILL
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill'
and   t.system_code='1' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill@link_ideclgen t;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_middle
( seq_num,hcode,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,fee_date,print_state,
  vanish_state,balance,monthcount,check_orgcode,sendermail,reg_cn,reg_no,reg_date,contract_no,charge_flag)
select seq_fee_bill_middle.nextval as seq_num,
       t1.hcode,t1.corp_code,t2.password,t1.module_id,t1.bill_id,t1.bill_no,t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,
       t1.vanish_state,t1.balance,t1.monthcount,t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill@link_ideclgen           t1,
     idecl_reg_license@link_ideclgen  t2
where t1.corp_code=t2.corp_code
and   t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin +2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill' and t.system_code='1';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill' and t.system_code='1';
end if;

delete from  t_logs_sys_fee t where t.tablename='fee_bill';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_TY_FEE_BILL;

---------------------------------------
--同步普通计费 FEE_BILL_ALLDECLARE
---------------------------------------
PROCEDURE PRO_TY_FEE_BILL_ALLDECLARE
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill_alldeclare'
and   t.system_code='1';
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill_alldeclare@link_ideclgen ;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_alldeclare t
( t.hcode,t.corp_code,t.module_id,t.bill_id,t.bill_no,t.receive_code,t.send_state,
  t.fee_date,t.print_state,t.vanish_state,t.balance,t.monthcount,t.check_orgcode,
  t.sendermail,t.reg_cn,t.reg_no,t.reg_date,t.contract_no,t.charge_flag)
select SEQ_FEE_BILL_ALLDECLARE.nextval as hcode,t1.corp_code,t1.module_id,t1.bill_id,t1.bill_no,
       t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,t1.vanish_state,t1.balance,t1.monthcount,
       t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill_alldeclare@link_ideclgen t1
where t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin+2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill_alldeclare' and t.system_code='1';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill_alldeclare' and t.system_code='1';
end if;

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_TY_FEE_BILL_ALLDECLARE;


---------------------------------------
--同步激活明细 IDECL_ACTIVITY
---------------------------------------

PROCEDURE PRO_TY_IDECL_ACTIVITY IS
v_count              number;
v_num                number;
v_corp_code          varchar2(50);
v_hardware_serial_no varchar2(300);
v_hardware_no        varchar2(100);
v_dm_type            varchar2(10);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
BEGIN

--删除激活明细记录表中已同步记录 T_LOGS_ACTIVITY
delete from t_logs_activity@link_ideclgen t where t.syn_status='1';
commit;

select count(*) into v_num from idecl_activity_middle where system_code ='1';

if v_num=0 then

--提取数据到本地中间表
insert into idecl_activity_middle
( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,hostname,os,osversion,
  ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch,dm_type,system_code)
select distinct
       t1.corp_code,t1.hardware_serial_no,t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
       t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch,t2.dm_type,'1' as system_code
from idecl_activity@link_ideclgen   t1,
     t_logs_activity@link_ideclgen  t2
where t1.corp_code          = t2.corp_code
and   t1.hardware_serial_no = t2.hardware_serial_no
and   t1.hardware_no        = t2.hardware_no
and   t2.syn_status='0';

update t_logs_activity@link_ideclgen t
set t.syn_status = '1',t.syn_date=sysdate
where  t.syn_status='0';

commit;
end if;

open v_cursor for
select corp_code,hardware_serial_no,hardware_no,dm_type
from idecl_activity_middle t
where t.system_code='1';--1代表idecl通用

loop
fetch v_cursor into v_corp_code,v_hardware_serial_no,v_hardware_no,v_dm_type;

exit when v_cursor%notfound;

if v_dm_type='delete'
then  delete from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '1'
and   t.dm_type            ='delete';
commit;
end if;

if v_dm_type<>'delete'
then  select count(*) into v_count
      from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
      if v_count =0
      then insert into idecl_activity
           ( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
             hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch)
           select corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
                  hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch
           from idecl_activity_middle t
           where t.corp_code          = V_CORP_CODE
           and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
           and   t.hardware_no        = V_HARDWARE_NO
           and   t.system_code        = '1'
           and   t.dm_type           <>'delete' ;
          
      else update idecl_activity t1
           set (t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
                t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch) =
               ( select t2.latest_conn_time,t2.latest_bulletin_time,t2.hostname,t2.os,t2.osversion,
                        t2.ieversion,t2.ip,t2.cpu_no,t2.hardware_no,t2.mac_no,t2.app_ptch
                 from  idecl_activity_middle t2
                 where t1.corp_code          = t2.corp_code
                 and   t1.hardware_serial_no = t2.hardware_serial_no
                 and   t1.hardware_no        = t2.hardware_no
                 and   t2.system_code        = '1'
                 and   t2.dm_type            <>'delete' )
            where t1.corp_code          = V_CORP_CODE
            and   t1.hardware_serial_no = V_HARDWARE_SERIAL_NO
            and   t1.hardware_no        = V_HARDWARE_NO ;
           
       end if;
      
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '1'
and   t.dm_type            <>'delete';
commit;

end if;

end loop;        
     
close v_cursor;

 

END PRO_TY_IDECL_ACTIVITY;


---------------------------------------
--同步计费方案 FEE_SCHEME_MAIN
---------------------------------------
PROCEDURE PRO_TY_FEE_SCHEME_MAIN is
v_count           number;
v_schemeid        number;
v_scheme_newid    number;
v_scheme_oldid    number;
v_scheme_name     varchar2(200);
v_office          varchar2(20);
v_dm_type         varchar2(10);
p_productCode     varchar2(10) default 'IDECL1001';
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

delete from t_logs_activity_fee@link_ideclgen t where t.syn_status='1' and t.table_name='fee_scheme_main';
commit;

open v_cursor for select t1.scheme_id,t1.cname,t1.office,t2.dm_type
                  from t_logs_activity_fee@link_ideclgen t2,
                       fee_scheme_main@link_ideclgen     t1
                  where t2.table_name='fee_scheme_main'
                  and   t2.syn_status ='0'
                  and   t1.scheme_id=t2.corp_code;

loop
fetch v_cursor into v_scheme_oldid,v_scheme_name,v_office,v_dm_type;

exit when v_cursor% notfound;
--判断fee_scheme_main是否有为空,若为空,则v_schemeid为1,否则v_schemeid为最大的scheme_id+1
select max(scheme_id) into v_schemeid from fee_scheme_main;
if v_schemeid is null
then v_schemeid := 1;
else v_schemeid := v_schemeid + 1;
end if;
--如果不是delete操作,则判断中间表中是否有新旧scheme对应记录
if v_dm_type<>'delete'
then select count(*) into v_count
     from fee_scheme_middle t
     where t.scheme_oldid=v_scheme_oldid
     and t.system_code='1' ; --1代表idecl通用
    
     if v_count=0 --如果中间表中没有新旧scheme对应记录,则插入一条新记录,并插入其相关表
     then insert into fee_scheme_middle(scheme_oldid,scheme_newid,system_code)
          values(v_scheme_oldid,v_schemeid,'1');
         
          insert into fee_scheme_main(scheme_id,cname,office)
          values (v_schemeid,v_scheme_name,v_office);
         
          insert into fee_scheme_product(scheme_id,product_code)
          values(v_schemeid,p_productCode);
         
          insert into fee_scheme_person
          (scheme_id,audit_flag,audit_per_name,audit_time,audit_remark,audit_per_id,add_per_id,add_per_name,add_time)
          values(v_schemeid,'0','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'','','','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

          insert into fee_scheme(scheme_id,module_id,utile,mcname)
          select v_schemeid,f.module_id,f.utile,f.mcname
          from fee_scheme@link_ideclgen f
          where f.scheme_id=v_scheme_oldid;

          update t_logs_activity_fee@link_ideclgen t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;

          commit;

     end if;
     if v_count>0 --如果中间表中有新旧scheme对应记录,则用中间表中旧schemeid对应的新schemeid更新其相关表
     then  select max(t.scheme_newid) into v_scheme_newid
           from fee_scheme_middle t
           where t.scheme_oldid=v_scheme_oldid
           and   t.system_code='1' ;--1代表通用
         
          
           update fee_scheme_main set cname=v_scheme_name,office=v_office where scheme_id=v_scheme_newid;
           update fee_scheme_product set product_code=p_productCode where scheme_id=v_scheme_newid;

           --update fee_scheme f2
           --set (f2.module_id,f2.utile,f2.mcname)=( select f1.module_id,f1.utile,f1.mcname
            --                                       from fee_scheme@link_ideclgen  f1,
             --                                           fee_scheme_middle         f3
            --                                       where f1.scheme_id=v_scheme_oldid
             --                                      and   f3.scheme_oldid=f1.scheme_id
             --                                      and   f3.scheme_newid=f2.scheme_id
             --                                      and   f1.module_id=f2.module_id )
            --where f2.scheme_id=v_scheme_newid;
            merge into fee_scheme                            t1
            using ( select tb.scheme_newid as scheme_id,
                           ta.module_id,ta.utile,ta.mcname
                    from fee_scheme@link_ideclgen  ta,
                         fee_scheme_middle         tb
                    where tb.scheme_oldid=ta.scheme_id
                    and   ta.scheme_id=v_scheme_oldid
                    and   tb.system_code ='1'         )      t2
            on (    t1.scheme_id = t2.scheme_id
                and t1.module_id = t2.module_id
                )
            when matched then
            update set t1.utile     = t2.utile,
                       t1.mcname    = t2.mcname
            when not matched then
            insert (scheme_id,module_id,utile,mcname)
            values(t2.scheme_id,t2.module_id,t2.utile,t2.mcname);


           
           
            update t_logs_activity_fee@link_ideclgen t2
            set t2.syn_status='1' ,t2.syn_date=sysdate
            where t2.syn_status='0'
            and   t2.table_name='fee_scheme_main'
            and   t2.corp_code=v_scheme_oldid;
            commit;
        end if;
end if;

if v_dm_type='delete'
then select count(*) into v_count from fee_scheme_middle t where t.scheme_oldid=v_scheme_oldid and t.system_code='1';
     if v_count>0
     then select max(t.scheme_newid) into v_scheme_newid
          from fee_scheme_middle t
          where t.scheme_oldid=v_scheme_oldid
          and t.system_code='1';
         
          delete from fee_scheme_middle where scheme_oldid=v_scheme_oldid and system_code='1';
          delete from fee_scheme_main where scheme_id=v_scheme_newid;
          delete from fee_scheme_product where scheme_id=v_scheme_newid;
          delete from fee_scheme_person where scheme_id=v_scheme_newid;
          delete from fee_scheme where scheme_id=v_scheme_newid;

          update t_logs_activity_fee@link_ideclgen t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;
          commit;
      end if;
end if;


end loop;
close v_cursor;


end PRO_TY_FEE_SCHEME_MAIN;
---------------------------------------
--同步 FEE_CORP_PROP
---------------------------------------

PROCEDURE PRO_TY_FEE_CORP_PROP is
v_scheme_newid       varchar2(10);
v_count              number;
v_num                number;
v_corp_code          fee_corp_prop_middle.corp_code%type;
v_dm_type            fee_corp_prop_middle.dm_type%type;
v_charge_type        fee_corp_prop_middle.charge_type%type;
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin


--删除已处理过的记录
delete from t_logs_activity_fee@link_ideclgen t where t.syn_status='1' and t.table_name='fee_corp_prop';
commit;

select count(*) into v_num from fee_corp_prop_middle where system_code ='1';

if v_num=0 then 
     
     
--提取数据到本地中间表
insert into fee_corp_prop_middle
( corp_code,soft_charge,charge_type,credit,balance,monthstatus,
  creditdays,balancebase,maxhcode,manage_type,dm_type,system_code)
select t1.corp_code,t1.soft_charge,t1.charge_type,t1.credit,t1.balance,t1.monthstatus,
       t1.creditdays,t1.balancebase,t1.maxhcode,'0',t2.dm_type,'1' as system_code
from fee_corp_prop@link_ideclgen                t1,
     t_logs_activity_fee@link_ideclgen          t2
where t1.corp_code     = t2.corp_code
and   t2.table_name    = 'fee_corp_prop'
and   t2.syn_status    = '0';

update t_logs_activity_fee@link_ideclgen t
set t.syn_status = '1',t.syn_date=sysdate
where t.table_name='fee_corp_prop'
and   t.syn_status='0';

commit;
end if;
     
open v_cursor for select t.corp_code,t.charge_type,t.dm_type
                  from fee_corp_prop_middle t
                  where t.system_code='1';
loop
fetch v_cursor into v_corp_code,v_charge_type,v_dm_type;

exit when v_cursor% notfound;
--如果不是delete操作,则判断fee_corp_prop中是否有对应记录
if v_dm_type<>'delete'
then select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
         
     select max(t.scheme_newid) into v_scheme_newid
     from fee_scheme_middle t
     where t.scheme_oldid    = v_charge_type
     and   t.system_code     = '1' ;
         
     if v_count=0
     then insert into fee_corp_prop
                (corp_code,soft_charge,charge_type,credit,balance,monthstatus,creditdays,balancebase,maxhcode)
          select t.corp_code,t.soft_charge,v_scheme_newid,t.credit,t.balance,t.monthstatus,t.creditdays,t.balancebase,0 as maxhcode
          from fee_corp_prop_middle            t
          where t.corp_code     = v_corp_code
          and   t.system_code   = '1'
          and   t.dm_type       <>'delete';
           
     else --update fee_corp_prop f2
          --set (f2.soft_charge,f2.charge_type,f2.credit,f2.monthstatus,f2.creditdays,f2.balancebase)
           --           =(select t.soft_charge,v_scheme_newid,t.credit,t.monthstatus,t.creditdays,t.balancebase
           --             from fee_corp_prop_middle t
           --             where t.corp_code   = v_corp_code
           --             and   t.system_code = '1'
           --             and   t.dm_type <>'delete')    
          --where f2.corp_code=v_corp_code;
      
         update fee_corp_prop f2
         set f2.charge_type = v_scheme_newid
         where f2.corp_code=v_corp_code;

      
      end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '1'
and   t.dm_type      <>'delete';
commit;

end if;
if v_dm_type='delete'
--如果是delete操作,则删除fee_corp_prop中对应记录
then  select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
      if v_count>0
      then delete from fee_corp_prop t where t.corp_code   = v_corp_code;
      end if; 
end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '1'
and   t.dm_type      ='delete';
commit;
     
end loop;
close v_cursor;


end PRO_TY_FEE_CORP_PROP;

 

---------------------------------------
--东莞版
---------------------------------------

---------------------------------------
--同步按单计费 FEEBILL
---------------------------------------
PROCEDURE PRO_DG_FEE_BILL
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill'
and   t.system_code='2' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill@link_eport112 t;

loop

if v_hcode_begin <= v_hcode_end
then
--处理数据
insert into fee_bill_middle
( seq_num,hcode,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,fee_date,print_state,
  vanish_state,balance,monthcount,check_orgcode,sendermail,reg_cn,reg_no,reg_date,contract_no,charge_flag)
select seq_fee_bill_middle.nextval as seq_num,
       t1.hcode,t1.corp_code,t2.password,t1.module_id,t1.bill_id,t1.bill_no,t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,
       t1.vanish_state,t1.balance,t1.monthcount,t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill@link_eport112           t1,
     idecl_reg_license@link_eport112  t2
where t1.corp_code=t2.corp_code
and   t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin +2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill' and t.system_code='2';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill' and t.system_code='2';
end if;

delete from  t_logs_sys_fee t where t.tablename='fee_bill';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
else
     exit;
end if;
end loop;

end PRO_DG_FEE_BILL;


---------------------------------------
--同步普通计费 FEE_BILL_ALLDECLARE
---------------------------------------
PROCEDURE PRO_DG_FEE_BILL_ALLDECLARE
is
v_hcode_begin number;--记录要处理数据的开始HCODE
v_hcode_end   number;--记录要处理数据的结束HCODE
begin

--提取要处理的数据的开始HCODE
select max_hcode into v_hcode_begin
from t_logs_fee t
where t.table_name='fee_bill_alldeclare'
and   t.system_code='2' ;
--提取要处理的数据的结束HCODE
select max(hcode) into v_hcode_end from fee_bill_alldeclare@link_eport112 ;
loop

if v_hcode_begin <= v_hcode_end
then

insert into fee_bill_alldeclare t
( t.hcode,t.corp_code,t.module_id,t.bill_id,t.bill_no,t.receive_code,t.send_state,
  t.fee_date,t.print_state,t.vanish_state,t.balance,t.monthcount,t.check_orgcode,
  t.sendermail,t.reg_cn,t.reg_no,t.reg_date,t.contract_no,t.charge_flag)
select SEQ_FEE_BILL_ALLDECLARE.nextval as hcode,t1.corp_code,t1.module_id,t1.bill_id,t1.bill_no,
       t1.receive_code,t1.send_state,t1.fee_date,t1.print_state,t1.vanish_state,t1.balance,t1.monthcount,
       t1.check_orgcode,t1.sendermail,t1.reg_cn,t1.reg_no,t1.reg_date,t1.contract_no,t1.charge_flag
from fee_bill_alldeclare@link_eport112 t1
where t1.hcode >  v_hcode_begin
and   t1.hcode <= v_hcode_begin+2000
and   t1.hcode <= v_hcode_end;

--标识已经处理的数据
if v_hcode_begin+2000 <v_hcode_end
then
update t_logs_fee t set t.max_hcode=v_hcode_begin+2000 where t.table_name='fee_bill_alldeclare' and t.system_code='2';
else
update t_logs_fee t set t.max_hcode=v_hcode_end where t.table_name='fee_bill_alldeclare' and t.system_code='2';
end if;

--delete from  t_logs_sys_fee t where t.tablename='fee_bill_alldeclare';

commit;

    v_hcode_begin:=v_hcode_begin+2000;
    else
     exit;
    end if;
end loop;

end PRO_DG_FEE_BILL_ALLDECLARE;


---------------------------------------
--同步激活明细 IDECL_ACTIVITY
---------------------------------------

PROCEDURE PRO_DG_IDECL_ACTIVITY IS
v_count              number;
v_num                number;
v_corp_code          varchar2(50);
v_hardware_serial_no varchar2(300);
v_hardware_no        varchar2(100);
v_dm_type            varchar2(10);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
BEGIN

--删除激活明细记录表中已同步记录 T_LOGS_ACTIVITY
delete from t_logs_activity@link_eport112 t where t.syn_status='1';
commit;

select count(*) into v_num from idecl_activity_middle where system_code ='2';
commit;
if v_num=0 then

--提取数据到本地中间表
insert into idecl_activity_middle
( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,hostname,os,osversion,
  ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch,dm_type,system_code)
select distinct
       t1.corp_code,t1.hardware_serial_no,t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
       t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch,t2.dm_type,'2' as system_code
from idecl_activity@link_eport112   t1,
     t_logs_activity@link_eport112  t2
where t1.corp_code          = t2.corp_code
and   t1.hardware_serial_no = t2.hardware_serial_no
and   t1.hardware_no        = t2.hardware_no
and   t2.syn_status='0';

update t_logs_activity@link_eport112 t
set t.syn_status = '1',t.syn_date=sysdate
where  t.syn_status='0';

commit;
end if;

open v_cursor for
select corp_code,hardware_serial_no,hardware_no,dm_type
from idecl_activity_middle t
where t.system_code='2';--2代表idecl东莞

loop
fetch v_cursor into v_corp_code,v_hardware_serial_no,v_hardware_no,v_dm_type;

exit when v_cursor%notfound;

if v_dm_type='delete'
then  delete from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '2'
and   t.dm_type            ='delete';   
commit;
end if;

if v_dm_type<>'delete'   
then  select count(*) into v_count
      from idecl_activity t
      where t.corp_code          = V_CORP_CODE
      and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
      and   t.hardware_no        = V_HARDWARE_NO ;
     
      if v_count =0
      then insert into idecl_activity
           ( corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
             hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch)
           select corp_code,hardware_serial_no,latest_conn_time,latest_bulletin_time,
                  hostname,os,osversion,ieversion,ip,cpu_no,hardware_no,mac_no,app_ptch
           from idecl_activity_middle t
           where t.corp_code          = V_CORP_CODE
           and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
           and   t.hardware_no        = V_HARDWARE_NO
           and   t.system_code        = '2'
           and   t.dm_type           <>'delete' ;
          
      else update idecl_activity t1
           set (t1.latest_conn_time,t1.latest_bulletin_time,t1.hostname,t1.os,t1.osversion,
                t1.ieversion,t1.ip,t1.cpu_no,t1.hardware_no,t1.mac_no,t1.app_ptch) =
               ( select t2.latest_conn_time,t2.latest_bulletin_time,t2.hostname,t2.os,t2.osversion,
                        t2.ieversion,t2.ip,t2.cpu_no,t2.hardware_no,t2.mac_no,t2.app_ptch
                 from  idecl_activity_middle t2
                 where t1.corp_code          = t2.corp_code
                 and   t1.hardware_serial_no = t2.hardware_serial_no
                 and   t1.hardware_no        = t2.hardware_no
                 and   t2.system_code        = '2'
                 and   t2.dm_type            <>'delete' )
            where t1.corp_code          = V_CORP_CODE
            and   t1.hardware_serial_no = V_HARDWARE_SERIAL_NO
            and   t1.hardware_no        = V_HARDWARE_NO ;
       end if;
      
delete idecl_activity_middle t
where t.corp_code          = V_CORP_CODE
and   t.hardware_serial_no = V_HARDWARE_SERIAL_NO
and   t.hardware_no        = V_HARDWARE_NO
and   t.system_code        = '2'
and   t.dm_type            <>'delete';
commit;

end if;

end loop;        
     
close v_cursor;

 

END PRO_DG_IDECL_ACTIVITY;


---------------------------------------
--同步计费方案 FEE_SCHEME_MAIN
---------------------------------------
PROCEDURE PRO_DG_FEE_SCHEME_MAIN is
v_count           number;
v_schemeid        number;
v_scheme_newid    number;
v_scheme_oldid    number;
v_scheme_name     varchar2(200);
v_office          varchar2(20);
v_dm_type         varchar2(10);
p_productCode     varchar2(10) default 'IDECL1001';
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin

delete from t_logs_activity_fee@link_eport112 t where t.syn_status='1' and t.table_name='fee_scheme_main';
commit;

open v_cursor for select t1.scheme_id,t1.cname,t1.office,t2.dm_type
                  from t_logs_activity_fee@link_eport112 t2,
                       fee_scheme_main@link_eport112     t1
                  where t2.table_name='fee_scheme_main'
                  and   t2.syn_status ='0'
                  and   t1.scheme_id=t2.corp_code;

loop
fetch v_cursor into v_scheme_oldid,v_scheme_name,v_office,v_dm_type;

exit when v_cursor% notfound;
--判断fee_scheme_main是否有为空,若为空,则v_schemeid为1,否则v_schemeid为最大的scheme_id+1
select max(scheme_id) into v_schemeid from fee_scheme_main;
if v_schemeid is null
then v_schemeid := 1;
else v_schemeid := v_schemeid + 1;
end if;
--如果不是delete操作,则判断中间表中是否有新旧scheme对应记录
if v_dm_type<>'delete'
then select count(*) into v_count
     from fee_scheme_middle t
     where t.scheme_oldid=v_scheme_oldid
     and t.system_code='2' ; --2代表idecl东莞
    
     if v_count=0 --如果中间表中没有新旧scheme对应记录,则插入一条新记录,并插入其相关表
     then insert into fee_scheme_middle(scheme_oldid,scheme_newid,system_code)
          values(v_scheme_oldid,v_schemeid,'2');
         
          insert into fee_scheme_main(scheme_id,cname,office)
          values (v_schemeid,v_scheme_name,v_office);
         
          insert into fee_scheme_product(scheme_id,product_code)
          values(v_schemeid,p_productCode);
         
          insert into fee_scheme_person
          (scheme_id,audit_flag,audit_per_name,audit_time,audit_remark,audit_per_id,add_per_id,add_per_name,add_time)
          values(v_schemeid,'0','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'','','','',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));

          insert into fee_scheme(scheme_id,module_id,utile,mcname)
          select v_schemeid,f.module_id,f.utile,f.mcname
          from fee_scheme@link_eport112 f
          where f.scheme_id=v_scheme_oldid;

          update t_logs_activity_fee@link_eport112 t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;

          commit;

     end if;
     if v_count>0 --如果中间表中有新旧scheme对应记录,则用中间表中旧schemeid对应的新schemeid更新其相关表
     then  select max(t.scheme_newid) into v_scheme_newid
           from fee_scheme_middle t
           where t.scheme_oldid=v_scheme_oldid
           and   t.system_code='2' ;--2代表东莞
         
          
           update fee_scheme_main set cname=v_scheme_name,office=v_office where scheme_id=v_scheme_newid;
           update fee_scheme_product set product_code=p_productCode where scheme_id=v_scheme_newid;

           --update fee_scheme f2
           --set (f2.module_id,f2.utile,f2.mcname)=( select f1.module_id,f1.utile,f1.mcname
           --                                        from fee_scheme@link_eport112  f1,
           --                                             fee_scheme_middle         f3
           --                                        where f1.scheme_id=v_scheme_oldid
           --                                        and   f3.scheme_oldid=f1.scheme_id
           --                                        and   f3.scheme_newid=f2.scheme_id
           --                                        and   f1.module_id=f2.module_id )
           -- where f2.scheme_id=v_scheme_newid;
           
            merge into fee_scheme                            t1
            using ( select tb.scheme_newid as scheme_id,
                           ta.module_id,ta.utile,ta.mcname
                    from fee_scheme@link_ideclgen  ta,
                         fee_scheme_middle         tb
                    where tb.scheme_oldid=ta.scheme_id
                    and   ta.scheme_id=v_scheme_oldid
                    and   tb.system_code ='2'         )      t2
            on (    t1.scheme_id = t2.scheme_id
                and t1.module_id = t2.module_id
                )
            when matched then
            update set t1.utile     = t2.utile,
                       t1.mcname    = t2.mcname
            when not matched then
            insert (scheme_id,module_id,utile,mcname)
            values(t2.scheme_id,t2.module_id,t2.utile,t2.mcname);
           
           
           
            update t_logs_activity_fee@link_eport112 t2
            set t2.syn_status='1' ,t2.syn_date=sysdate
            where t2.syn_status='0'
            and   t2.table_name='fee_scheme_main'
            and   t2.corp_code=v_scheme_oldid;
            commit;
        end if;
end if;

if v_dm_type='delete'
then select count(*) into v_count from fee_scheme_middle t where t.scheme_oldid=v_scheme_oldid and t.system_code='0';
     if v_count>0
     then select max(t.scheme_newid) into v_scheme_newid
          from fee_scheme_middle t
          where t.scheme_oldid=v_scheme_oldid
          and t.system_code='2';
         
          delete from fee_scheme_middle where scheme_oldid=v_scheme_oldid and system_code='2';
          delete from fee_scheme_main where scheme_id=v_scheme_newid;
          delete from fee_scheme_product where scheme_id=v_scheme_newid;
          delete from fee_scheme_person where scheme_id=v_scheme_newid;
          delete from fee_scheme where scheme_id=v_scheme_newid;

          update t_logs_activity_fee@link_eport112 t2
          set t2.syn_status='1' ,t2.syn_date=sysdate
          where t2.syn_status='0'
          and   t2.table_name='fee_scheme_main'
          and   t2.corp_code=v_scheme_oldid;
          commit;
        end if;
end if;


end loop;
close v_cursor;


end PRO_DG_FEE_SCHEME_MAIN;
---------------------------------------
--同步 FEE_CORP_PROP
---------------------------------------

PROCEDURE PRO_DG_FEE_CORP_PROP is
v_scheme_newid       varchar2(10);
v_count              number;
v_num                number;
v_corp_code          fee_corp_prop_middle.corp_code%type;
v_dm_type            fee_corp_prop_middle.dm_type%type;
v_charge_type        fee_corp_prop_middle.charge_type%type;
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
begin


--删除已处理过的记录
delete from t_logs_activity_fee@link_eport112 t where t.syn_status='1' and t.table_name='fee_corp_prop';
commit;

select count(*) into v_num from fee_corp_prop_middle where system_code ='2';
commit;
if v_num=0 then 
     
     
--提取数据到本地中间表
insert into fee_corp_prop_middle
( corp_code,soft_charge,charge_type,credit,balance,monthstatus,
  creditdays,balancebase,maxhcode,manage_type,dm_type,system_code)
select t1.corp_code,t1.soft_charge,t1.charge_type,t1.credit,t1.balance,t1.monthstatus,
       t1.creditdays,t1.balancebase,t1.maxhcode,'0',t2.dm_type,'2' as system_code
from fee_corp_prop@link_eport112                t1,
     t_logs_activity_fee@link_eport112          t2
where t1.corp_code     = t2.corp_code
and   t2.table_name    = 'fee_corp_prop'
and   t2.syn_status    = '0';

update t_logs_activity_fee@link_eport112 t
set t.syn_status = '1',t.syn_date=sysdate
where t.table_name='fee_corp_prop'
and   t.syn_status='0';

commit;
end if;
     
open v_cursor for select t.corp_code,t.charge_type,t.dm_type
                  from fee_corp_prop_middle t
                  where t.system_code='2';
loop
fetch v_cursor into v_corp_code,v_charge_type,v_dm_type;
exit when v_cursor% notfound;
      --如果不是delete操作,则判断fee_corp_prop中是否有对应记录
if v_dm_type<>'delete'
then select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
     select max(t.scheme_newid) into v_scheme_newid
     from fee_scheme_middle t
     where t.scheme_oldid    = v_charge_type
     and   t.system_code     = '2' ;
         
     if v_count=0
     then insert into fee_corp_prop
             (corp_code,soft_charge,charge_type,credit,balance,monthstatus,creditdays,balancebase,maxhcode)
          select t.corp_code,t.soft_charge,v_scheme_newid,t.credit,t.balance,t.monthstatus,t.creditdays,t.balancebase,0
          from fee_corp_prop_middle            t
          where t.corp_code     = v_corp_code
          and   t.system_code   = '2'
          and   t.dm_type <>'delete';
           
     else --update fee_corp_prop f2
          --set (f2.soft_charge,f2.charge_type,f2.credit,f2.monthstatus,f2.creditdays,f2.balancebase)
          --       =(select t.soft_charge,v_scheme_newid,t.credit,t.monthstatus,t.creditdays,t.balancebase
          --         from fee_corp_prop_middle t
           --        where t.corp_code   = v_corp_code
           --        and   t.system_code = '2'
           --        and   t.dm_type <>'delete')    
          --where f2.corp_code=v_corp_code;
         
         update fee_corp_prop f2
         set f2.charge_type = v_scheme_newid
         where f2.corp_code=v_corp_code;
         
     end if;
--删除临时表对应记录   
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '2'
and   t.dm_type      <>'delete';
commit;
end if;
       --如果是delete操作,则删除fee_corp_prop中对应记录
if v_dm_type='delete' then
         select count(*) into v_count from fee_corp_prop t where t.corp_code=v_corp_code;
         if v_count>0 then
            delete from fee_corp_prop t
            where t.corp_code   = v_corp_code;
         end if; 
 end if;
--删除临时表对应记录
delete from fee_corp_prop_middle t
where t.corp_code    = v_corp_code
and   t.system_code  = '2'
and   t.dm_type      ='delete';
commit;
     
end loop;
close v_cursor;


end PRO_DG_FEE_CORP_PROP;

--模拟发单
procedure pro_postbill
is
v_num_begin number;--记录要处理数据的开始HCODE
v_num_end   number;--记录要处理数据的结束HCODE
v_seq_num   number;
V_CORP_CODE         VARCHAR2(50);
V_PASSWORD          VARCHAR2(50);
V_MODULE_ID         VARCHAR2(50);
V_BILL_ID           VARCHAR2(100);
V_BILL_NO           VARCHAR2(30);
V_RECEIVE_CODE      VARCHAR2(50);
V_SEND_STATE        VARCHAR2(1);
V_SENDERMAIL        VARCHAR2(100);
V_CHECK_ORGCODE     VARCHAR2(30);
V_REG_CN            VARCHAR2(100);
V_REG_NO            VARCHAR2(100);
V_REG_DATE          VARCHAR2(30);
V_CONTRACT_NO       VARCHAR2(100);
type TYPE_CUR is ref cursor;
v_cursor TYPE_CUR ;
v_msg               varchar2(500);
begin

select min(seq_num) into v_num_begin from fee_bill_middle;
select max(seq_num) into v_num_end from fee_bill_middle;

loop

if v_num_begin <= v_num_end
then open v_cursor for
     select seq_num,corp_code,password,module_id,bill_id,bill_no,receive_code,send_state,
            sendermail,check_orgcode,reg_cn,reg_no,reg_date,contract_no
     from fee_bill_middle t
     where t.seq_num >=  v_num_begin
     and   t.seq_num < v_num_begin+100
     and   t.seq_num <= v_num_end;

     loop
     fetch v_cursor into v_seq_num,V_CORP_CODE,V_PASSWORD,V_MODULE_ID,V_BILL_ID,V_BILL_NO,V_RECEIVE_CODE,
                         V_SEND_STATE,V_SENDERMAIL,V_CHECK_ORGCODE,V_REG_CN,V_REG_NO,V_REG_DATE,V_CONTRACT_NO;

     exit when v_cursor%notfound;

     select POSTBILLDATAV3(V_CORP_CODE,V_PASSWORD,V_MODULE_ID,V_BILL_ID,V_BILL_NO,V_RECEIVE_CODE,V_SEND_STATE,
                           V_SENDERMAIL,V_CHECK_ORGCODE,V_REG_CN,V_REG_NO,V_REG_DATE,V_CONTRACT_NO)
     into v_msg from dual;
    
     delete from fee_bill_middle where seq_num=v_seq_num;
     commit;
    
     end loop;
     close v_cursor;
    
     v_num_begin:=v_num_begin+100;
else
     exit;
end if;
end loop;
 
end pro_postbill;


end Pkg_SYN_TO_SISS;
/


spool off

分享到:
评论

相关推荐

    dblink创建与数据同步

    该存储过程实现源数据库按照时间同步更新计算库的过程 实现表的同步以及动态表某期间的数据同步。 该存储过程主要演示了: 1.dblink创建和dblink的使用; 2.如何使用字典表和游标实现选择性数据同步。

    java多个数据库实现数据同步

    1. **触发器和存储过程**:在每个数据库中设置触发器,当数据发生变化时,触发器会调用存储过程,将更改发送到其他数据库。然而,这种方法的缺点在于它依赖于数据库特定的语法,可能不适用于跨不同数据库系统的同步...

    基于FlinkSQLCDC的实时数据同步方案

    很明显这种模式是不可持续发展的,这种双写到各个数据存储系统中可能导致不可维护和扩展,数据一致性问题等,需要引入分布式事务,成本和复杂度也随之增加。我们可以通过CDC(ChangeDataCapture)工具进行解除耦合,...

    数据同步,数据异地传输

    首先,数据同步是指将一个数据源中的信息实时或定期地更新到另一个数据源的过程,以保持两个或多个数据存储位置的一致性。这通常应用于数据库系统、文件服务器、云存储服务等。数据同步可以是单向的,即信息只从一个...

    一种面向分布式读写分离系统的数据同步策略.pdf

    1. 同步效率提升:由于只对发生更改的分区进行数据同步,避免了大量无变化数据的重复传输和存储,大大提高了数据同步的效率。 2. 空间占用降低:减少同步不必要的分区,降低了对本地存储和内存资源的消耗,节约了...

    oracle使用dblink高速复制表数据的动态存储过程

    同步完成后自动删除动态生成的存储过程、dblink。 本程序不同步blob和clob字段,玩oracle的人都懂,用sql同步此类数据,速度上不去,此类数据应该要用导出导入的方式提升速度。 具体请参阅程序,可根据自已需要进行...

    c#定时服务数据同步源代码

    用户可以根据需求设置每天、每小时甚至每分钟同步数据的具体时刻。 在数据同步的过程中,有几种常见的同步策略,如全量同步和增量同步。全量同步会将所有数据复制到目标系统,而增量同步只传输自上次同步以来发生...

    基于FPGA的多路数据同步采编存储系统设计.pdf

    该系统的应用展示了FPGA在多路数据同步采编存储领域中的优势,它不仅提高了数据采集的效率和可靠性,还通过小型化设计降低了设备的体积和重量,有利于减轻飞行器负载。系统的设计和应用也进一步推动了飞行试验技术的...

    Delphi中调用oracle的存储过程返回数据集

    Delphi 中调用 Oracle 的存储过程返回数据集 Delphi 中调用 Oracle 的存储过程返回数据集是指在 Delphi 应用程序中调用 Oracle 数据库中的存储过程,并将存储过程的返回结果集显示在 Delphi 应用程序中。这种方式...

    mysql历史数据同步到clickhouse 已测试

    MySQL作为流行的开源关系型数据库,广泛应用于事务处理和实时数据存储。在需要对历史数据进行深度分析时,将MySQL的数据同步到ClickHouse可以显著提升查询效率。 **1. 数据同步方案** - **binlog同步**: 利用MySQL...

    MSSQL_SERVER数据库增量同步软件(数据库结构及数据)

    MSSQL_SERVER数据库增量同步软件(数据库结构及数据): 可以增量同步的....7.同步表结构,此功能是只同步结构,不同步数据.(会同步表的结构,包含所有索引,默认值,主键.和属性.同步存储过程.函数.视图.触发器)

    Informatica调用存储过程图文流程

    * 数据同步:使用 Informatica 调用存储过程可以实时同步数据。 * 报表生成:使用 Informatica 调用存储过程可以生成复杂的报表。 Informatica 调用存储过程是一个功能强大、灵活的数据集成解决方案,可以满足各种...

    Oracle+DataX+存储过程实现异构库之间的数据同步

    4. 在Oracle数据库中创建存储过程,该过程会执行shell脚本,启动数据同步任务。 5. 执行存储过程,启动数据同步,完成后可根据需要进行日志查看和结果验证。 这个过程充分展示了IT技术在数据管理领域的综合应用,...

    oracle数据库数据同步

    压缩包中包含的文件:数据库建表、存储过程(生成指定格式的字符串,供后台服务写文件)、数据同步的后台服务,三个文件; 希望对刚好有这需求的人有帮助,同时也欢迎指正程序中的不足,共同进步。

    SQL2000数据同步

    在信息技术领域,数据同步是确保多台服务器间数据一致性的关键操作,尤其在企业级应用中,如需在不同地理位置的服务器间实现数据同步,这一过程变得尤为重要。本篇文章将深入探讨SQL Server 2000环境下如何进行数据...

    异步数据同步组件

    异步数据同步组件在技术需求方面,不仅涉及到数据的存储、描述、传输和加密等基础技术,还包括了数据与业务操作之间的联动,确保数据流转能够激活业务操作,反之亦然。为了实现这些功能,组件支持多种数据存在方式的...

    如何在两个SQLSERVER之间数据同步

    数据同步是在两个或多个数据库之间保持数据一致性的过程。对于SQL Server而言,其提供了多种数据同步的方法,其中最常用的就是复制技术。复制技术不仅可以用于SQL Server实例之间,还可以用于跨不同类型的数据库管理...

Global site tag (gtag.js) - Google Analytics