在数据库同步过程中常用的同步方法：
说明：
1.建立数据库之间的关联请参考上一篇《给力--oracle 与sql同步》
2.数据库同步脚本如下：
create or replace procedure prod_synchronized_data
as
--作者：chenab
--时间：2011-01-10
--功能：原XXX系统中人事与卡信息同步到XXX管理系统中
begin
-- *********************人事岗位同步 ******************* ----
--插入操作（没有的数据）
insert into _post@door
select * from _post s where not exists(
       select 1 from _post@door d where d.smt_postcode=s.smt_postcode
);
--更新改变的数据
update _post@door d set
(
  smt_postname,
  smt_postexplain,
  smt_dailycode,
  smt_dailyno,
  smt_shortname,
  smt_enlishname,
  smt_mnemonic,
  smt_codeby,
  smt_gbflag
)=
(select
  smt_postname,
  smt_postexplain,
  smt_dailycode,
  smt_dailyno,
  smt_shortname,
  smt_enlishname,
  smt_mnemonic,
  smt_codeby,
  smt_gbflag
  from _post s where s.smt_postcode=d.smt_postcode)
  where exists(
       select 1 from _post t  where t.smt_postcode=d.smt_postcode and
       not (
t.smt_postname = d.smt_postname and 
t.smt_postexplain =d.smt_postexplain and
t.smt_dailycode= d.smt_dailycode and
t.smt_dailyno=d.smt_dailyno and
t.smt_shortname = d.smt_shortname and
t.smt_enlishname = d.smt_enlishname and
t.smt_mnemonic =d.smt_mnemonic and
t.smt_codeby = d.smt_codeby and
t.smt_gbflag = d.smt_gbflag
 )
);
--删除操作
delete from _post@door d where not exists(
       select 1 from _post s  where d.smt_postcode=s.smt_postcode
);
commit;
-- *********************人事身份同步 *******************
--插入操作（没有的数据）
insert into _status@door
select * from _status s where not exists(
       select 1 from _status@door d where d.smt_statuscode=s.smt_statuscode
);
--更新改变的数据
update _status@door d set
(
smt_statusname,
smt_statusexplain,
smt_foriccard,
smt_forshowcard,
smt_dailycode,
smt_dailyno,
smt_shortname,
smt_enlishname,
smt_mnemonic,
smt_codeby,
smt_gbflag)=
(select
smt_statusname,
smt_statusexplain,
smt_foriccard,
smt_forshowcard,
smt_dailycode,
smt_dailyno,
smt_shortname,
smt_enlishname,
smt_mnemonic,
smt_codeby,
smt_gbflag 
  from _status s where s.smt_statuscode=d.smt_statuscode)
  where exists(
       select 1 from _status t  where t.smt_statuscode=d.smt_statuscode and
       not (
  t.smt_statusname=d.smt_statusname and
  t.smt_statusexplain=d.smt_statusexplain and
  t.smt_foriccard=d.smt_foriccard and
  t.smt_forshowcard=d.smt_forshowcard and
  t.smt_dailycode=d.smt_dailycode and
  t.smt_dailyno=d.smt_dailyno and
  t.smt_shortname=d.smt_shortname and
  t.smt_enlishname=d.smt_enlishname and
  t.smt_mnemonic=d.smt_mnemonic and
  t.smt_codeby=d.smt_codeby and
  t.smt_gbflag=d.smt_gbflag
       )
);
--删除操作
delete from _status@door d where not exists(
       select 1 from _status s  where d.smt_statuscode=s.smt_statuscode
);
commit;
-- *********************人事行政组织同步 ******************* ----
--插入操作（没有的数据）
insert into _dept@door
select * from _dept s where not exists(select 1 from _dept@door d where d.smt_deptcode=s.smt_deptcode);
--更新改变的数据
update _dept@door d set (
  smt_deptno,
  smt_deptname,
  smt_level,
  smt_least,
  smt_explain,
  smt_parent_id,
  smt_dailycode,
  smt_dailyno,
  smt_shortname,
  smt_enlishname,
  smt_mnemonic,
  smt_codeby,
  smt_gbflag,
  smt_index)=
