TRS.WCM.Developer/贵州项目/3_sp_data_migration_wcmchannelsyn.sql

DROP PROCEDURE IF EXISTS sp_data_migration_wcmchannelsyn;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmchannelsyn(IN prevChannelSynId BIGINT(20),
                                                  IN channelSynIdOffset BIGINT(20),
                                                  IN channelIdOffset BIGINT(20))
	BEGIN
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmchannelsyn';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE  f_CHANNELSYNID int(11) DEFAULT NULL;
    DECLARE  f_SRCCHANNEL int(11) DEFAULT NULL;
    DECLARE  f_TOCHANNEL int(11) DEFAULT NULL;
    DECLARE  f_WHERESQL varchar(500) DEFAULT NULL;
    DECLARE  f_SDATE datetime DEFAULT NULL;
    DECLARE  f_EDATE datetime DEFAULT NULL;
    DECLARE  f_DOCSDATE datetime DEFAULT NULL;
    DECLARE  f_DOCEDATE datetime DEFAULT NULL;
    DECLARE  f_STATUSES varchar(300) DEFAULT NULL;
    DECLARE  f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE  f_SYNTYPES varchar(50) DEFAULT 'NEW';
    DECLARE  f_OPERAFTER int(11) DEFAULT '0';
    DECLARE  f_CLASSINFOIDS varchar(100) DEFAULT NULL ;



    DECLARE cur CURSOR FOR
		SELECT
      CHANNELSYNID,
      SRCCHANNEL,
      TOCHANNEL,
      WHERESQL,
      SDATE,
      EDATE,
      DOCSDATE,
      DOCEDATE,
      STATUSES,
      ATTRIBUTE,
      SYNTYPES,
      OPERAFTER,
      null
    FROM wcmchannelsyn
    ORDER BY CHANNELSYNID ASC ;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CHANNELSYNID,
    f_SRCCHANNEL,
    f_TOCHANNEL,
    f_WHERESQL,
    f_SDATE,
    f_EDATE,
    f_DOCSDATE,
    f_DOCEDATE,
    f_STATUSES,
    f_ATTRIBUTE,
    f_SYNTYPES,
    f_OPERAFTER,
    f_CLASSINFOIDS
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_CHANNELSYNID > prevChannelSynId)

        THEN
          SET @CHANNELSYNID = f_CHANNELSYNID;
          SET f_CHANNELSYNID = f_CHANNELSYNID + channelSynIdOffset;
          SET @src_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_SRCCHANNEL and  end_id >= f_SRCCHANNEL), channelIdOffset);
          SET f_SRCCHANNEL = f_SRCCHANNEL + @src_offset_num;
          SET @to_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_TOCHANNEL and  end_id >= f_TOCHANNEL), channelIdOffset);
          SET f_TOCHANNEL = f_TOCHANNEL + @to_offset_num;


        insert into trs_hycloud_iip.wcmchannelsyn
          (
            CHANNELSYNID,
            SRCCHANNEL,
            TOCHANNEL,
            WHERESQL,
            SDATE,
            EDATE,
            DOCSDATE,
            DOCEDATE,
            STATUSES,
            ATTRIBUTE,
            SYNTYPES,
            OPERAFTER,
            CLASSINFOIDS
          )
          VALUES
            (
              f_CHANNELSYNID,
              f_SRCCHANNEL,
              f_TOCHANNEL,
              f_WHERESQL,
              f_SDATE,
              f_EDATE,
              f_DOCSDATE,
              f_DOCEDATE,
              f_STATUSES,
              f_ATTRIBUTE,
              f_SYNTYPES,
              f_OPERAFTER,
              f_CLASSINFOIDS
            );

          SET @log_sql = build_migration_log_sql(migr_table_name, @CHANNELSYNID, now());

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, channelSynIdOffset, prevChannelSynId, @CHANNELSYNID);
    END IF ;

		FETCH cur INTO
      f_CHANNELSYNID,
      f_SRCCHANNEL,
      f_TOCHANNEL,
      f_WHERESQL,
      f_SDATE,
      f_EDATE,
      f_DOCSDATE,
      f_DOCEDATE,
      f_STATUSES,
      f_ATTRIBUTE,
      f_SYNTYPES,
      f_OPERAFTER,
      f_CLASSINFOIDS
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;