Oracle数据同步(存储过程)修改纪录记载

最近在做一个Oracle千万级会员积分数据同步的操作，一开始我是直接写了一个insert操作存储过程，异常时回滚，语句如下：

CREATE OR REPLACE PROCEDURE PROC_ZTWD_ADJINTEGRAL_SYNC  AS
 /******************************************************************************
     NAME:     PROC_ZTWD_DATA_SYNC
     PURPOSE:  积分明细表数据同步

     PARAMETERS:
     Name        Description
     ---------       ------------------------------------------------------------------

     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
     1.0        2016-10-18     jay     1. Created this procedure.

  ******************************************************************************/
  V_ERRMSG VARCHAR2(255);
BEGIN
  --ZTWD_ADJINTEGRAL
  INSERT INTO ZTWD_ADJINTEGRAL@DBLINKS_237_HTVIP
  (
         ADJUS_ID, GJAHR, ADJUS_SOURCE, ADJUS_DATE, ADJUS_TYPE, VIP_ID, LSD01,
         EXPENSE, POINT, STORE, STATUS, DELETED, DATBI, POINT_BALANCE, VALID,
         POINT_DAY, REMARK, ZCRE_DATE, ZCRE_TIME, ZCRE_USER,
         ZUP_DATE, ZUP_TIME, ZUP_USER
  )
  SELECT /*+CHOOSE+*/ ADJUS_ID || GJAHR, TO_NUMBER(GJAHR) GJAHR, ADJUS_SOURCE, TO_DATE(ADJUS_DATE, 'yyyy-mm-dd hh24:mi:ss') ADJUS_DATE,
         ADJUS_TYPE, TO_NUMBER(VIP_ID) VIP_ID, CASE WHEN LSD01 = ' ' THEN NULL ELSE TO_NUMBER(LSD01) END LSD01,
         EXPENSE, POINT, STORE, CASE WHEN STATUS = ' ' THEN '' ELSE STATUS END STATUS, 
				 CASE WHEN DELETED = ' ' THEN '' ELSE DELETED END DELETED, 
				 TO_DATE(DATBI, 'yyyy-mm-dd hh24:mi:ss') DATBI, POINT_BALANCE, CASE WHEN VALID = ' ' THEN '' ELSE VALID END VALID,
         TO_NUMBER(POINT_DAY) POINT_DAY, REMARK,
         ZCRE_DATE, CASE WHEN ZCRE_TIME = ' ' THEN NULL ELSE ZCRE_TIME END ZCRE_TIME, ZCRE_USER,
         CASE WHEN ZUP_DATE = ' ' THEN NULL ELSE ZUP_DATE END ZUP_DATE,
         CASE WHEN ZUP_TIME = ' ' THEN NULL ELSE ZUP_TIME END ZUP_TIME, ZUP_USER
  FROM ZTWD_ADJINTEGRAL;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    V_ERRMSG := TRIM(SQLERRM);
    ROLLBACK;
    RAISE;
END;

写完语句，我就直接调用，结果数据在跑了1个多小时的时候，抛出异常"ORA-01861:文字与格式字符串不匹配"。当时气得我够呛，跑了这么长时间居然出错，也不知道哪条记录数据异常。后来我想了一下，数据记录数为15597584条，如果直接一下子处理，中间难保不出异常，就加个时间段，总共6年的数据，我一个月一个月跑，这样出错，定位也相对容易一些。重新修改了一下存储过程，语句如下：

CREATE OR REPLACE PROCEDURE PROC_ZTWD_ADJINTEGRAL_SYNC(v_sdate VARCHAR2,v_edate VARCHAR2) AS
 /******************************************************************************
     NAME:     PROC_ZTWD_DATA_SYNC
     PURPOSE:  积分明细表数据同步

     PARAMETERS:
     Name        Description
     ---------       ------------------------------------------------------------------

     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
     1.0        2016-10-18     jay     1. Created this procedure.

  ******************************************************************************/
  V_ERRMSG VARCHAR2(255);