(select
  smt_deptno,
  smt_deptname,
  smt_level,
  smt_least,
  smt_explain,
  smt_parent_id,
  smt_dailycode,
  smt_dailyno,
  smt_shortname,
  smt_enlishname,
  smt_mnemonic,
  smt_codeby,
  smt_gbflag,
  smt_index  
from _dept s where s.smt_deptcode=d.smt_deptcode)
  where exists(
  select 1 from _dept t  where t.smt_deptcode=d.smt_deptcode and not (
      t.smt_deptno=d.smt_deptno and
      t.smt_deptname=d.smt_deptname and
      t.smt_level=d.smt_level and
      t.smt_least=d.smt_least and
      t.smt_explain=d.smt_explain and
      t.smt_parent_id=d.smt_parent_id and
      t.smt_dailycode=d.smt_dailycode and
      t.smt_dailyno=d.smt_dailyno and
      t.smt_shortname=d.smt_shortname and
      t.smt_enlishname=d.smt_enlishname and
      t.smt_mnemonic=d.smt_mnemonic and
      t.smt_codeby=d.smt_codeby and
      t.smt_gbflag=d.smt_gbflag and
      t.smt_index=d.smt_index
       )
);
--删除
delete from _dept@door d where not exists(
       select 1 from  _dept s where d.smt_deptcode=s.smt_deptcode
);
commit;
--***********************人事信息同步 *******************----
--插入操作（没有的数据）
insert into _personnel@door (
  smt_personnelid,
  smt_name,
  smt_namespell,
  smt_sex,
  smt_birthday,
  smt_salaryno,
  smt_idcode,
  smt_idno,
  smt_datasource,
  smt_indatetime,
  smt_email,
  smt_deptcode,
  smt_postcode,
  smt_statuscode,
  smt_dossiercode,
  smt_native,
  smt_people,
  smt_married,
  smt_dwelling,
  smt_polity,
  smt_phoneno,
  smt_dailycode,
  smt_dailyno,
  smt_postalcode,
  smt_linkaddress,
  smt_mobiletelephone,
  smt_linkman,
  smt_linkunit,
  smt_linkmanaddress,
  smt_linkmanphone,
  smt_linkmanmobile,
  smt_levelcode,
  smt_countrycode,
  smt_idcardno,
  smt_visadate,
  smt_validitydate,
  smt_visaorgan,
  smt_specialtycode,
  smt_knowledgecode,
  smt_incompanydate,
  smt_salarycode,
  smt_specialtypostcode,
  smt_degreecode
)
select * from _personnel s where not exists(
       select 1 from _personnel@door d where d.smt_personnelid=s.smt_personnelid
);
--更新(更新改变的数据)
update _personnel@door d set
( 
 smt_name,
 smt_namespell,
 smt_sex,
 smt_birthday,
 smt_salaryno,
 smt_idcode,
 smt_idno,
 smt_datasource,
 smt_indatetime,
 smt_email,
 smt_deptcode,
 smt_postcode,
 smt_statuscode,
 smt_dossiercode,
 smt_native,
 smt_people,
 smt_married,
 smt_dwelling,
 smt_polity,
 smt_phoneno,
 smt_dailycode,
 smt_dailyno,
 smt_postalcode,
 smt_linkaddress,
 smt_mobiletelephone,
 smt_linkman,
 smt_linkunit,
 smt_linkmanaddress,
 smt_linkmanphone,
 smt_linkmanmobile,
 smt_levelcode,
 smt_countrycode,
 smt_idcardno,
 smt_visadate,
 smt_validitydate,
 smt_visaorgan,
 smt_specialtycode,
 smt_knowledgecode,
 smt_incompanydate,
 smt_salarycode,
 smt_specialtypostcode,
 smt_degreecode
 
)=
(select
 smt_name,
 smt_namespell,
 smt_sex,
 smt_birthday,
 smt_salaryno,
 smt_idcode,
 smt_idno,
 smt_datasource,
 smt_indatetime,
 smt_email,
 smt_deptcode,
 smt_postcode,
 smt_statuscode,
 smt_dossiercode,
 smt_native,
 smt_people,
 smt_married,
 smt_dwelling,
 smt_polity,
 smt_phoneno,
 smt_dailycode,
 smt_dailyno,
 smt_postalcode,
 smt_linkaddress,
 smt_mobiletelephone,
 smt_linkman,
 smt_linkunit,
 smt_linkmanaddress,
 smt_linkmanphone,
 smt_linkmanmobile,
 smt_levelcode,
 smt_countrycode,
 smt_idcardno,
 smt_visadate,
 smt_validitydate,
 smt_visaorgan,
 smt_specialtycode,
 smt_knowledgecode,
 smt_incompanydate,
 smt_salarycode,
 smt_specialtypostcode,
 smt_degreecode   
 from _personnel s where s.smt_personnelid=d.smt_personnelid)
  where exists
  (select 1 from _personnel t  where t.smt_personnelid=d.smt_personnelid and not (
   t.smt_name = d.smt_name and
   t.smt_namespell = d.smt_namespell and
   t.smt_sex =d.smt_sex and
   t.smt_birthday =d.smt_birthday and
   t.smt_salaryno =d.smt_salaryno and
   t.smt_idcode =d.smt_idcode and
   t.smt_idno =d.smt_idno and
   t.smt_datasource =d.smt_datasource and
   t.smt_indatetime =d.smt_indatetime and
   t.smt_email =d.smt_email and
   t.smt_deptcode =d.smt_deptcode and
   t.smt_postcode =d.smt_postcode and
   t.smt_statuscode =d.smt_statuscode and
   t.smt_dossiercode =d.smt_dossiercode and
   t.smt_native =d.smt_native and
   t.smt_people =d.smt_people and
   t.smt_married =d.smt_married and
   t.smt_dwelling =d.smt_dwelling and
   t.smt_polity =d.smt_polity and
   t.smt_phoneno =d.smt_phoneno and
   t.smt_dailycode =d.smt_dailycode and
   t.smt_dailyno =d.smt_dailyno and
   t.smt_postalcode =d.smt_postalcode and
   t.smt_linkaddress =d.smt_linkaddress and
   t.smt_mobiletelephone =d.smt_mobiletelephone and
   t.smt_linkman =d.smt_linkman and
   t.smt_linkunit =d.smt_linkunit and
   t.smt_linkmanaddress =d.smt_linkmanaddress and
   t.smt_linkmanphone =d.smt_linkmanphone and
   t.smt_linkmanmobile =d.smt_linkmanmobile and
   t.smt_levelcode =d.smt_levelcode and
   t.smt_countrycode =d.smt_countrycode and
   t.smt_idcardno =d.smt_idcardno and
   t.smt_visadate =d.smt_visadate and
   t.smt_validitydate =d.smt_validitydate and
   t.smt_visaorgan =d.smt_visaorgan and
   t.smt_specialtycode =d.smt_specialtycode and
   t.smt_knowledgecode =d.smt_knowledgecode and
   t.smt_incompanydate =d.smt_incompanydate and
   t.smt_salarycode =d.smt_salarycode and
   t.smt_specialtypostcode =d.smt_specialtypostcode and
   t.smt_degreecode =d.smt_degreecode   
        )
  );
