ORACLE 简单存储过程

实现每日同步一个表的昨日的数据至另一个表

用merge 实现

控制台打印输出     set serveroutput on 

CREATE OR REPLACE PROCEDURE ML_SYNC_LOG_JOB_FROM_SAP
AS
  /*****************************************************************************
  PROCEDURE NAME     : ML_SYNC_LOG_JOB_FROM_SAP
  DESCRIPTION        : This stored procedure sync log of SAP sync to-do time from SAP
  CREATE BY          : 
  LAST MODIFIED BY   : 
  *****************************************************************************/
BEGIN
  BEGIN
    MERGE INTO ml_sys_log_job_for_sap dst USING
    (SELECT FD_START_TIME,
      FD_END_TIME,
      FD_TASK_DURATION,
      FD_SUBJECT,
      FD_SUCCESS
    FROM sys_log_job
    WHERE FD_SUBJECT         = '同步SAP系统待办列表'
    AND TRUNC(FD_START_TIME) = TRUNC(SYSDATE) - 1
    ORDER BY FD_START_TIME
    ) src ON (dst.START_TIME = src.FD_START_TIME)
  WHEN MATCHED THEN
    UPDATE
    SET dst.END_TIME    = src.FD_END_TIME,
      dst.TASK_DURATION = src.FD_TASK_DURATION,
      dst.SUBJECT       = src.FD_SUBJECT,
      dst.SUCCESS_FLAG  = src.FD_SUCCESS 
  WHEN NOT MATCHED THEN
    INSERT
      (
        START_TIME,
        END_TIME,
        TASK_DURATION,
        SUBJECT,
        SUCCESS_FLAG
      )
      VALUES
      (
        src.FD_START_TIME,
        src.FD_END_TIME,
        src.FD_TASK_DURATION,
        src.FD_SUBJECT,
        src.FD_SUCCESS
      );
    COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Completed with error : '||sqlerrm);
  END ;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Finished!');
END ML_SYNC_LOG_JOB_FROM_SAP;