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

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmdbfieldinfo;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmdbfieldinfo(IN citySuffix VARCHAR(20),
                                              IN prevFieldId BIGINT(20),
                                              IN fieldIdOffset BIGINT(20),
                                              IN classInfoIdOffset BIGINT(20),
                                              IN tableIdOffset BIGINT(20))
	BEGIN
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmdbfieldinfo';
    DECLARE tabel_migr_table_name varchar(255) DEFAULT 'xwcmtableinfo';
    DECLARE class_igr_table_name varchar(255) DEFAULT 'xwcmclassinfo';

    DECLARE  f_DBFIELDINFOID int(11) DEFAULT NULL;
    DECLARE  f_TABLENAME varchar(50) DEFAULT '0';
    DECLARE  f_TABLEID int(11) DEFAULT '0';
    DECLARE  f_FIELDNAME varchar(50) DEFAULT '0';
    DECLARE  f_ANOTHERNAME varchar(200) DEFAULT '0';
    DECLARE  f_FIELDTYPE int(11) DEFAULT '0';
    DECLARE  f_DBTYPE int(11) DEFAULT '0';
    DECLARE  f_DBLENGTH int(11) DEFAULT '0';
    DECLARE  f_DEFAULTVALUE varchar(200) DEFAULT NULL;
    DECLARE  f_ENMVALUE varchar(1000) DEFAULT NULL;
    DECLARE  f_NOTNULL smallint(6) DEFAULT NULL;
    DECLARE  f_CLASSID int(11) DEFAULT '0';
    DECLARE  f_DBSCALE int(11) DEFAULT '0';
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_VALIDATOR varchar(200) DEFAULT NULL;
    DECLARE  f_RADORCHK smallint(6) DEFAULT NULL;
    DECLARE  f_NOTEDIT smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENFIELD smallint(6) DEFAULT '0';



  DECLARE cur CURSOR FOR
		SELECT
      DBFIELDINFOID,
      TABLENAME,
      TABLEID,
      FIELDNAME,
      ANOTHERNAME,
      FIELDTYPE,
      DBTYPE,
      DBLENGTH,
      DEFAULTVALUE,
      ENMVALUE,
      NOTNULL,
      CLASSID,
      DBSCALE,
      CRUSER,
      CRTIME,
      VALIDATOR,
      RADORCHK,
      NOTEDIT,
      HIDDENFIELD
    FROM xwcmdbfieldinfo
		ORDER BY DBFIELDINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_DBFIELDINFOID,
    f_TABLENAME,
    f_TABLEID,
    f_FIELDNAME,
    f_ANOTHERNAME,
    f_FIELDTYPE,
    f_DBTYPE,
    f_DBLENGTH,
    f_DEFAULTVALUE,
    f_ENMVALUE,
    f_NOTNULL,
    f_CLASSID,
    f_DBSCALE,
    f_CRUSER,
    f_CRTIME,
    f_VALIDATOR,
    f_RADORCHK,
    f_NOTEDIT,
    f_HIDDENFIELD
    ;



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



    WHILE isover= 0 DO

      # 断点续传
      IF (f_DBFIELDINFOID > prevFieldId)

        THEN

            SET @DBFIELDINFOID = f_DBFIELDINFOID;
            SET f_DBFIELDINFOID = f_DBFIELDINFOID + fieldIdOffset;
            SET @table_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = tabel_migr_table_name AND start_id < f_TABLEID and  end_id >= f_TABLEID), tableIdOffset);
            SET f_TABLEID = f_TABLEID + @table_offset_num;

            SET @class_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = class_igr_table_name AND start_id < f_CLASSID and  end_id >= f_CLASSID), classInfoIdOffset);
            SET f_CLASSID = f_CLASSID + @class_offset_num;

            insert into trs_hycloud_iip.xwcmdbfieldinfo
              (
                DBFIELDINFOID,
                TABLENAME,
                TABLEID,
                FIELDNAME,
                ANOTHERNAME,
                FIELDTYPE,
                DBTYPE,
                DBLENGTH,
                DEFAULTVALUE,
                ENMVALUE,
                NOTNULL,
                CLASSID,
                DBSCALE,
                CRUSER,
                CRTIME,
                VALIDATOR,
                RADORCHK,
                NOTEDIT,
                HIDDENFIELD
              )
              VALUES
                (
                  f_DBFIELDINFOID,
                  concat(f_TABLENAME, citySuffix),
                  f_TABLEID,
                  f_FIELDNAME,
                  f_ANOTHERNAME,
                  f_FIELDTYPE,
                  f_DBTYPE,
                  f_DBLENGTH,
                  f_DEFAULTVALUE,
                  f_ENMVALUE,
                  f_NOTNULL,
                  f_CLASSID,
                  f_DBSCALE,
                  f_CRUSER,
                  f_CRTIME,
                  f_VALIDATOR,
                  f_RADORCHK,
                  f_NOTEDIT,
                  f_HIDDENFIELD
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, fieldIdOffset, prevFieldId, @DBFIELDINFOID);
    END IF ;

		FETCH cur INTO
      f_DBFIELDINFOID,
      f_TABLENAME,
      f_TABLEID,
      f_FIELDNAME,
      f_ANOTHERNAME,
      f_FIELDTYPE,
      f_DBTYPE,
      f_DBLENGTH,
      f_DEFAULTVALUE,
      f_ENMVALUE,
      f_NOTNULL,
      f_CLASSID,
      f_DBSCALE,
      f_CRUSER,
      f_CRTIME,
      f_VALIDATOR,
      f_RADORCHK,
      f_NOTEDIT,
      f_HIDDENFIELD
      ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;