给力--oracle 数据库同步
http://blog.51cto.com/neweditor/editor/images/smiley/52.gif在数据库同步过程中常用的同步方法：http://blog.51cto.com/neweditor/editor/images/smiley/35.gif说明：
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 twhere 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 swhere 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 twhere 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 swhere 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 twhere 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 twhere 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 dwhere 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 and
s.smt_addfarerate3 =d.smt_addfarerate3 and
s.smt_addfare3 =d.smt_addfare3 and
s.smt_depreciation = d.smt_depreciation and
s.smt_lagdate = d.smt_lagdate and
--addsmt_addfarerate4,
--addsmt_addfare4,
s.smt_validday = d.smt_validday
)
);
--同步删除数据
delete from _iccardsort@door d where not exists(
select 1 from _iccardsort s where s.smt_iccardcode=d.smt_iccardcode
);
commit;
end;
页:
[1]