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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmappendix;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmappendix(IN prevAppendixId BIGINT(20),
                                              IN appendixIdOffset BIGINT(20),
                                              IN docIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmappendix';
    DECLARE doc_migr_table_name varchar(255) DEFAULT 'wcmdocument';

    DECLARE f_APPENDIXID int(11) DEFAULT 0;
    DECLARE f_APPDOCID int(11) DEFAULT 0;
    DECLARE f_APPFILE varchar(255) DEFAULT 0;
    DECLARE f_APPFILETYPE int(11) DEFAULT '-1';
    DECLARE f_APPDESC varchar(400) DEFAULT NULL;
    DECLARE f_APPLINKALT varchar(255) DEFAULT NULL;
    DECLARE f_APPSERN int(11) DEFAULT NULL;
    DECLARE f_APPPROP int(11) DEFAULT NULL;
    DECLARE f_APPFLAG smallint(6) DEFAULT NULL;
    DECLARE f_APPTIME datetime DEFAULT NULL;
    DECLARE f_APPAUTHOR varchar(100) DEFAULT NULL;
    DECLARE f_APPEDITOR varchar(100) DEFAULT NULL;
    DECLARE f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE f_USEDVERSIONS decimal(30,0) DEFAULT '1';
    DECLARE f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;
    DECLARE f_SRCFILE varchar(400) DEFAULT NULL;
    DECLARE f_FILEEXT varchar(20) DEFAULT NULL;
    DECLARE f_ISIMPORTTOIMAGELIB int(11) DEFAULT '0';
    DECLARE f_RELATEDCOLUMN varchar(200) DEFAULT NULL;
    DECLARE f_RELATEPHOTOIDS varchar(100) DEFAULT NULL;
    DECLARE f_APPDESC2 varchar(300) DEFAULT NULL;
    DECLARE f_ATTRIBUTES varchar(800) DEFAULT NULL;
    DECLARE f_AppFromType int(11) DEFAULT '0';
    DECLARE f_AppFromId int(11) DEFAULT '0';




  DECLARE cur CURSOR FOR
		SELECT
      APPENDIXID,
      APPDOCID,
      APPFILE,
      APPFILETYPE,
      APPDESC,
      APPLINKALT,
      APPSERN,
      APPPROP,
      APPFLAG,
      APPTIME,
      APPAUTHOR,
      APPEDITOR,
      ATTRIBUTE,
      USEDVERSIONS,
      CRUSER,
      CRTIME,
      SRCFILE,
      FILEEXT,
      ISIMPORTTOIMAGELIB,
      RELATEDCOLUMN,
      RELATEPHOTOIDS,
      APPDESC2,
      ATTRIBUTES,
      0,
      0
    FROM wcmappendix
		ORDER BY APPENDIXID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_APPENDIXID,
    f_APPDOCID,
    f_APPFILE,
    f_APPFILETYPE,
    f_APPDESC,
    f_APPLINKALT,
    f_APPSERN,
    f_APPPROP,
    f_APPFLAG,
    f_APPTIME,
    f_APPAUTHOR,
    f_APPEDITOR,
    f_ATTRIBUTE,
    f_USEDVERSIONS,
    f_CRUSER,
    f_CRTIME,
    f_SRCFILE,
    f_FILEEXT,
    f_ISIMPORTTOIMAGELIB,
    f_RELATEDCOLUMN,
    f_RELATEPHOTOIDS,
    f_APPDESC2,
    f_ATTRIBUTES,
    f_AppFromType,
    f_AppFromId
    ;


    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;

            insert into trs_hycloud_iip.wcmappendix
              (
                APPENDIXID,
                APPDOCID,
                APPFILE,
                APPFILETYPE,
                APPDESC,
                APPLINKALT,
                APPSERN,
                APPPROP,
                APPFLAG,
                APPTIME,
                APPAUTHOR,
                APPEDITOR,
                ATTRIBUTE,
                USEDVERSIONS,
                CRUSER,
                CRTIME,
                SRCFILE,
                FILEEXT,
                ISIMPORTTOIMAGELIB,
                RELATEDCOLUMN,
                RELATEPHOTOIDS,
                APPDESC2,
                ATTRIBUTES,
                AppFromType,
                AppFromId
              )
              VALUES
                (
                  f_APPENDIXID,
                  f_APPDOCID,
                  f_APPFILE,
                  f_APPFILETYPE,
                  f_APPDESC,
                  f_APPLINKALT,
                  f_APPSERN,
                  f_APPPROP,
                  f_APPFLAG,
                  f_APPTIME,
                  f_APPAUTHOR,
                  f_APPEDITOR,
                  f_ATTRIBUTE,
                  f_USEDVERSIONS,
                  f_CRUSER,
                  f_CRTIME,
                  f_SRCFILE,
                  f_FILEEXT,
                  f_ISIMPORTTOIMAGELIB,
                  f_RELATEDCOLUMN,
                  f_RELATEPHOTOIDS,
                  f_APPDESC2,
                  f_ATTRIBUTES,
                  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_APPFILE,
      f_APPFILETYPE,
      f_APPDESC,
      f_APPLINKALT,
      f_APPSERN,
      f_APPPROP,
      f_APPFLAG,
      f_APPTIME,
      f_APPAUTHOR,
      f_APPEDITOR,
      f_ATTRIBUTE,
      f_USEDVERSIONS,
      f_CRUSER,
      f_CRTIME,
      f_SRCFILE,
      f_FILEEXT,
      f_ISIMPORTTOIMAGELIB,
      f_RELATEDCOLUMN,
      f_RELATEPHOTOIDS,
      f_APPDESC2,
      f_ATTRIBUTES,
      f_AppFromType,
      f_AppFromId
      ;
		
	END WHILE;
	
	CLOSE cur;

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