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

use trs_data_migration;

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  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 trs_data_migration.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 f_TEMPLATEID = f_TEMPLATEID + templateIdOffset;
      SET f_QUOTECHANNELID = f_QUOTECHANNELID + templateIdOffset;

      IF (f_FOLDERTYPE = 103)
        THEN SET f_FOLDERID = f_FOLDERID + siteIdOffset;
      END IF ;

      IF (f_FOLDERTYPE = 101)
        THEN SET f_FOLDERID = f_FOLDERID + channelIdOffset;
      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 ;