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

DROP PROCEDURE IF EXISTS p_data_migration_wcmmetatable;
DELIMITER $$
CREATE PROCEDURE p_data_migration_wcmmetatable(IN prevTableInfoId VARCHAR(100),
                                              IN prevDocId BIGINT(20),
                                              IN docIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))

	BEGIN

    DECLARE isover INT DEFAULT 0;
    DECLARE f_TABLEINFOID INT DEFAULT 0;
    DECLARE f_TABLENAME VARCHAR(200) DEFAULT NULL;

    DECLARE cur CURSOR FOR
      SELECT
        TABLEINFOID,
        TABLENAME
      FROM v_wcm.xwcmtableinfo
      ORDER BY TABLEINFOID asc;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;

    OPEN cur;

    FETCH cur INTO
      f_TABLEINFOID,
      f_TABLENAME
    ;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_TABLEINFOID > prevTableInfoId)

      THEN

        # 由于元数据表是动态生成的，执行存储过程前，需要提前将所有元数据表（结构+数据）直接复制至海云
        # 迁移后的元数据，其元数据ID不能和其他元数据表中的ID发生冲突，需要更改，因此有了以下SQL
        SET @sql = concat('UPDATE trs_hycloud_iip.', f_TABLENAME,
                          ' SET MetaDataId = MetaDataId + ', docIdOffset,
                          ', ChannelId = ChannelId + ', channelIdOffset,
                          ' WHERE MetaDataId > ', prevDocId);

        prepare stmt from @sql;
        EXECUTE stmt;
        deallocate prepare stmt;

        SELECT CONCAT('成功升级 wcmmetatable', f_TABLENAME, ' 的MetaDataId和ChannelId') ;

      END IF ;

    FETCH cur INTO
      f_TABLEINFOID,
      f_TABLENAME
    ;

    END WHILE;

    CLOSE cur;

    SELECT concat('升级 wcmmetatable', f_TABLENAME, ' 完毕');

	END $$
DELIMITER ;