TRS.WCM.Developer/TRS.WCM.Developer/贵州项目/sp/p_data_migration_xwcmdbinfo.sql

use trs_hycloud_iip;

DROP PROCEDURE IF EXISTS p_data_migration_xwcmdbfieldinfo;
DELIMITER $$
CREATE PROCEDURE p_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  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 v_wcm.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
    ;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_DBFIELDINFOID > prevFieldId)

        THEN

            SET @DBFIELDINFOID = f_DBFIELDINFOID;
            SET f_DBFIELDINFOID = f_DBFIELDINFOID + fieldIdOffset;
            SET f_TABLEID = f_TABLEID + tableIdOffset;
            SET f_CLASSID = f_CLASSID + classInfoIdOffset;

            insert into trs_hycloud_iip.xwcmtableinfo
              (
                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
                );

            SELECT CONCAT('成功迁移 xwcmdbfieldinfo[DBFIELDINFOID=', @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 '迁移 xwcmdbfieldinfo 完毕';
		
	END $$
DELIMITER ;