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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmtemplateargument;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmtemplateargument(IN prevTemplateArgId BIGINT(20),
                                              IN templateArgIdOffset 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 'wcmtemplateargument';
    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_TEMPLATEARGUMENTID int(11) DEFAULT '0';
    DECLARE  f_EMPLOYERTYPE int(11) DEFAULT '0';
    DECLARE  f_EMPLOYERID int(11) DEFAULT '0';
    DECLARE  f_TEMPLATEID int(11) DEFAULT '0';
    DECLARE  f_ARGUMENTNAME varchar(256) DEFAULT '0';
    DECLARE  f_ARGUMENTVALUE varchar(500) DEFAULT NULL;
    DECLARE  f_PREFIXOFNAME varchar(256) DEFAULT NULL;




    DECLARE cur CURSOR FOR
		SELECT
      TEMPLATEARGUMENTID,
      EMPLOYERTYPE,
      EMPLOYERID,
      TEMPLATEID,
      ARGUMENTNAME,
      ARGUMENTVALUE,
      PREFIXOFNAME
    FROM wcmtemplateargument
		ORDER BY TEMPLATEARGUMENTID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPLATEARGUMENTID,
    f_EMPLOYERTYPE,
    f_EMPLOYERID,
    f_TEMPLATEID,
    f_ARGUMENTNAME,
    f_ARGUMENTVALUE,
    f_PREFIXOFNAME
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_TEMPLATEARGUMENTID > prevTemplateArgId)

        THEN

            SET @TEMPLATEARGUMENTID = f_TEMPLATEARGUMENTID;
            SET f_TEMPLATEARGUMENTID = f_TEMPLATEARGUMENTID + templateArgIdOffset;
            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;

            IF (f_EMPLOYERTYPE = 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_EMPLOYERID and  end_id >= f_EMPLOYERID), siteIdOffset);
                SET f_EMPLOYERID = f_EMPLOYERID + @site_offset_num;
            END IF ;

            IF (f_EMPLOYERTYPE = 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_EMPLOYERID and  end_id >= f_EMPLOYERID), channelIdOffset);
                SET f_EMPLOYERID = f_EMPLOYERID + @channel_offset_num;
            END IF ;



            insert into trs_hycloud_iip.wcmtemplateargument
              (
                TEMPLATEARGUMENTID,
                EMPLOYERTYPE,
                EMPLOYERID,
                TEMPLATEID,
                ARGUMENTNAME,
                ARGUMENTVALUE,
                PREFIXOFNAME
              )
              VALUES
                (
                  f_TEMPLATEARGUMENTID,
                  f_EMPLOYERTYPE,
                  f_EMPLOYERID,
                  f_TEMPLATEID,
                  f_ARGUMENTNAME,
                  f_ARGUMENTVALUE,
                  f_PREFIXOFNAME
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, templateArgIdOffset, prevTemplateArgId, @TEMPLATEARGUMENTID);
    END IF ;

		FETCH cur INTO
      f_TEMPLATEARGUMENTID,
      f_EMPLOYERTYPE,
      f_EMPLOYERID,
      f_TEMPLATEID,
      f_ARGUMENTNAME,
      f_ARGUMENTVALUE,
      f_PREFIXOFNAME
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;