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

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmmetaviewfieldgroup;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmmetaviewfieldgroup(IN prevMetaViewFieldGroupId BIGINT(20),
                                              IN metaViewFieldGroupIdOffset BIGINT(20),
                                              IN viewIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmmetaviewfieldgroup';

    DECLARE  f_METAVIEWFIELDGROUPID int(11) DEFAULT NULL;
    DECLARE  f_GROUPNAME varchar(50) DEFAULT '0';
    DECLARE  f_METAVIEWID int(11) DEFAULT '0';
    DECLARE  f_PARENTID int(11) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_GROUPORDER int(11) DEFAULT '0';




  DECLARE cur CURSOR FOR
		SELECT
      METAVIEWFIELDGROUPID,
      GROUPNAME,
      METAVIEWID,
      PARENTID,
      CRUSER,
      CRTIME,
      GROUPORDER
    FROM xwcmmetaviewfieldgroup
		ORDER BY METAVIEWFIELDGROUPID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_METAVIEWFIELDGROUPID,
    f_GROUPNAME,
    f_METAVIEWID,
    f_PARENTID,
    f_CRUSER,
    f_CRTIME,
    f_GROUPORDER
    ;



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

    WHILE isover= 0 DO

      # 断点续传
      IF (f_METAVIEWFIELDGROUPID > prevMetaViewFieldGroupId)

        THEN

            SET @METAVIEWFIELDGROUPID = f_METAVIEWFIELDGROUPID;
            SET f_METAVIEWFIELDGROUPID = f_METAVIEWFIELDGROUPID + metaViewFieldGroupIdOffset;
            SET f_METAVIEWID = f_METAVIEWID + viewIdOffset;
            SET f_PARENTID = f_PARENTID + metaViewFieldGroupIdOffset;

            insert into trs_hycloud_iip.xwcmmetaviewfieldgroup
              (
                METAVIEWFIELDGROUPID,
                GROUPNAME,
                METAVIEWID,
                PARENTID,
                CRUSER,
                CRTIME,
                GROUPORDER
              )
              VALUES
                (
                  f_METAVIEWFIELDGROUPID,
                  f_GROUPNAME,
                  f_METAVIEWID,
                  f_PARENTID,
                  f_CRUSER,
                  f_CRTIME,
                  f_GROUPORDER
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
    END IF ;

		FETCH cur INTO
      f_METAVIEWFIELDGROUPID,
      f_GROUPNAME,
      f_METAVIEWID,
      f_PARENTID,
      f_CRUSER,
      f_CRTIME,
      f_GROUPORDER
      ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;