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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmchannelchildindexquote;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmchannelchildindexquote(IN templateIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmchannelchildindexquote';
    DECLARE template_migr_table_name varchar(255) DEFAULT 'wcmtemplate';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE  f_TEMPLATEID int(11) DEFAULT '0';
    DECLARE  f_FOLDERTYPE int(11) DEFAULT '0';
    DECLARE  f_FOLDERID int(11) DEFAULT '0';
    DECLARE  f_QUOTECHANNELID int(11) DEFAULT '0';




    DECLARE cur CURSOR FOR
		SELECT
      TEMPLATEID,
      FOLDERTYPE,
      FOLDERID,
      QUOTECHANNELID
    FROM wcmchannelchildindexquote;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPLATEID,
    f_FOLDERTYPE,
    f_FOLDERID,
    f_QUOTECHANNELID
    ;


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



    WHILE isover= 0 DO

      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), templateIdOffset);
      SET f_TEMPLATEID = f_TEMPLATEID + @template_offset_num;

      SET @quote_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_QUOTECHANNELID and  end_id >=f_QUOTECHANNELID), channelIdOffset);
      SET f_QUOTECHANNELID = f_QUOTECHANNELID + @quote_offset_num;

      IF (f_FOLDERTYPE = 103)
        THEN
          SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_FOLDERID and  end_id >=f_FOLDERID), siteIdOffset);
          SET f_FOLDERID = f_FOLDERID + @site_offset_num;
      END IF ;

      IF (f_FOLDERTYPE = 101)
        THEN
          SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_FOLDERID and  end_id >=f_FOLDERID), channelIdOffset);
          SET f_FOLDERID = f_FOLDERID + @channel_offset_num;
      END IF ;



      insert into trs_hycloud_iip.wcmchannelchildindexquote
        (
          TEMPLATEID,
          FOLDERTYPE,
          FOLDERID,
          QUOTECHANNELID
        )
        VALUES
          (
            f_TEMPLATEID,
            f_FOLDERTYPE,
            f_FOLDERID,
            f_QUOTECHANNELID
          );


		FETCH cur INTO
      f_TEMPLATEID,
      f_FOLDERTYPE,
      f_FOLDERID,
      f_QUOTECHANNELID
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;