--删除操作
delete from _personnel@door d  where not exists(
       select 1 from _personnel s where d.smt_personnelid=s.smt_personnelid
);
commit;
-- *********************卡信息同步 ******************* ----
insert into _card@door
select * from _card s where not exists(
       select 1 from _card@door d where d.smt_cardid=s.smt_cardid
);
--更新改变的数据
update _card@door d set (
 smt_cardid,
 smt_personnelid,
 smt_cardno,
 smt_showcardno,
 smt_cardcode,
 smt_iccardcode,
 smt_bankcardcode,
  smt_cardserial,
  smt_accounts,
  smt_bankbakno,
  smt_carddate,
  smt_startcode,
  smt_startdate,
  smt_validitydate,
  smt_endcode,
  smt_enddate,
  smt_changedate,
  smt_password,
  smt_org_id,
  smt_operatorid,
  smt_dailycode,
  smt_dailyno,
  smt_deposit,
  smt_returndeposit,
  smt_depreciation,
  smt_blacklistinvoiceno,
  smt_oldcardid )=
 (select
  smt_cardid,
  smt_personnelid,
  smt_cardno,
  smt_showcardno,
  smt_cardcode,
  smt_iccardcode,
  smt_bankcardcode,
  smt_cardserial,
  smt_accounts,
  smt_bankbakno,
  smt_carddate,
  smt_startcode,
  smt_startdate,
  smt_validitydate,
  smt_endcode,
  smt_enddate,
  smt_changedate,
  smt_password,
  smt_org_id,
  smt_operatorid,
  smt_dailycode,
  smt_dailyno,
  smt_deposit,
  smt_returndeposit,
  smt_depreciation,
  smt_blacklistinvoiceno,
  smt_oldcardid 
  from _card where smt_cardid=d.smt_cardid)
  where exists(
       select 1 from _card s where s.smt_cardid=d.smt_cardid and
       not (
      s.smt_personnelid = d.smt_personnelid and
       s.smt_cardno = d.smt_cardno and
       s.smt_showcardno  = d.smt_showcardno and
       s.smt_cardcode = d.smt_cardcode and
       s.smt_iccardcode = d.smt_iccardcode and
       s.smt_bankcardcode = d.smt_bankcardcode and
       s.smt_cardserial = d.smt_cardserial and
       s.smt_accounts = d.smt_accounts and
       s.smt_bankbakno  = d.smt_bankbakno and
       s.smt_carddate = d.smt_carddate and
       s.smt_startcode = d.smt_startcode and
       s.smt_startdate = d.smt_startdate and
       s.smt_validitydate = d.smt_validitydate and
       s.smt_endcode = d.smt_endcode and
       s.smt_enddate = d.smt_enddate and
       s.smt_changedate = d.smt_changedate and
       s.smt_password = d.smt_password and
       s.smt_org_id = d.smt_org_id and
       s.smt_operatorid = d.smt_operatorid and
       s.smt_dailycode = d.smt_dailycode and
       s.smt_dailyno = d.smt_dailyno and
       s.smt_deposit = d.smt_deposit and
       s.smt_returndeposit = d.smt_returndeposit and
       s.smt_depreciation = d.smt_depreciation and
       s.smt_blacklistinvoiceno  = d.smt_blacklistinvoiceno and
       s.smt_oldcardid = d.smt_oldcardid  
       )
);
delete from _card@door d where not exists(
       select 1 from _card s where s.smt_cardid=d.smt_cardid
);
commit;
-- *********************用户卡片类型同步 ******************* ----
--插入操作（没有的数据）


