TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/如增量迁移需运行SQL/3_sp_data_migration_wcmmetablexx.sql

DROP PROCEDURE IF EXISTS sp_data_migration_increment_wcmmetablexx;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_increment_wcmmetablexx(IN metableName VARCHAR(255),
                                              IN prevDocId BIGINT(20),
                                              IN docIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 

	  DECLARE isover INT DEFAULT 0;
	  DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE  f_CHNLID int(11)  DEFAULT '0';
    DECLARE  f_METADATAID int(11)  DEFAULT '0';

	  DECLARE cur CURSOR FOR
		SELECT
      channel_id,
      metadat_id
    FROM meta_data
		ORDER BY metadat_id asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CHNLID,
    f_METADATAID;


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

    WHILE isover= 0 DO

      # 支持断点续传
      IF (f_METADATAID > prevDocId)

        THEN

          SET @metadataId = f_METADATAID;
          SET f_METADATAID = f_METADATAID + docIdOffset;
          SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_CHNLID and  end_id >= f_CHNLID), channelIdOffset);
          SET f_CHNLID = f_CHNLID + @channel_offset_num;
          #sql 修改数据
          SET @up_sql = concat('UPDATE trs_hycloud_iip.', metableName,
                               ' SET MetaDataId = ', f_METADATAID,
                               ', ChannelId = ', f_CHNLID,
                               ' WHERE MetaDataId = ', @metadataId);
          prepare stmt from @up_sql;
          EXECUTE stmt;
          deallocate prepare stmt;

          SET @log_sql = build_migration_log_sql(metableName, @metadataId, now());
          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;

          call setOffset(metableName, docIdOffset, prevDocId, @metadataId);

    END IF ;

		FETCH cur INTO
    f_CHNLID,
    f_METADATAID;
		
	END WHILE;
	
	CLOSE cur;

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

  END $$
DELIMITER ;