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

DROP PROCEDURE IF EXISTS sp_data_migration_masid;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_masid(IN prevMasId BIGINT(20),
                                              IN masIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'mas_masid';

    DECLARE  f_ID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDTIME BIGINT(20) DEFAULT NULL;
    DECLARE  f_CREATEDUSER varchar(255) DEFAULT NULL;
    DECLARE  f_CREATEDUSERID int(11) DEFAULT NULL;
    DECLARE  f_CREATEDUSERNICKNAME varchar(255) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDTIME bigint DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSER varchar(255) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSERID int(11) DEFAULT NULL;
    DECLARE  f_srcObjType varchar(255) DEFAULT NULL;




    DECLARE cur CURSOR FOR
      SELECT
        ID,
        CREATEDTIME,
        CREATEDUSER,
        CREATEDUSERID,
        CREATEDUSERNICKNAME,
        LASTMODIFIEDTIME,
        LASTMODIFIEDUSER,
        LASTMODIFIEDUSERID,
        srcObjType
      FROM mas_masid
      ORDER BY ID asc;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;

    OPEN cur;

    FETCH cur INTO
      f_ID,
      f_CREATEDTIME,
      f_CREATEDUSER,
      f_CREATEDUSERID,
      f_CREATEDUSERNICKNAME,
      f_LASTMODIFIEDTIME,
      f_LASTMODIFIEDUSER,
      f_LASTMODIFIEDUSERID,
      f_srcObjType
      ;

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


      WHILE isover= 0 DO

        # 断点续传
        IF (f_ID > prevMasId)

          THEN

              SET @ID = f_ID;
              SET f_ID = f_ID + masIdOffset;

              insert into trs_mas.mas_masid
                (
                  ID,
                  CREATEDTIME,
                  CREATEDUSER,
                  CREATEDUSERID,
                  CREATEDUSERNICKNAME,
                  LASTMODIFIEDTIME,
                  LASTMODIFIEDUSER,
                  LASTMODIFIEDUSERID,
                  srcObjType
                )
                VALUES
                  (
                    f_ID,
                    f_CREATEDTIME,
                    f_CREATEDUSER,
                    f_CREATEDUSERID,
                    f_CREATEDUSERNICKNAME,
                    f_LASTMODIFIEDTIME,
                    f_LASTMODIFIEDUSER,
                    f_LASTMODIFIEDUSERID,
                    f_srcObjType
                  );

            SET @log_sql = build_migration_log_sql(migr_table_name, @ID, now());
            prepare stmt from @log_sql;
            EXECUTE stmt;
            deallocate prepare stmt;
            call setOffset(migr_table_name, masIdOffset, prevMasId, @ID);
      END IF ;

      FETCH cur INTO
        f_ID,
        f_CREATEDTIME,
        f_CREATEDUSER,
        f_CREATEDUSERID,
        f_CREATEDUSERNICKNAME,
        f_LASTMODIFIEDTIME,
        f_LASTMODIFIEDUSER,
        f_LASTMODIFIEDUSERID,
        f_srcObjType
        ;

    END WHILE;

    CLOSE cur;

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

	END $$
DELIMITER ;