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

use trs_data_migration;

DROP PROCEDURE IF EXISTS sp_data_migration_wcmtemplatequote;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmtemplatequote(IN prevTemplateQuoteId BIGINT(20),
                                              IN templateQuoteIdOffset BIGINT(20),
                                              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 'wcmtemplatequote';

    DECLARE  f_TEMPLATEQUOTEID int(11) DEFAULT '0';
    DECLARE  f_TEMPLATEID int(11) DEFAULT '0';
    DECLARE  f_TEMPLATETYPE smallint(6) DEFAULT '0';
    DECLARE  f_QUOTEDFOLDERTYPE int(11) DEFAULT '0';
    DECLARE  f_QUOTEDFOLDERID int(11) DEFAULT '0';
    DECLARE  f_FAMILYINDEX smallint(6) DEFAULT '0';
    DECLARE  f_QUOTETYPE smallint(6) DEFAULT '0';


    DECLARE cur CURSOR FOR
		SELECT
      TEMPLATEQUOTEID,
      TEMPLATEID,
      TEMPLATETYPE,
      QUOTEDFOLDERTYPE,
      QUOTEDFOLDERID,
      FAMILYINDEX,
      QUOTETYPE
    FROM trs_data_migration.wcmtemplatequote
		ORDER BY TEMPLATEQUOTEID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPLATEQUOTEID,
    f_TEMPLATEID,
    f_TEMPLATETYPE,
    f_QUOTEDFOLDERTYPE,
    f_QUOTEDFOLDERID,
    f_FAMILYINDEX,
    f_QUOTETYPE
    ;



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




    WHILE isover= 0 DO

      # 断点续传
      IF (f_TEMPLATEQUOTEID > prevTemplateQuoteId)

        THEN

            SET @TEMPLATEQUOTEID = f_TEMPLATEQUOTEID;
            SET f_TEMPLATEQUOTEID = f_TEMPLATEQUOTEID + templateQuoteIdOffset;
            SET f_TEMPLATEID = f_TEMPLATEID + templateIdOffset;

            IF (f_QUOTEDFOLDERTYPE = 103)
              THEN SET f_QUOTEDFOLDERID = f_QUOTEDFOLDERID + siteIdOffset;
            END IF ;

            IF (f_QUOTEDFOLDERTYPE = 101)
              THEN SET f_QUOTEDFOLDERID = f_QUOTEDFOLDERID + channelIdOffset;
            END IF ;



            insert into trs_hycloud_iip.wcmtemplatequote
              (
                TEMPLATEQUOTEID,
                TEMPLATEID,
                TEMPLATETYPE,
                QUOTEDFOLDERTYPE,
                QUOTEDFOLDERID,
                FAMILYINDEX,
                QUOTETYPE
              )
              VALUES
                (
                  f_TEMPLATEQUOTEID,
                  f_TEMPLATEID,
                  f_TEMPLATETYPE,
                  f_QUOTEDFOLDERTYPE,
                  f_QUOTEDFOLDERID,
                  f_FAMILYINDEX,
                  f_QUOTETYPE
                );

          SET @log_sql = trs_data_migration.build_migration_log_sql(migr_table_name, @TEMPLATEQUOTEID);

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
    END IF ;

		FETCH cur INTO
      f_TEMPLATEQUOTEID,
      f_TEMPLATEID,
      f_TEMPLATETYPE,
      f_QUOTEDFOLDERTYPE,
      f_QUOTEDFOLDERID,
      f_FAMILYINDEX,
      f_QUOTETYPE
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;