存储过程---查询其他数据库中的数据插入到本地数据库_mysql 查询其它库的数据并写入本地数据库-CSDN博客

本文链接：https://blog.csdn.net/tianya9006/article/details/49663591

create or replace package body PACK_QX_DLZHXX is

PROCEDURE P_SJCL_QX_DLZHXX(C_DAY VARCHAR2) IS
SQLERROINFO VARCHAR2(200);
SCRQ VARCHAR2(10);
BEGIN
INSERT INTO SJ_SJCL_RZ
(GCM, MS, BZ, CSSJ)
VALUES
('P_SJCL_QX_DLZHXX', '同步征管系统登陆账号信息开始', '0', SYSDATE);
COMMIT;
SELECT TO_CHAR(T.CQRQ, 'YYYYMMDD')
INTO SCRQ
FROM SJ_SJCQ_JL T
WHERE T.CQBZ = 'Y'
AND T.TABLE_NAME = 'QX_DLZHXX';

MERGE INTO QX_DLZHXX T
USING (SELECT DLZH_DM,
LRR_DM ,
LRRQ,
XGRQ ,
XGR_DM ,
KLYXQZ ,
KLYXQQ ,
DLZHKL ,
SWRY_DM,
YXBZ

FROM QX_DLZHXX@SJ_DB_3QZG
WHERE LRRQ >= TO_DATE(SCRQ, 'YYYYMMDD')
AND LRRQ < TO_DATE(C_DAY, 'YYYYMMDD')) T1
ON (T.DLZH_DM = T1.DLZH_DM)
WHEN MATCHED THEN
UPDATE
SET
T.LRR_DM =T1.LRR_DM ,
T.LRRQ =T1.LRRQ ,
T.XGRQ =T1.XGRQ ,
T.XGR_DM =T1.XGR_DM ,
T.KLYXQZ =T1.KLYXQZ ,
T.KLYXQQ =T1.KLYXQQ ,
T.DLZHKL =T1.DLZHKL ,
T.SWRY_DM=T1.SWRY_DM,
T.YXBZ =T1.YXBZ

WHEN NOT MATCHED THEN
INSERT
VALUES
(T1.DLZH_DM ,
T1.LRR_DM ,
T1.LRRQ ,
T1.XGRQ ,
T1.XGR_DM ,
T1.KLYXQZ ,
T1.KLYXQQ ,
T1.DLZHKL ,
T1.SWRY_DM ,
T1.YXBZ );
commit;

UPDATE SJ_SJCQ_JL T
SET T.CQRQ = TO_DATE(C_DAY, 'YYYYMMDD'), T.CZSJ = SYSDATE
WHERE T.CQBZ = 'Y'
AND T.TABLE_NAME = 'QX_DLZHXX';
COMMIT;
INSERT INTO SJ_SJCL_RZ
(GCM, MS, BZ, CSSJ)
VALUES
('P_SJCL_QX_DLZHXX', '同步征管系统登陆账号信息结束', '0', SYSDATE);
COMMIT;



EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
SQLERROINFO := SUBSTR(TO_CHAR(SQLERRM), 1, 200);
INSERT INTO SJ_SJCL_RZ
(GCM, MS, BZ, CSSJ)
VALUES
('P_SJCL_QX_DLZHXX', SQLERROINFO, '1', SYSDATE);
COMMIT;
END;

end PACK_QX_DLZHXX;
/