最近在做一个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、我们先采用一个月一个月处理方式,如果当月操作异常了,再采用游标方式来处理当月记录
呵呵!呵呵!