TRS.WCM.Developer/贵州项目/3_sp_data_migration_xwcmclassinfo.sql

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmclassinfo;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmclassinfo(IN prevClassInfoId BIGINT(20),
                                              IN classInfoIdOffset BIGINT(20))
	BEGIN 
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmclassinfo';
    
    DECLARE  f_CLASSINFOID int(11) DEFAULT NULL;
    DECLARE  f_CLASSORDER int(11) DEFAULT NULL;
    DECLARE  f_CNAME varchar(50) DEFAULT '0';
    DECLARE  f_CDESC varchar(200) DEFAULT NULL;
    DECLARE  f_PARENTID int(11) DEFAULT '0';
    DECLARE  f_ROOTID int(11) DEFAULT '0';
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_CCODE varchar(50) DEFAULT NULL;





    DECLARE cur CURSOR FOR
		SELECT
      CLASSINFOID,
      CLASSORDER,
      CNAME,
      CDESC,
      PARENTID,
      ROOTID,
      CRUSER,
      CRTIME,
      CCODE
    FROM xwcmclassinfo
		ORDER BY CLASSINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CLASSINFOID,
    f_CLASSORDER,
    f_CNAME,
    f_CDESC,
    f_PARENTID,
    f_ROOTID,
    f_CRUSER,
    f_CRTIME,
    f_CCODE
    ;


    select concat('开始迁移 ', migr_table_name) info;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_CLASSINFOID > prevClassInfoId)

        THEN

          SET @CLASSINFOID = f_CLASSINFOID;
          SET f_CLASSINFOID = f_CLASSINFOID + classInfoIdOffset;
          SET @offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = migr_table_name AND start_id < f_PARENTID and  end_id >= f_PARENTID), 0);
          SET f_PARENTID = f_PARENTID + @offset_num;

          IF (f_ROOTID > 0)
             THEN
                SET f_ROOTID = f_ROOTID + classInfoIdOffset;
                SET f_CLASSORDER = f_CLASSORDER + classInfoIdOffset;
          END IF ;

            insert into trs_hycloud_iip.xwcmclassinfo
              (
                CLASSINFOID,
                CLASSORDER,
                CNAME,
                CDESC,
                PARENTID,
                ROOTID,
                CRUSER,
                CRTIME,
                CCODE
              )
              VALUES
                (
                  f_CLASSINFOID,
                  f_CLASSORDER,
                  f_CNAME,
                  f_CDESC,
                  f_PARENTID,
                  f_ROOTID,
                  f_CRUSER,
                  f_CRTIME,
                  f_CCODE
                );

          SET @log_sql = build_migration_log_sql(migr_table_name, @CLASSINFOID, now());

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, classInfoIdOffset, prevClassInfoId, @CLASSINFOID);
    END IF ;

		FETCH cur INTO
      f_CLASSINFOID,
      f_CLASSORDER,
      f_CNAME,
      f_CDESC,
      f_PARENTID,
      f_ROOTID,
      f_CRUSER,
      f_CRTIME,
      f_CCODE
      ;
		
	END WHILE;
	
	CLOSE cur;

    select concat('完成迁移 ', migr_table_name) info;


  END $$
DELIMITER ;