最近比较流行，这种通过ETL同步数据到中间表，再到业务表的方式去开发。这种存储过程结构差不多，所以，直接贴出一个例子，就不多去解释了。需要用到的，直接复制过去，改一下表名，和更新的字段名，即可使用。
CREATE OR REPLACE PROCEDURE PROC_SYNC_ABNORMAL_SETTLEMENT AS
LAST_SYNC_TIME DATE; --业务表最新修改时间
BEGIN
--找出业务表中最新修改时间。由于一开始业务表是空的，所以，要使用到nvl()函数
SELECT nvl(MAX(modify_tm),
to_date('2000-01-1 01:00:00', 'yyyy-mm-dd hh24:mi:ss'))
INTO LAST_SYNC_TIME
FROM ET_ABNORMAL_SETTLEMENT;
--插入数据，首先，过滤出中间表中修改时间>业务表中最新更新时间的数据，再去判断业务表中是否存在这样的ID，如无，则进行插入操作。
INSERT INTO ET_ABNORMAL_SETTLEMENT EA
SELECT *
FROM MID_ABNORMAL_SETTLEMENT MA
WHERE MODIFY_TM > LAST_SYNC_TIME
AND NOT EXISTS
(SELECT ID FROM ET_ABNORMAL_SETTLEMENT WHERE ID = MA.ID);
-- 批量更新
--过滤出中间表中修改时间>业务表中最新更新时间的数据，再去判断业务表中是否存在这样的ID，如有，则进行更新操作。
FOR I IN (SELECT *
FROM MID_ABNORMAL_SETTLEMENT MA
WHERE MA.MODIFY_TM > LAST_SYNC_TIME
and EXISTS
(SELECT ID FROM ET_ABNORMAL_SETTLEMENT WHERE ID = MA.ID)) LOOP
BEGIN
UPDATE ET_ABNORMAL_SETTLEMENT
SET TRADE_CODE = I.TRADE_CODE,
SETTLEMENT_AREA = I.SETTLEMENT_AREA,
ACCOUNT_CODE = I.ACCOUNT_CODE,
ACCOUNT_NAME = I.ACCOUNT_NAME,
WAYBILL_MONTH = I.WAYBILL_MONTH,
HANDLE_STATE = I.HANDLE_STATE,
EXCEPTION_TYPE = I.EXCEPTION_TYPE,
TASK_ID = I.TASK_ID,
REPEAT_TASK_ID = I.REPEAT_TASK_ID,
TASK_STATE = I.TASK_STATE,
GPS_VALID = I.GPS_VALID,
VALID_SOURCE = I.VALID_SOURCE,
LINE_CODE = I.LINE_CODE,
CONTRACT_SERVICE_TYPE = I.CONTRACT_SERVICE_TYPE,
TRANSPORT_WAY = I.TRANSPORT_WAY,
RUN_MODE = I.RUN_MODE,
PRICING_MANNER = I.PRICING_MANNER,
PROOF = I.PROOF,
AUDIT_INSTRUCTIONS = I.AUDIT_INSTRUCTIONS,
REASON_CANCELLATION = I.REASON_CANCELLATION,
CANCEL_USER = I.CANCEL_USER,
CANCEL_TM = I.CANCEL_TM,
TASK_DEPT_CODE = I.TASK_DEPT_CODE,
PROOF_USER = I.PROOF_USER,
PROOF_TM = I.PROOF_TM,
PREFETCH_EXEC_STATUS = I.PREFETCH_EXEC_STATUS,
REQUIRE_PLAN_ID = I.REQUIRE_PLAN_ID,
REQUIRE_ID = I.REQUIRE_ID,
ACTUAL_WEIGHT = I.ACTUAL_WEIGHT,
MODIFY_EXPLAIN = I.MODIFY_EXPLAIN,
MODIFY_USER = I.MODIFY_USER,
MODIFY_TM = I.MODIFY_TM,
IS_DISPOSE = I.IS_DISPOSE
WHERE ID = I.ID;
END;
END LOOP;
COMMIT;--记得执行完，要进行提交
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
ROLLBACK;--有异常则回滚
END;
