TRS.WCM.Developer/TRS.WCM.Developer/贵州项目/sp/p_data_migration_wcmtemplateemploy.sql

DROP PROCEDURE IF EXISTS p_data_migration_wcmtemplateemploy;
DELIMITER $$
CREATE PROCEDURE p_data_migration_wcmtemplateemploy(IN prevTemplateEmployId BIGINT(20),
                                              IN templateEmployIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE over INT DEFAULT 0;

    DECLARE  f_TEMPLATEEMPLOYID 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_TEMPLATETYPE smallint(6) DEFAULT '0';
    DECLARE  f_ISDEFAULT smallint(6) DEFAULT '0';


    DECLARE cur CURSOR FOR
		SELECT
      TEMPLATEEMPLOYID,
      EMPLOYERTYPE,
      EMPLOYERID,
      TEMPLATEID,
      TEMPLATETYPE,
      ISDEFAULT
    FROM v_wcm.wcmtemplateemploy
		ORDER BY TEMPLATEEMPLOYID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET over = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPLATEEMPLOYID,
    f_EMPLOYERTYPE,
    f_EMPLOYERID,
    f_TEMPLATEID,
    f_TEMPLATETYPE,
    f_ISDEFAULT
    ;



    WHILE over= 0 DO

      # 断点续传
      IF (f_TEMPLATEEMPLOYID > prevTemplateEmployId)

        THEN

            SET f_TEMPLATEEMPLOYID = f_TEMPLATEEMPLOYID + templateEmployIdOffset;
            SET f_TEMPLATEID = f_TEMPLATEID + baseTemplateId;

            IF (f_EMPLOYERTYPE = 103)
              THEN SET f_TEMPLATEID = f_TEMPLATEID + siteIdOffset;
            END IF ;

            IF (f_EMPLOYERTYPE = 101)
              THEN SET f_TEMPLATEID = f_TEMPLATEID + channelIdOffset;
            END IF ;



            insert into trs_hycloud_iip.wcmtemplateemploy
              (
                TEMPLATEEMPLOYID,
                EMPLOYERTYPE,
                EMPLOYERID,
                TEMPLATEID,
                TEMPLATETYPE,
                ISDEFAULT
              )
              VALUES
                (
                  f_TEMPLATEEMPLOYID,
                  f_EMPLOYERTYPE,
                  f_EMPLOYERID,
                  f_TEMPLATEID,
                  f_TEMPLATETYPE,
                  f_ISDEFAULT
                );

            SELECT CONCAT('成功迁移 wcmtemplateemploy[TEMPLATEEMPLOYID=', f_TEMPID, ']') ;
    END IF ;

		FETCH cur INTO
      f_TEMPLATEEMPLOYID,
      f_EMPLOYERTYPE,
      f_EMPLOYERID,
      f_TEMPLATEID,
      f_TEMPLATETYPE,
      f_ISDEFAULT
    ;
		
	END WHILE;
	
	CLOSE cur;

  SELECT '迁移 wcmtemplateemploy 完毕';
		
	END$$
DELIMITER ;