insert into _iccardsort@door 
(
smt_iccardcode,
smt_iccardname,
smt_iccardexplain,
smt_deposit,
smt_backdeposit,
smt_addfarerate,
smt_backaddfare,
smt_losspoundage,
smt_damagepoundage,
smt_istemporary,
smt_dailycode,
smt_dailyno,
smt_regbylist,
smt_regbybank,
smt_regbyfinger,
smt_validdate,
smt_onemonthinmoneytimes,
smt_onemonthmaxmoney,
smt_onemonthoutmoneytimes,
smt_onemonthoutmaxmoney,
smt_function,
smt_addfare4,
smt_deposit4,
smt_addfarerate4,
smt_backfarerate5,
smt_addfare,
smt_regbybanktrans,
smt_regbylibrarycard,
--smt_defaultpassword,
smt_regbycodes,
smt_addfarerate1,
smt_addfare1,
smt_addfarerate2,
smt_addfare2,
smt_backfarerate3,
smt_addfarerate3,
smt_addfare3,
smt_depreciation,
smt_lagdate,
--addsmt_addfarerate4,
--addsmt_addfare4,
smt_validday
) 
select 
smt_iccardcode,
smt_iccardname,
smt_iccardexplain,
smt_deposit,
smt_backdeposit,
smt_addfarerate,
smt_backaddfare,
smt_losspoundage,
smt_damagepoundage,
smt_istemporary,
smt_dailycode,
smt_dailyno,
smt_regbylist,
smt_regbybank,
smt_regbyfinger,
smt_validdate,
smt_onemonthinmoneytimes,
smt_onemonthmaxmoney,
smt_onemonthoutmoneytimes,
smt_onemonthoutmaxmoney,
smt_function,
smt_addfare4,
smt_deposit4,
smt_addfarerate4,
smt_backfarerate5,
smt_addfare,
smt_regbybanktrans,
smt_regbylibrarycard,
--smt_defaultpassword,
smt_regbycodes,
smt_addfarerate1,
smt_addfare1,
smt_addfarerate2,
smt_addfare2,
smt_backfarerate3,
smt_addfarerate3,
smt_addfare3,
smt_depreciation,
smt_lagdate,
--addsmt_addfarerate4,
--addsmt_addfare4,
smt_validday
 from _iccardsort s where not exists(
       select 1 from _iccardsort@door d where d.smt_iccardcode=s.smt_iccardcode
);
--更新改变的数据
update _iccardsort@door d set (
 smt_iccardcode,
  smt_iccardname,
  smt_iccardexplain,
  smt_deposit,
  smt_backdeposit,
  smt_addfarerate,
  smt_backaddfare,
  smt_losspoundage,
  smt_damagepoundage,
  smt_istemporary,
  smt_dailycode,
  smt_dailyno,
  smt_regbylist,
  smt_regbybank,
  smt_regbyfinger,
  smt_validdate,
  smt_onemonthinmoneytimes,
  smt_onemonthmaxmoney,
  smt_onemonthoutmoneytimes,
  smt_onemonthoutmaxmoney,
  smt_function,
  smt_addfare4,
  smt_deposit4,
  smt_addfarerate4,
  smt_backfarerate5,
  smt_addfare,
  smt_regbybanktrans,
  smt_regbylibrarycard,
  --smt_defaultpassword,
  smt_regbycodes,
  smt_addfarerate1,
  smt_addfare1,
  smt_addfarerate2,
  smt_addfare2,
  smt_backfarerate3,
  smt_addfarerate3,
  smt_addfare3,
  smt_depreciation,
  smt_lagdate,
  --addsmt_addfarerate4,
  --addsmt_addfare4,
  smt_validday )=
 (select
 smt_iccardcode,
  smt_iccardname,
  smt_iccardexplain,
  smt_deposit,
  smt_backdeposit,
  smt_addfarerate,
  smt_backaddfare,
  smt_losspoundage,
  smt_damagepoundage,
  smt_istemporary,
  smt_dailycode,
  smt_dailyno,
  smt_regbylist,
  smt_regbybank,
  smt_regbyfinger,
  smt_validdate,
  smt_onemonthinmoneytimes,
  smt_onemonthmaxmoney,
  smt_onemonthoutmoneytimes,
  smt_onemonthoutmaxmoney,
  smt_function,
  smt_addfare4,
  smt_deposit4,
  smt_addfarerate4,
  smt_backfarerate5,
  smt_addfare,
  smt_regbybanktrans,
  smt_regbylibrarycard,
  --smt_defaultpassword,
  smt_regbycodes,
  smt_addfarerate1,
  smt_addfare1,
  smt_addfarerate2,
  smt_addfare2,
  smt_backfarerate3,
  smt_addfarerate3,
  smt_addfare3,
  smt_depreciation,
  smt_lagdate,
  --addsmt_addfarerate4,
  --addsmt_addfare4,
  smt_validday
  from _iccardsort where smt_iccardcode=d.smt_iccardcode)
  where exists(
       select 1 from _iccardsort s where s.smt_iccardcode=d.smt_iccardcode and
       not (   
        s.smt_iccardname = d.smt_iccardname and
        s.smt_iccardexplain = d.smt_iccardexplain and 
        s.smt_deposit = d.smt_deposit and 
        s.smt_backdeposit = d.smt_backdeposit and 
        s.smt_addfarerate= d.smt_addfarerate and 
        s.smt_backaddfare = d.smt_backaddfare and 
        s.smt_losspoundage = d.smt_losspoundage and 
        s.smt_damagepoundage = d.smt_damagepoundage and 
        s.smt_istemporary = d.smt_istemporary and 
        s.smt_dailycode= d.smt_dailycode and 
        s.smt_dailyno =d.smt_dailyno and 
        s.smt_regbylist =d.smt_regbylist and 
        s.smt_regbybank =d.smt_regbybank and 
        s.smt_regbyfinger= d.smt_regbyfinger and 
        s.smt_validdate = d.smt_validdate and 
        s.smt_onemonthinmoneytimes = d.smt_onemonthinmoneytimes and 
        s.smt_onemonthmaxmoney=d.smt_onemonthmaxmoney and 
        s.smt_onemonthoutmoneytimes =d.smt_onemonthoutmoneytimes and 
        s.smt_onemonthoutmaxmoney = d.smt_onemonthoutmaxmoney and 
        s.smt_function = d.smt_function and 
        s.smt_addfare4 = d.smt_addfare4 and 
        s.smt_deposit4 = d.smt_deposit4 and 
        s.smt_addfarerate4 = d.smt_addfarerate4 and 
        s.smt_backfarerate5 = d.smt_backfarerate5 and 
        s.smt_addfare = d.smt_addfare and 
        s.smt_regbybanktrans =d.smt_regbybanktrans and 
        s.smt_regbylibrarycard = d.smt_regbylibrarycard and 
        --smt_defaultpassword,
        s.smt_regbycodes = d.smt_regbycodes and 
        s.smt_addfarerate1 = d.smt_addfarerate1 and 
        s.smt_addfare1 =d.smt_addfare1 and 
        s.smt_addfarerate2 = d.smt_addfarerate2 and 
        s.smt_addfare2 = d.smt_addfare2 and 
        s.smt_backfarerate3 = d.smt_backfarerate3