BEGIN
  --ZTWD_ADJINTEGRAL
  INSERT INTO ZTWD_ADJINTEGRAL@DBLINKS_237_HTVIP
  (
         ADJUS_ID, GJAHR, ADJUS_SOURCE, ADJUS_DATE, ADJUS_TYPE, VIP_ID, LSD01,
         EXPENSE, POINT, STORE, STATUS, DELETED, DATBI, POINT_BALANCE, VALID,
         POINT_DAY, REMARK, ZCRE_DATE, ZCRE_TIME, ZCRE_USER,
         ZUP_DATE, ZUP_TIME, ZUP_USER
  )
  SELECT /*+CHOOSE+*/ ADJUS_ID || GJAHR, TO_NUMBER(GJAHR) GJAHR, ADJUS_SOURCE, TO_DATE(ADJUS_DATE, 'yyyy-mm-dd hh24:mi:ss') ADJUS_DATE,
         ADJUS_TYPE, TO_NUMBER(VIP_ID) VIP_ID, CASE WHEN LSD01 = ' ' THEN NULL ELSE TO_NUMBER(LSD01) END LSD01,
         EXPENSE, POINT, STORE, CASE WHEN STATUS = ' ' THEN '' ELSE STATUS END STATUS, 
				 CASE WHEN DELETED = ' ' THEN '' ELSE DELETED END DELETED, 
				 TO_DATE(DATBI, 'yyyy-mm-dd hh24:mi:ss') DATBI, POINT_BALANCE, CASE WHEN VALID = ' ' THEN '' ELSE VALID END VALID,
         TO_NUMBER(POINT_DAY) POINT_DAY, REMARK,
         ZCRE_DATE, CASE WHEN ZCRE_TIME = ' ' THEN NULL ELSE ZCRE_TIME END ZCRE_TIME, ZCRE_USER,
         CASE WHEN ZUP_DATE = ' ' THEN NULL ELSE ZUP_DATE END ZUP_DATE,
         CASE WHEN ZUP_TIME = ' ' THEN NULL ELSE ZUP_TIME END ZUP_TIME, ZUP_USER
  FROM ZTWD_ADJINTEGRAL
	WHERE TO_DATE(ZCRE_DATE,'yyyy-mm-dd') BETWEEN
				TO_DATE(V_SDATE, 'yyyy-mm-dd') AND
				TO_DATE(V_EDATE, 'yyyy-mm-dd')
	;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    V_ERRMSG := TRIM(SQLERRM);
    ROLLBACK;
    RAISE;
END;

这样确实大大确保数据传输正常了，有异常的我先记录下来，最后再处理，下面是处理过程中我随便截取的操作记录：

CALL proc_ztwd_adjintegral_sync('2011-07-01', '2011-08-31');--记录数1101295||时间854s
CALL proc_ztwd_adjintegral_sync('2011-09-01', '2011-09-30');--记录数472344||时间350s
CALL proc_ztwd_adjintegral_sync('2011-10-01', '2011-10-31');--记录数616741||时间378s
CALL proc_ztwd_adjintegral_sync('2011-11-01', '2011-12-31');--记录数874970||操作异常
CALL proc_ztwd_adjintegral_sync('2012-01-01', '2012-02-29');--记录数866069||时间1756s

事情到这里，应该也结束了。

后来我将该方案与运营的伙伴们描述了一下，她们说在正式数据同步的时间，操作异常一个月数据回滚数据量很大，且不清楚数据传输了多少。我说可以用游标一条一条处理，但效率我不敢保证，后来我们领导说就采用游标方式。好吧，领导这么说就这么干吧，重新修改存储过程，语句如下：

CREATE OR REPLACE PROCEDURE PROC_ZTWD_ADJINTEGRAL_SYNC AS
 /******************************************************************************
     NAME:     PROC_ZTWD_DATA_SYNC
     PURPOSE:  积分明细表数据同步

     PARAMETERS:
     Name        Description
     ---------       ------------------------------------------------------------------

     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
     1.0        2016-10-18     jay     1. Created this procedure.

  ******************************************************************************/
  errorcode VARCHAR2(255);
	errmsg VARCHAR2(1024);
	
	CURSOR C_ZTWD_ADJINTEGRAL  IS 
	  SELECT  GJAHR, ADJUS_ID
		FROM ZTWD_ADJINTEGRAL 
    ORDER BY GJAHR, ADJUS_ID;

  C_ZTWD_ADJINTEGRAL_REC C_ZTWD_ADJINTEGRAL%ROWTYPE;
