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

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmclassinfoview;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmclassinfoview(IN prevClassInfoViewId BIGINT(20),
                                              IN classInfoViewIdOffset BIGINT(20),
                                              IN classInfoIdOffset BIGINT(20),
                                              IN docIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmclassinfoview';
    DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE class_migr_table_name varchar(255) DEFAULT 'xwcmclassinfo';
    DECLARE doc_migr_table_name varchar(255) DEFAULT 'wcmdocument';

    DECLARE  f_CLASSINFOVIEWID int(11) DEFAULT NULL;
    DECLARE  f_CLASSINFOID int(11) DEFAULT NULL;
    DECLARE  f_METADATAID int(11) DEFAULT NULL;
    DECLARE  f_VIEWID int(11) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;



  DECLARE cur CURSOR FOR
		SELECT
      CLASSINFOVIEWID,
      CLASSINFOID,
      METADATAID,
      VIEWID,
      CRUSER,
      CRTIME
    FROM xwcmclassinfoview
		ORDER BY CLASSINFOVIEWID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CLASSINFOVIEWID,
    f_CLASSINFOID,
    f_METADATAID,
    f_VIEWID,
    f_CRUSER,
    f_CRTIME
    ;



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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_CLASSINFOVIEWID > prevClassInfoViewId)

        THEN

          SET @CLASSINFOVIEWID = f_CLASSINFOVIEWID;
          SET f_CLASSINFOVIEWID = f_CLASSINFOVIEWID + classInfoViewIdOffset;

          SET @class_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = class_migr_table_name AND start_id < f_CLASSINFOID and  end_id >= f_CLASSINFOID), 0);
          SET f_CLASSINFOID = f_CLASSINFOID + @class_offset_num;
          SET @doc_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = doc_migr_table_name AND start_id < f_METADATAID and  end_id >= f_METADATAID), 0);
          SET f_METADATAID = f_METADATAID + @doc_offset_num;
          SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_VIEWID and  end_id >= f_VIEWID), 0);
          SET f_VIEWID = f_VIEWID + @view_offset_num;


            insert into trs_hycloud_iip.xwcmclassinfoview
              (
                CLASSINFOVIEWID,
                CLASSINFOID,
                METADATAID,
                VIEWID,
                CRUSER,
                CRTIME
              )
              VALUES
                (
                  f_CLASSINFOVIEWID,
                  f_CLASSINFOID,
                  f_METADATAID,
                  f_VIEWID,
                  f_CRUSER,
                  f_CRTIME
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, classInfoViewIdOffset, prevClassInfoViewId, @CLASSINFOVIEWID);
    END IF ;

		FETCH cur INTO
      f_CLASSINFOVIEWID,
      f_CLASSINFOID,
      f_METADATAID,
      f_VIEWID,
      f_CRUSER,
      f_CRTIME
      ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;