TRS.WCM.Developer/贵州项目/增量迁移/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 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 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) );

        IF (@metaTableExists > 0)

          THEN

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

            set @del_sql = concat('delete from meta_data');
            prepare stmt from @del_sql;
            EXECUTE stmt;
            deallocate prepare stmt;

            SET @doc_sql = concat('INSERT INTO meta_data(metadat_id, channel_id) SELECT MetaDataId,ChannelId FROM trs_hycloud_iip.wcmmetatable',
             f_TABLENAME, citySuffix, ' WHERE MetaDataId >  ', prevDocId);
            prepare stmt from @doc_sql;
            EXECUTE stmt;
            deallocate prepare stmt;

            call sp_data_migration_wcmmetablexx(concat('wcmmetatable', f_TABLENAME, citySuffix), prevDocId, docIdOffset, channelIdOffset);

            if prevTableInfoId = 0
              then
                #更新当前表的ID字段名称
                set @id_field_sql = concat('ALTER TABLE trs_hycloud_iip.wcmmetatable', f_TABLENAME, citySuffix,
                ' CHANGE COLUMN `WCMMetaTable',f_TABLENAME,
                                 'ID` `wcmmetatable',f_TABLENAME, citySuffix,
                                 'ID`  int(11) NOT NULL;');
                select concat('更新ID字段元数据sql： ', @id_field_sql ) info;
                prepare stmt from @id_field_sql;
                EXECUTE stmt;
                deallocate prepare stmt;

                #设置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('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 ;

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

      END IF ;

    FETCH cur INTO
      f_TABLEINFOID,
      f_TABLENAME
    ;

    END WHILE;

    CLOSE cur;


	END $$
DELIMITER ;