OR REPLACE PROCEDURE PROCEDURE1
AS
BEGIN
--单位PAK_GCCOMP
merge
into PAK_GCCOMP b using (
select * from pak_gccomp
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
pak_gccomp order by row_addtime
desc)
where rownum
=1))) c
on (b.PCOMP_ID
=c.PCOMP_ID)
when matched
then
update set b.COMP_ID
=c.COMP_ID, b.COMP_NAME
=c.COMP_NAME,b.COMP_ZZXL
=c.COMP_ZZXL,b.COMP_ZZXL_NAME
=c.COMP_ZZXL_NAME,b.COMP_LXR
=c.COMP_LXR,b.COMP_LXR_PHONE
=c.COMP_LXR_PHONE,b.COMP_FZR
=c.COMP_FZR,b.COMP_FZR_PHONE
=c.COMP_FZR_PHONE,b.COMP_LWFZR
=c.COMP_LWFZR,
b.COMP_LWFZR_PHONE=c.COMP_LWFZR_PHONE, b.GC_ID
=c.GC_ID,b.GC_CONTENT
=c.GC_CONTENT,b.ROW_ADDUSERID
=c.ROW_ADDUSERID,b.ROW_ADDUSER
=c.ROW_ADDUSER,b.ROW_ADDTIME
=c.ROW_ADDTIME,b.IS_USERD
=c.IS_USERD,b.BACC_ID
=c.BACC_ID,b.PCOMP_ID_B
=c.PCOMP_ID_B
when not matched
then
insert values (c.pcomp_id,c.COMP_ID, c.COMP_NAME,c.COMP_ZZXL,c.COMP_ZZXL_NAME,c.COMP_LXR,c.COMP_LXR_PHONE,c.COMP_FZR,c.COMP_FZR_PHONE,c.COMP_LWFZR,
c.COMP_LWFZR_PHONE, c.GC_ID,c.GC_CONTENT,c.ROW_ADDUSERID,c.ROW_ADDUSER,c.ROW_ADDTIME,c.IS_USERD,c.BACC_ID,c.PCOMP_ID_B);
commit;
--班组PAK_GROUP
merge
into PAK_GROUP b using (
select * from PAK_GROUP
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
PAK_GROUP order by row_addtime
desc)
where rownum
=1))) c
on (b.GROUP_ID
=c.GROUP_ID)
when matched
then
update set b.GROUP_NAME
=c.GROUP_NAME,b.ROW_ADDUSER
=c.ROW_ADDUSER, b.ROW_ADDUSERID
=c.ROW_ADDUSERID,b.ROW_ADDTIME
=c.ROW_ADDTIME,b.GC_ID
=c.GC_ID,b.PERSION_ID
=c.PERSION_ID, b.PCOMP_ID
=c.PCOMP_ID,b.PERSION_WORKTYPE
=c.PERSION_WORKTYPE
when not matched
then
insert values (c.GROUP_ID, c.GROUP_NAME,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.GC_ID,c.PERSION_ID, c.PCOMP_ID,c.PERSION_WORKTYPE);
commit;
--进出场PAK_LWGX
merge
into PAK_LWGX b using (
select * from PAK_LWGX
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
PAK_LWGX order by row_addtime
desc)
where rownum
=1))) c
on (b.LWGX_ID
=c.LWGX_ID)
when matched
then
update set b.LWGX_NO
=c.LWGX_NO,b.LWGX_STIME
=c.LWGX_STIME, b.LWGX_ETIME
=c.LWGX_ETIME,b.LWGX_PAYMETHOD
=c.LWGX_PAYMETHOD,b.ROW_ADDUSER
=c.ROW_ADDUSER,b.ROW_ADDUSERID
=c.ROW_ADDUSERID, b.ROW_ADDTIME
=c.ROW_ADDTIME,b.PERSION_ID
=c.PERSION_ID,b.COMP_ID
=c.COMP_ID,b.GROUP_ID
=c.GROUP_ID,b.GC_ID
=c.GC_ID
when not matched
then
insert values (c.LWGX_ID,c.LWGX_NO,c.LWGX_STIME,c.LWGX_ETIME,c.LWGX_PAYMETHOD,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.PERSION_ID,c.COMP_ID,c.GROUP_ID,c.GC_ID);
commit;
--人员PAK_PERSION
merge
into PAK_PERSION b using (
select * from PAK_PERSION
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
PAK_PERSION order by row_addtime
desc)
where rownum
=1))) c
on (b.PERSION_ID
=c.PERSION_ID)
when matched
then --b.PERSION_ID=c.PERSION_ID,
update set b.PERSION_NAME
=c.PERSION_NAME,b.PERSION_TEL
=c.PERSION_TEL,b.PERSION_CODE
=c.PERSION_CODE,b.PERSION_BIRTHDAY
=c.PERSION_BIRTHDAY,b.PERSION_SEX
=c.PERSION_SEX,b.PERSION_NATION
=c.PERSION_NATION, b.PERSION_ADDR
=c.PERSION_ADDR,b.ROW_ADDUSER
=c.ROW_ADDUSER,b.ROW_ADDUSERID
=c.ROW_ADDUSERID,b.ROW_ADDTIME
=c.ROW_ADDTIME,b.PERSION_IMGURL
=c.PERSION_IMGURL,b.PERSION_PHONE
=c.PERSION_PHONE
when not matched
then
insert values (c.PERSION_ID,c.PERSION_NAME,c.PERSION_TEL,c.PERSION_CODE,c.PERSION_BIRTHDAY,c.PERSION_SEX,c.PERSION_NATION,c.PERSION_ADDR,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.PERSION_IMGURL,c.PERSION_PHONE);
commit;
--考勤表PAK_ATTENDANCE
merge
into PAK_ATTENDANCE b using PAK_ATTENDANCE
@scjg c
on (b.id
=c.id)
when matched
then
update set b.idcardnum
=c.idcardnum,b.createtime
=c.createtime, b.name
=c.name,b.serialnumber
=c.serialnumber,b.importtype
=c.importtype,b.bacc_id
=c.bacc_id, b.workplace
=c.workplace
when not matched
then
insert values (c.id, c.idcardnum, c.createtime, c.name,c.serialnumber,c.importtype,c.bacc_id, c.workplace);
commit;
--企业端用户信息SF_AUTH_APPUSER
merge
into SF_AUTH_APPUSER
@scjg b using (
select * from SF_AUTH_APPUSER
where row_addtime
>(
select row_addtime
from(
select * from (
select * from
SF_AUTH_APPUSER@scjg order by row_addtime
desc)
where rownum
=1))) c
on (b.APPUSER_ID
=c.APPUSER_ID)
when matched
then
update set b.APPUSER_LOGID
=c.APPUSER_LOGID, b.APPUSER_NAME
=c.APPUSER_NAME,b.APPUSER_ALIASNAME
=c.APPUSER_ALIASNAME,b.APPUSER_DOMAIN
=c.APPUSER_DOMAIN,b.APPUSER_DDEPT
=c.APPUSER_DDEPT,b.APPUSER_MPHONE
=c.APPUSER_MPHONE,b.APPUSER_EMAIL
=c.APPUSER_EMAIL,b.APPUSER_LASTLOGIN
=c.APPUSER_LASTLOGIN,b.APPUSER_ROOT
=c.APPUSER_ROOT,b.APPUSER_PWD
=c.APPUSER_PWD,
b.APPUSER_CHANGEPWD=c.APPUSER_CHANGEPWD, b.APPUSER_CHANGEPWDDATE
=c.APPUSER_CHANGEPWDDATE,b.APPUSER_LOGERROR
=c.APPUSER_LOGERROR,b.APPUSER_TRYLOG
=c.APPUSER_TRYLOG,b.APPUSER_BEGINDATE
=c.APPUSER_BEGINDATE,b.APPUSER_OUTSERVICEDATE
=c.APPUSER_OUTSERVICEDATE,b.APPUSER_GBID
=c.APPUSER_GBID,b.APPUSER_DEFAULTPAGE
=c.APPUSER_DEFAULTPAGE,
b.APPUSER_INDEXPORTAL =c.APPUSER_INDEXPORTAL,b.APPUSER_RELATION
=c.APPUSER_RELATION, b.VALID_FLAG
=c.VALID_FLAG,b.ROW_ADDUSER
=c.ROW_ADDUSER,b.ROW_ADDTIME
=c.ROW_ADDTIME,b.ROW_ADDUSERID
=c.ROW_ADDUSERID,b.DDEPT_ORDER
=c.DDEPT_ORDER,b.ROW_EDITUSER
=c.ROW_EDITUSER,b.ROW_EDITTIME
=c.ROW_EDITTIME,b.ROW_EDITUSERID
=c.ROW_EDITUSERID,
b.ROW_STATUS=c.ROW_STATUS,b.APPUSER_ORDER
=c.APPUSER_ORDER,b.GCUSER_STATUS
=c.GCUSER_STATUS,b.IS_BJ
=c.IS_BJ
when not matched
then
insert values (c.APPUSER_ID,c.APPUSER_LOGID,c.APPUSER_NAME,c.APPUSER_ALIASNAME,c.APPUSER_DOMAIN,c.APPUSER_DDEPT,c.APPUSER_MPHONE,c.APPUSER_EMAIL,c.APPUSER_LASTLOGIN,c.APPUSER_ROOT,c.APPUSER_PWD,
c.APPUSER_CHANGEPWD,c.APPUSER_CHANGEPWDDATE,c.APPUSER_LOGERROR,c.APPUSER_TRYLOG,c.APPUSER_BEGINDATE,c.APPUSER_OUTSERVICEDATE,c.APPUSER_GBID,c.APPUSER_DEFAULTPAGE,
c.APPUSER_INDEXPORTAL,c.APPUSER_RELATION,c.VALID_FLAG,c.ROW_ADDUSER,c.ROW_ADDTIME,c.ROW_ADDUSERID,c.DDEPT_ORDER,c.ROW_EDITUSER,c.ROW_EDITTIME,c.ROW_EDITUSERID,
c.ROW_STATUS,c.APPUSER_ORDER,c.GCUSER_STATUS,c.IS_BJ);
commit;
--PAK_BANK_ACCOUNT
merge
into PAK_BANK_ACCOUNT
@scjg b using (
select * from PAK_BANK_ACCOUNT
where ADD_TIME
>(
select ADD_TIME
from(
select * from (
select * from
PAK_BANK_ACCOUNT@scjg order by ADD_TIME
desc)
where rownum
=1))) c
on (b.bacc_id
=c.bacc_id)
when matched
then
update set b.BANK_BRANCHNAME
=c.BANK_BRANCHNAME,b.BANK_ACCOUNT
=c.BANK_ACCOUNT, b.BANK_MONEY
=c.BANK_MONEY,b.BANK_ID
=c.BANK_ID,b.APPUSER_LOGID
=c.APPUSER_LOGID,b.IS_IMP
=c.IS_IMP, b.LZZGY1_PHONE
=c.LZZGY1_PHONE,b.LZZGY1_NAME
=c.LZZGY1_NAME,b.STAFF_COMPFZR
=c.STAFF_COMPFZR, b.STAFF_NAME
=c.STAFF_NAME,b.STAFF_PHONE
=c.STAFF_PHONE,b.YEZHU
=c.YEZHU,b.PHONE_YEZHU
=c.PHONE_YEZHU, b.STAFF_COMPFZRPHONE
=c.STAFF_COMPFZRPHONE,
b.JS_COMP_NAME=c.JS_COMP_NAME,b.SG_COMP_NAME
=c.SG_COMP_NAME,b.ADD_USER
=c.ADD_USER,b.ADD_TIME
=c.ADD_TIME,b.BACC_NAME
=c.BACC_NAME,b.IS_BSJ
=c.IS_BSJ,b.BANK_AREA
=c.BANK_AREA,b.PROJECT_ID
=c.PROJECT_ID,b.BANK_STATE
=c.BANK_STATE,b.QXFLAG
=c.QXFLAG
when not matched
then
insert values (c.bacc_id,c.BANK_BRANCHNAME,c.BANK_ACCOUNT,c.BANK_MONEY,c.BANK_ID,c.APPUSER_LOGID,c.IS_IMP,c.LZZGY1_PHONE,c.LZZGY1_NAME,c.STAFF_COMPFZR,c.STAFF_NAME,c.STAFF_PHONE,c.YEZHU,c.PHONE_YEZHU,c.STAFF_COMPFZRPHONE,c.JS_COMP_NAME,c.SG_COMP_NAME,c.ADD_USER,c.ADD_TIME,c.BACC_NAME,c.IS_BSJ,c.BANK_AREA,c.PROJECT_ID,c.BANK_STATE,c.QXFLAG);
commit;
--PAK_BANK_GC
merge
into PAK_BANK_GC
@scjg b using PAK_BANK_GC c
on (b.BGC_ID
=c.BGC_ID)
when matched
then
update set b.GC_ID
=c.GC_ID,b.BACC_ID
=c.BACC_ID
when not matched
then
insert values (c.BGC_ID,c.GC_ID, c.BACC_ID);
commit;
--PAK_ACCOUNT;
merge
into PAK_ACCOUNT b using (
select * from PAK_ACCOUNT
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
PAK_ACCOUNT order by row_addtime
desc)
where rownum
=1))) c
on (b.ACCOUNT_ID
=c.ACCOUNT_ID)
when matched
then
update set b.GROUP_ID
=c.GROUP_ID, b.PERSION_ID
=c.PERSION_ID,b.ACCOUNT_NO
=c.ACCOUNT_NO,b.ACCOUNT_MONTH
=c.ACCOUNT_MONTH,b.ACCOUNT_DAYS
=c.ACCOUNT_DAYS,b.SALARY_SUM
=c.SALARY_SUM,b.SALARY_NUM
=c.SALARY_NUM,b.ROW_ADDUSER
=c.ROW_ADDUSER,b.ROW_ADDUSERID
=c.ROW_ADDUSERID,
b.ROW_ADDTIME=c.ROW_ADDTIME, b.
FILE_ID=c.
FILE_ID,b.GC_ID
=c.GC_ID,b.TASK_CODE
=c.TASK_CODE
when not matched
then
insert values (c.ACCOUNT_ID,c.GROUP_ID, c.PERSION_ID,c.ACCOUNT_NO,c.ACCOUNT_MONTH,c.ACCOUNT_DAYS,c.SALARY_SUM,c.SALARY_NUM,c.ROW_ADDUSER,c.ROW_ADDUSERID,
c.ROW_ADDTIME, c.FILE_ID,c.GC_ID,c.TASK_CODE);
commit;
--PAK_PERSION_BANK
merge
into PAK_PERSION_BANK b using (
select * from PAK_PERSION_BANK
@scjg where row_addtime
>(
select row_addtime
from(
select * from (
select * from
PAK_PERSION_BANK order by row_addtime
desc)
where rownum
=1))) c
on (b.BANK_ID
=c.BANK_ID)
when matched
then
update set b.BANK_TYPE
=c.BANK_TYPE, b.BANK_CODE
=c.BANK_CODE,b.PERSION_ID
=c.PERSION_ID,b.GC_ID
=c.GC_ID,b.ROW_ADDTIME
=c.ROW_ADDTIME
when not matched
then
insert values (c.BANK_ID,c.BANK_TYPE, c.BANK_CODE,c.PERSION_ID,c.GC_ID,c.ROW_ADDTIME);
commit;
END PROCEDURE1;
ps。lz目前是一个刚入门小白，看见很多前辈大神有记录的好习惯，遂有意向见贤思齐，然后后期会坚持养成这个好习惯
2017.08.17
关于数据库数据同步的问题
标签：存储 添加 link add value 判断 入门 group ranch
