PROCEDURE MIGRATE_BATCH_LIVE_EVENT AS
CURSOR LIVE_EVENT_RECORD
IS SELECT/*+parallel(8)*/ E.*,A.SCHEDULE_ID FROM DYBF_RB_LIVE_EVENT E
LEFT JOIN (SELECT S.MATCH_ID,S.SCHEDULE_ID,M.THIRD_ID FROM DYBF_SCHEDULE S
LEFT JOIN THIRD_MATCH M ON S.MATCH_ID = M.ID
WHERE S.MATCH_ID IS NOT NULL) A ON A.THIRD_ID = E.GAME_ID
WHERE A.SCHEDULE_ID IS NOT NULL;
TYPE LIVE_RECORD IS RECORD
(
ID DYBF_RB_LIVE_EVENT.ID%TYPE,
GAME_ID DYBF_RB_LIVE_EVENT.GAME_ID%TYPE,
EVENT_NUM DYBF_RB_LIVE_EVENT.EVENT_NUM%TYPE,
RB_CODE_ID DYBF_RB_LIVE_EVENT.RB_CODE_ID%TYPE,
CUR_TIME DYBF_RB_LIVE_EVENT.CUR_TIME%TYPE,
RE_EVENT_NUM DYBF_RB_LIVE_EVENT.RE_EVENT_NUM%TYPE,
ADD_INFO DYBF_RB_LIVE_EVENT.ADD_INFO%TYPE,
STATE DYBF_RB_LIVE_EVENT.STATE%TYPE,
SCHEDULE_ID DYBF_SCHEDULE.SCHEDULE_ID%TYPE
);
TYPE LIVE_RECORD_COLLECTION IS TABLE OF LIVE_RECORD;
LIVE_RECORD_LIST LIVE_RECORD_COLLECTION;
NEW_LIVE_EVENT_ID BF_RB_LIVE_EVENT.ID%TYPE;
V_COUNTER PLS_INTEGER := 0;
V_CUR_ID NUMBER;
S_D TIMESTAMP;
E_D TIMESTAMP;
BEGIN
--DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP || ' 开始迁移DYBF_DETAIL_RESULT表中球探赛事结果数据...');
S_D := SYSTIMESTAMP;
OPEN LIVE_EVENT_RECORD;
LOOP
FETCH LIVE_EVENT_RECORD BULK COLLECT INTO LIVE_RECORD_LIST LIMIT FLAG;
EXIT WHEN LIVE_RECORD_LIST.COUNT = 0;
V_COUNTER := V_COUNTER + 1;
FOR I IN LIVE_RECORD_LIST.FIRST .. LIVE_RECORD_LIST.LAST LOOP
V_CUR_ID := LIVE_RECORD_LIST(I).ID;
SELECT SEQ_BF_RB_COMM_ID.NEXTVAL INTO NEW_LIVE_EVENT_ID FROM DUAL;
INSERT /*+APPEND*/ INTO BF_RB_LIVE_EVENT
(
ID,
GAME_ID,
EVENT_NUM,
RB_CODE_ID,
CUR_TIME,
RE_EVENT_NUM,
ADD_INFO,
STATE
)
VALUES
(
NEW_LIVE_EVENT_ID,
LIVE_RECORD_LIST(I).SCHEDULE_ID,
LIVE_RECORD_LIST(I).EVENT_NUM,
LIVE_RECORD_LIST(I).RB_CODE_ID,
LIVE_RECORD_LIST(I).CUR_TIME,
LIVE_RECORD_LIST(I).RE_EVENT_NUM,
LIVE_RECORD_LIST(I).ADD_INFO,
LIVE_RECORD_LIST(I).STATE
);
END LOOP;
COMMIT;
END LOOP;
--DBMS_OUTPUT.PUT_LINE (SYSTIMESTAMP || ' 共完成处理的数据量(DYBF_RB_LIVE_EVENT):' || LIVE_EVENT_RECORD%ROWCOUNT);
E_D := SYSTIMESTAMP;
MIGRATE_EXECUTE_TIME('MIGRATE_BATCH_LIVE_EVENT',S_D,E_D,LIVE_EVENT_RECORD%ROWCOUNT);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
INSERT_ERR_INFO(SQLERRM,'MIGRATE_QT_RESULT_DETAIL',V_CUR_ID);
DBMS_OUTPUT.PUT_LINE
(
'当前处理失败的数据:[赛事事件编号=' || V_CUR_ID || ' ]' || ' [异常信息:'
|| SQLERRM || ']'
);
CLOSE LIVE_EVENT_RECORD;
END MIGRATE_BATCH_LIVE_EVENT;
转载于:https://www.cnblogs.com/luoqiyi/p/6080908.html