数据库同步(一)

来源：互联网发布：天龙八部附体加成数据编辑：程序博客网时间：2024/04/28 05:45

方法一:

PROCEDURE Update_FIM_CDM_CONTRACTS IS V_SYNCH_TYPE VARCHAR2(30); -- 同步类型 v_old_contract_header_id NUMBER; --合同号ID v_last_update_date DATE; -- 最近更新时间 BEGIN REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ']开始同步CDM合同号信息...'); --设置同步类型 V_SYNCH_TYPE := 'CDM_CONTRACT'; --遍历所有合同号 FOR REC IN (SELECT * FROM apps.cdm_contract_headers_v_to_fol@to_cms.world /*WHERE rownum = 1*/ ) LOOP BEGIN SELECT contract_header_id, LAST_UPDATE_DATE INTO v_old_contract_header_id, v_last_update_date FROM ztefim.zte_fim_cdm_contracts WHERE contract_header_id = rec.contract_header_id; EXCEPTION WHEN OTHERS THEN v_old_contract_header_id := 0; END; IF v_old_contract_header_id = 0 THEN --插入合同号 INSERT INTO ztefim.zte_fim_cdm_contracts (last_update_date, last_updated_by, creation_date, created_by, last_update_login, ENABLED_FLAG, contract_header_id, contract_number, hc_contract_id, hc_contract_number, org_id , org_name, customer_id, customer_number, customer_name, sale_org_id , sale_org_name, sale_region_id, sale_region, salesrep_id, salesrep_name , currency_code, contract_amount, conclude_amount) VALUES (SYSDATE, 0, SYSDATE, 0, 0, rec.enabled_flag, rec.contract_header_id, rec.contract_number, rec.hc_contract_id, rec.hc_contract_number, rec.org_id , rec.NAME, rec.customer_id, rec.customer_number, rec.customer_name, rec.sale_org_id , rec.sale_org_name, rec.sale_region_id, rec.sale_region_name, rec.salesrep_id, rec.salesrep_name , rec.currency_code, rec.contract_amount, rec.conclude_amount); ELSE IF v_last_update_date != rec.LAST_UPDATE_DATE THEN --更新数据 UPDATE ztefim.zte_fim_cdm_contracts SET last_update_date = rec.last_update_date, contract_number = rec.contract_number, hc_contract_id = rec.hc_contract_id, hc_contract_number = rec.hc_contract_number, org_id = rec.org_id, ENABLED_FLAG = rec.ENABLED_FLAG, org_name = rec.NAME, customer_id = rec.customer_id, customer_number = rec.customer_number, customer_name = rec.customer_name, sale_org_id = rec.sale_org_id , sale_org_name = rec.sale_org_name, sale_region_id = rec.sale_region_id, sale_region = rec.sale_region_name, salesrep_id = rec.salesrep_id, salesrep_name = rec.salesrep_name , currency_code = rec.currency_code, contract_amount = rec.contract_amount, conclude_amount = rec.conclude_amount WHERE contract_header_id = rec.contract_header_id; END IF; END IF; END LOOP; --报告成功同步结果 REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ']同步CDM合同号信息完毕!'); COMMIT; -- 记录同步历史 SET_SYNCH_HISTORY(SYSDATE, V_SYNCH_TYPE, 'Y', NULL); EXCEPTION WHEN OTHERS THEN --报告失败同步结果 REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ']CDM合同号信息同步异常:' || SQLERRM); ROLLBACK; -- 记录同步历史 SET_SYNCH_HISTORY(SYSDATE, V_SYNCH_TYPE, 'N', SQLERRM); END;

--输出信息

PROCEDURE REPORT_LINE(P_TEXT VARCHAR2) IS BEGIN IF V_REPORT_FLAG = 'Y' THEN APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT, P_TEXT); ELSE DBMS_OUTPUT.ENABLE(4000000); DBMS_OUTPUT.PUT_LINE(P_TEXT); END IF; EXCEPTION WHEN OTHERS THEN V_REPORT_FLAG := 'N'; DBMS_OUTPUT.ENABLE(4000000); DBMS_OUTPUT.PUT_LINE(P_TEXT); END;

-- -- 将同步信息插入同步历史表中 -- INSERT INTO ZTE_FBP_SYNCH_HISTORIES ( SYNCH_DATE, SYNCH_TYPE, SUCCEED_FLAG, MEMO) VALUES (P_SYNCH_DATE, P_SYNCH_TYPE, P_SUCCEED_FLAG, P_MEMO); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END SET_SYNCH_HISTORY;