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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmmetatable;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmmetatable(IN citySuffix VARCHAR(20),
                                              IN prevTableInfoId BIGINT(20),
                                              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 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

        SET @metaTableExists = (SELECT count(1) from information_schema.tables where table_schema = 'trs_hycloud_iip' and table_name = concat('wcmmetatable', f_TABLENAME, citySuffix) );

        select concat('开始更新 :', @metaTableExists, '====:','wcmmetatable', f_TABLENAME, citySuffix) info;
        IF (@metaTableExists > 0)

          THEN

            select concat('开始更新 ', 'wcmmetatable', f_TABLENAME, citySuffix, '.MetaDataId & ChannelId') info;

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

            select concat('更新元数据sql： ', @update_sql ) info;
            prepare stmt from @update_sql;
            EXECUTE stmt;
            deallocate prepare stmt;

            if (prevTableInfoId = 0)
              then
                #设置ID的最大值
                set @max_sql = concat('SELECT ifnull(max(wcmmetatable', f_TABLENAME, citySuffix, 'ID), 0)into @meta_data_id from trs_hycloud_iip.wcmmetatable', f_TABLENAME, citySuffix);
                prepare stmt from @max_sql;
                EXECUTE stmt;
                deallocate prepare stmt;

                set @id_sql = concat('INSERT INTO trs_hycloud_iip.wcmid VALUES (','\'','wcmmetatable',
                                 f_TABLENAME, citySuffix, '\'',', ', @meta_data_id + 1,', 1, ','\'','wcmmetatable',f_TABLENAME, citySuffix,'ID','\'',');');
                select concat('插入wcmid元数据sql： ', @id_sql ) info;
                prepare stmt from @id_sql;
                EXECUTE stmt;
                deallocate prepare stmt;
            else
              #设置ID的最大值
              set @max_sql = concat('SELECT max(wcmmetatable', f_TABLENAME, citySuffix, 'ID) into @meta_data_id from trs_hycloud_iip.wcmmetatable', f_TABLENAME, citySuffix);
              prepare stmt from @max_sql;
              EXECUTE stmt;
              deallocate prepare stmt;

              set @id_sql = concat('UPDATE trs_hycloud_iip.wcmid SET NEXTID = ', @meta_data_id + 1,' where TABLENAME = ','\'','wcmmetatable',
                                   f_TABLENAME, citySuffix, '\'');
              prepare stmt from @id_sql;
              EXECUTE stmt;
              deallocate prepare stmt;
            end if ;

            SET @log_sql = build_migration_log_sql(concat('wcmmetatable', f_TABLENAME, citySuffix), @meta_data_id, now());
            prepare stmt from @log_sql;
            EXECUTE stmt;
            deallocate prepare stmt;

            select concat('完成更新 ', 'wcmmetatable', f_TABLENAME, citySuffix, '.MetaDataId & ChannelId') info;
        END IF ;

      END IF ;

    FETCH cur INTO
      f_TABLEINFOID,
      f_TABLENAME
    ;

    END WHILE;

    CLOSE cur;
    select CONCAT('sp_data_migration_wcmmetatable 执行完成') info;

	END $$
DELIMITER ;