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

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmmaterial;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmmaterial(IN prevAppendixId BIGINT(20),
                                              IN appendixIdOffset BIGINT(20),
                                              IN docIdOffset BIGINT(20),
                                              IN masIdOffset BIGINT(20))
BEGIN

	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmmaterial';

    DECLARE f_APPENDIXID int(11) DEFAULT 0;
    DECLARE f_APPDOCID int(11) DEFAULT 0;
    DECLARE f_APPFLAG smallint(6) DEFAULT NULL;
    DECLARE f_APPFILE varchar(255) DEFAULT 0;
    DECLARE f_AppFromId int(11) DEFAULT '0';
		DECLARE f_AppFromType int(11) DEFAULT '0';
		DECLARE f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;


  DECLARE cur CURSOR FOR
    SELECT
      a.MATERIALQUOTEID,
      a.DOCID,
      b.MATERIALTYPE,
      b.FILENAME,
      b.MASID,
      b.CrUser,
      b.CrTime
    FROM
      xwcmmaterialquote a
      LEFT JOIN xwcmmaterial b ON a.MATERIALID = b.MATERIALID
    WHERE b.MATERIALTYPE IN (1, 2)
    ORDER BY
      a.MATERIALQUOTEID ASC;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;

	OPEN cur;

	FETCH cur INTO
    f_APPENDIXID,
    f_APPDOCID,
    f_APPFLAG,
    f_APPFILE,
    f_AppFromId,
    f_CRUSER,
    f_CRTIME
    ;


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



    WHILE isover = 0 DO

      # 断点续传
      IF (f_APPENDIXID > prevAppendixId)

        THEN

            SET @APPENDIXID = f_APPENDIXID;
            SET f_APPENDIXID = f_APPENDIXID + appendixIdOffset;

            SET @doc_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = migr_table_name AND start_id < f_APPDOCID and  end_id >= f_APPDOCID), docIdOffset);
            SET f_APPDOCID = f_APPDOCID + @doc_offset_num;
						IF (f_APPFLAG = 1)
            THEN
              SET f_APPFLAG = 80;
							SET f_AppFromId = 0;
							SET f_AppFromType = 0;
						END IF;
						IF (f_APPFLAG = 2)
            THEN
              SET f_APPFLAG = 90;
              SET @mas_offset_num = ifnull((SELECT offset_num FROM trs_data_migration_mas.data_migration_offset WHERE table_name = 'mas_masvideo' AND start_id < f_AppFromId and  end_id >= f_AppFromId), masIdOffset);
							SET f_AppFromId = f_AppFromId + @mas_offset_num;
							SET f_APPFILE = f_AppFromId;
							SET f_AppFromType = 1;
						END IF;

            insert into trs_hycloud_iip.wcmappendix
              (
                APPENDIXID,
                APPDOCID,
                APPFILE,
                APPFILETYPE,
                APPFLAG,
                CRUSER,
                CRTIME,
                AppFromType,
                AppFromId
              )
              VALUES
                (
                  f_APPENDIXID,
                  f_APPDOCID,
                  f_APPFILE,
                  -1,
                  f_APPFLAG,
                  f_CRUSER,
                  f_CRTIME,
                  f_AppFromType,
                  f_AppFromId
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, appendixIdOffset, prevAppendixId, @APPENDIXID);
    END IF ;

		FETCH cur INTO
    f_APPENDIXID,
    f_APPDOCID,
    f_APPFLAG,
    f_APPFILE,
    f_AppFromId,
    f_CRUSER,
    f_CRTIME
    ;

	END WHILE;

	CLOSE cur;

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

	END $$
DELIMITER ;