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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmtemplatenest;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmtemplatenest(IN prevTemplateNestId BIGINT(20),
                                              IN templateNestIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmtemplatenest';
    DECLARE template_migr_table_name varchar(255) DEFAULT 'wcmtemplate';

    DECLARE  f_TEMPLATEID int(11) DEFAULT 0;
    DECLARE  f_NESTEDTEMPLATEID int(11) DEFAULT 0;
    DECLARE  f_TEMPLATENESTID int(11) DEFAULT 0;



    

    DECLARE cur CURSOR FOR
		SELECT
      TEMPLATEID,
      NESTEDTEMPLATEID,
      TEMPLATENESTID
    FROM wcmtemplatenest
		ORDER BY TEMPLATENESTID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPLATEID,
    f_NESTEDTEMPLATEID,
    f_TEMPLATENESTID
    ;


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



    WHILE isover= 0 DO

      # 断点续传
      IF (f_TEMPLATENESTID > prevTemplateNestId)

        THEN

            SET @TEMPLATENESTID = f_TEMPLATENESTID;
            SET f_TEMPLATENESTID = f_TEMPLATENESTID + templateNestIdOffset;

            SET @template_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = template_migr_table_name AND start_id < f_TEMPLATEID and  end_id >= f_TEMPLATEID), 0);
            SET f_TEMPLATEID = f_TEMPLATEID + @template_offset_num;

            SET @nest_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = migr_table_name AND start_id < f_NESTEDTEMPLATEID and  end_id >= f_NESTEDTEMPLATEID), templateNestIdOffset);
            SET f_NESTEDTEMPLATEID = f_NESTEDTEMPLATEID + @nest_offset_num;

            insert into trs_hycloud_iip.wcmtemplatenest
              (
                TEMPLATEID,
                NESTEDTEMPLATEID,
                TEMPLATENESTID
              )
              VALUES
                (
                  f_TEMPLATEID,
                  f_NESTEDTEMPLATEID,
                  f_TEMPLATENESTID
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, templateNestIdOffset, prevTemplateNestId, @TEMPLATENESTID);
    END IF ;

		FETCH cur INTO
      f_TEMPLATEID,
      f_NESTEDTEMPLATEID,
      f_TEMPLATENESTID
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;