BEGIN
  FOR C_ZTWD_ADJINTEGRAL_REC IN C_ZTWD_ADJINTEGRAL LOOP	
		BEGIN
       INSERT INTO ZTWD_ADJINTEGRAL@DBLINKS_237_HTVIP
        (
               ADJUS_ID, GJAHR, ADJUS_SOURCE, ADJUS_DATE, ADJUS_TYPE, VIP_ID, LSD01,
               EXPENSE, POINT, STORE, STATUS, DELETED, DATBI, POINT_BALANCE, VALID,
               POINT_DAY, REMARK, ZCRE_DATE, ZCRE_TIME, ZCRE_USER,
               ZUP_DATE, ZUP_TIME, ZUP_USER
        )
        SELECT ADJUS_ID || GJAHR, TO_NUMBER(GJAHR) GJAHR, ADJUS_SOURCE, TO_DATE(ADJUS_DATE, 'yyyy-mm-dd hh24:mi:ss') ADJUS_DATE,
               ADJUS_TYPE, TO_NUMBER(VIP_ID) VIP_ID, CASE WHEN LSD01 = ' ' THEN NULL ELSE TO_NUMBER(LSD01) END LSD01,
               EXPENSE, POINT, STORE, CASE WHEN STATUS = ' ' THEN '' ELSE STATUS END STATUS, 
               CASE WHEN DELETED = ' ' THEN '' ELSE DELETED END DELETED, 
               TO_DATE(DATBI, 'yyyy-mm-dd hh24:mi:ss') DATBI, POINT_BALANCE, CASE WHEN VALID = ' ' THEN '' ELSE VALID END VALID,
               TO_NUMBER(POINT_DAY) POINT_DAY, REMARK,      
               TO_CHAR(TO_DATE(ZCRE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') ZCRE_DATE, 
               TO_CHAR(TO_DATE(ZCRE_TIME, 'hh24:mi:ss'),'hh24:mi:ss') ZCRE_TIME, 
						   CASE WHEN ZCRE_USER = ' ' THEN '' ELSE ZCRE_USER END ZCRE_USER,
						   CASE WHEN ZUP_DATE = ' ' THEN '' ELSE TO_CHAR(TO_DATE(ZUP_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') END ZUP_DATE, 
               TO_CHAR(TO_DATE(ZUP_TIME, 'hh24:mi:ss'),'hh24:mi:ss') ZUP_TIME, 
						   CASE WHEN ZUP_USER = ' ' THEN '' ELSE ZUP_USER END ZUP_USER
        FROM ZTWD_ADJINTEGRAL
        WHERE ADJUS_ID = C_ZTWD_ADJINTEGRAL_REC.ADJUS_ID AND GJAHR = C_ZTWD_ADJINTEGRAL_REC.GJAHR;
        COMMIT;
        
        EXCEPTION
                WHEN OTHERS THEN
                    ROLLBACK;
                    errorcode   := SQLCODE;
                    errmsg      := SQLERRM;
                    BEGIN
												UPDATE ZTWD_ADJINTEGRAL
												SET ISERROR = 1, ERROR_MSG = errorcode || errmsg
												WHERE ADJUS_ID = C_ZTWD_ADJINTEGRAL_REC.ADJUS_ID AND GJAHR = C_ZTWD_ADJINTEGRAL_REC.GJAHR;
                        COMMIT;
                    END;
      END;
    END LOOP;
END;

这样是可以确保数据一直在传输了，也可以确定哪条数据传输异常，回头再处理异常数据。但效率却不敢恭维，原来472344条记录只需要350s，而现在却需要2个小时。此处，我不表达任何想法!

事后，我想了一下，我们可以采用两种方式相结合的操作：

1、修改游标方式加上日期范围

2、我们先采用一个月一个月处理方式，如果当月操作异常了，再采用游标方式来处理当月记录

呵呵！呵呵！