千万级数据迁移版本2

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;