oracle分中心存储过程

CREATE OR REPLACE PROCEDURE PROC_TT_DTX5_UPDATE_TASK  AS
-- DTX5强制升级：生成每个中转场对应一条数据
    NUMMID          NUMBER(20);
    LEVELNUM        NUMBER(20);
   TRANSITCODE      VARCHAR2(100);
   TRANSITCODEMID   VARCHAR2(10);
    --TRANSITCODE     VARCHAR2(1000); 
      
BEGIN
   -- 批量提取
FOR RT IN(SELECT * FROM TM_DTX5_CONFIG T WHERE  DEAL_FLG=0 OR DEAL_FLG IS NULL ) LOOP --AND DEAL_FLG='0'
       IF LTRIM(RTRIM(RT.TRANSIT_CODE)) IS NULL  
         THEN 
       FOR  RM IN (SELECT TRANSIT_CODE  FROM TM_ZNO_TRANSIT )  LOOP
     INSERT INTO TM_DTX5_CONFIG_MID
   (ID, DELIVE_CODE, TRANSIT_CODE, TYPE, VERSION_NUMBER, DEADLINE, PARAM_1, PARAM_2, PARAM_3, PARAM_4, PARAM_5, PARAM_6, PARAM_7, PARAM_8, PARAM_9, PARAM_10, DATA_COMMENT, STATUS, CREATE_TM,DEAL_FLG)
 VALUES
   (SEQ_TM_DTX5_CONFIG.NEXTVAL,  RT.DELIVE_CODE, RM.TRANSIT_CODE, RT.TYPE, RT.VERSION_NUMBER, RT.DEADLINE, RT.PARAM_1, RT.PARAM_2, RT.PARAM_3, RT.PARAM_4, RT.PARAM_5, RT.PARAM_6, RT.PARAM_7, RT.PARAM_8, RT.PARAM_9, RT.PARAM_10, RT.DATA_COMMENT, RT.STATUS, SYSDATE,0);
   END LOOP;   
        UPDATE    TM_DTX5_CONFIG SET DEAL_FLG=2 WHERE ID=RT.ID ;  --AND DEAL_FLG='2'                                                     
     ELSE  
    TRANSITCODE :=LTRIM(RTRIM(RT.TRANSIT_CODE));   
    SELECT COUNT(1) INTO NUMMID FROM TM_ZNO_TRANSIT;
       FOR  LEVELNUM IN 1 .. NUMMID  LOOP
      SELECT NVL(REGEXP_SUBSTR(TRANSITCODE, '[^,]+', 1, LEVELNUM, 'i'), 'NULLL')AS STR INTO TRANSITCODEMID  FROM DUAL;    
        IF TRANSITCODEMID <> 'NULLL' 
          THEN
     INSERT INTO TM_DTX5_CONFIG_MID
   (ID, DELIVE_CODE, TRANSIT_CODE, TYPE, VERSION_NUMBER, DEADLINE, PARAM_1, PARAM_2, PARAM_3, PARAM_4, PARAM_5, PARAM_6, PARAM_7, PARAM_8, PARAM_9, PARAM_10, DATA_COMMENT, STATUS, CREATE_TM,DEAL_FLG)
 VALUES
   (SEQ_TM_DTX5_CONFIG.NEXTVAL,  RT.DELIVE_CODE, TRANSITCODEMID, RT.TYPE, RT.VERSION_NUMBER, RT.DEADLINE, RT.PARAM_1, RT.PARAM_2, RT.PARAM_3, RT.PARAM_4, RT.PARAM_5, RT.PARAM_6, RT.PARAM_7, RT.PARAM_8, RT.PARAM_9, RT.PARAM_10, RT.DATA_COMMENT, RT.STATUS, SYSDATE,0);
      END IF;
      END LOOP; 
      UPDATE   TM_DTX5_CONFIG SET DEAL_FLG=2 WHERE ID=RT.ID ;  --AND DEAL_FLG='2'
      END IF;
      END LOOP;
      COMMIT;  
	
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        PKG_SYS_LOG.ERROR_LOG(NULL,
            'PROC_TT_DTX5_UPDATE_TASK',
            SYSDATE,
            SQLCODE,
            SQLERRM,
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,NULL
        );
END PROC_TT_DTX5_UPDATE_TASK;
/