TRS.WCM.Developer/贵州项目/4_sp_city_data.sql

DROP PROCEDURE IF EXISTS sp_city_data;
DELIMITER $$
CREATE PROCEDURE sp_city_data(IN n INT)

  BEGIN

    # 每迁移一个wcm节点，@n 必须 +1
    SET @n = n;

    # 每个wcm节点，元数据表需要加上后缀，以区分同名元数据表
    SET @citySuffix = concat('_city', @n);

    # 支持断点续传，上一次迁移的最后一个 xx ID
    SET @prevRecId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmchnldoc'), 0);
    SET @prevDocId = ifnull((SELECT prev_id from data_migration_log where table_name = 'wcmdocument'), 0);
    SET @prevClassInfoViewId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmclassinfoview'), 0);
    SET @prevTableInfoId = ifnull((SELECT prev_id from data_migration_log where table_name = 'xwcmtableinfo'), 0);

    # 支持数据增量 偏移量  修改
    # xx ID 偏移量,获取海云系统中的对应 xx maxID 错开一段距离,改为存入最大id
	  SET @maxDocId= IFNULL((SELECT MAX(DOCID) from trs_hycloud_iip.wcmdocument),0);
	  SET @docIdOffset = @maxDocId - @prevDocId;
    SET @maxRecId= IFNULL((SELECT MAX(recId) from trs_hycloud_iip.wcmchnldoc),0);
    SET @recIdOffset = @maxRecId - @prevRecId;
    SET @maxClassInfoViewId = IFNULL((SELECT MAX(CLASSINFOVIEWID) from trs_hycloud_iip.xwcmclassinfoview),0);
    SET @classInfoViewIdOffset = @maxClassInfoViewId - @prevClassInfoViewId;
    SET @view_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmviewinfo'), 0);
    SET @viewIdOffset = @view_offset_num;
    SET @site_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmwebsite'), 0);
    SET @siteIdOffset = @site_offset_num;
    SET @wcmchannel_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'wcmchannel'), 0);
    SET @channelIdOffset = @wcmchannel_offset_num;
    SET @classinfo_offset_num = ifnull((SELECT max(offset_num) FROM data_migration_offset WHERE table_name = 'xwcmclassinfo'), 0);
	  SET @classInfoIdOffset = @classinfo_offset_num;


    #数据迁移
    call sp_data_migration_wcmdocument(@prevDocId, @docIdOffset, @viewIdOffset, @siteIdOffset, @channelIdOffset);
    call sp_data_migration_wcmchnldoc(@prevRecId, @recIdOffset, @docIdOffset, @siteIdOffset, @channelIdOffset, @viewIdOffset);
    call sp_data_migration_xwcmclassinfoview(@prevClassInfoViewId, @classInfoViewIdOffset, @classInfoIdOffset, @docIdOffset, @viewIdOffset);
    call sp_data_migration_wcmmetatable(@citySuffix, @prevTableInfoId, @prevDocId, @docIdOffset, @channelIdOffset);

  END $$
DELIMITER ;