TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/3_sp_data_migration_xwcmmetaviewemployer.sql

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmmetaviewemployer;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmmetaviewemployer(IN prevMetaViewEmployerId BIGINT(20),
                                              IN metaViewEmployerIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmmetaviewemployer';
    DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE  f_VIEWID int(11) DEFAULT '0';
    DECLARE  f_CHANNELID int(11) DEFAULT '0';
    DECLARE  f_METAVIEWEMPLOYERID int(11) DEFAULT '0';
    DECLARE  f_EmployerType int(11) DEFAULT '101';
    DECLARE  f_EmployerId int(11) DEFAULT '0';
    DECLARE  f_FOLDERTYPE int(11) DEFAULT '101';
    DECLARE  f_FOLDERID int(11) DEFAULT '0';




    DECLARE cur CURSOR FOR
		SELECT
      VIEWID,
      CHANNELID,
      METAVIEWEMPLOYERID,
      101,
      CHANNELID
    FROM xwcmmetaviewemployer
		ORDER BY METAVIEWEMPLOYERID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_VIEWID,
    f_CHANNELID,
    f_METAVIEWEMPLOYERID,
    f_EmployerType,
    f_EmployerId
    ;



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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_METAVIEWEMPLOYERID > prevMetaViewEmployerId)

        THEN

            SET @METAVIEWEMPLOYERID = f_METAVIEWEMPLOYERID;
            SET f_METAVIEWEMPLOYERID = f_METAVIEWEMPLOYERID + metaViewEmployerIdOffset;
            SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_CHANNELID and  end_id >= f_CHANNELID), 0);
            SET f_CHANNELID = f_CHANNELID + @channel_offset_num;
            SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_VIEWID and  end_id >= f_VIEWID), 0);
            SET f_VIEWID = f_VIEWID + @view_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), 0);
                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), 0);
                SET f_EmployerId = f_EmployerId + @channel_offset_num;
            END IF ;


            insert into trs_hycloud_iip.xwcmmetaviewemployer
              (
                VIEWID,
                CHANNELID,
                METAVIEWEMPLOYERID,
                EmployerType,
                EmployerId
              )
              VALUES
                (
                  f_VIEWID,
                  f_CHANNELID,
                  f_METAVIEWEMPLOYERID,
                  f_EmployerType,
                  f_EmployerId
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, metaViewEmployerIdOffset, prevMetaViewEmployerId, @METAVIEWEMPLOYERID);
    END IF ;

		FETCH cur INTO
      f_VIEWID,
      f_CHANNELID,
      f_METAVIEWEMPLOYERID,
      f_EmployerType,
      f_EmployerId
    ;
		
	END WHILE;
	
	CLOSE cur;

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



  END $$
DELIMITER ;