TRS.WCM.Developer/TRS.WCM.Developer/sp/p_data_migration_xwcmtableinfo.sql

DROP PROCEDURE IF EXISTS p_data_migration_xwcmtableinfo;
DELIMITER $$
CREATE PROCEDURE p_data_migration_xwcmtableinfo(IN citySuffix VARCHAR(20),
                                              IN prevTableInfoId BIGINT(20),
                                              IN tableInfoIdOffset BIGINT(20))
	BEGIN 
	
	  DECLARE over INT DEFAULT 0;

    DECLARE  f_TABLEINFOID int(11) DEFAULT NULL;
    DECLARE  f_TABLENAME varchar(50) DEFAULT NULL;
    DECLARE  f_ANOTHERNAME varchar(100) DEFAULT NULL;
    DECLARE  f_TABLEDESC varchar(200) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_OWNERTYPE int(11) DEFAULT '1';
    DECLARE  f_OWNERID int(11) DEFAULT '4';


  DECLARE cur CURSOR FOR
		SELECT
      TABLEINFOID,
      TABLENAME,
      ANOTHERNAME,
      TABLEDESC,
      CRUSER,
      CRTIME,
      OWNERTYPE,
      OWNERID
    FROM v_wcm.xwcmtableinfo
		ORDER BY TABLEINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET over = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TABLEINFOID,
    f_TABLENAME,
    f_ANOTHERNAME,
    f_TABLEDESC,
    f_CRUSER,
    f_CRTIME,
    f_OWNERTYPE,
    f_OWNERID
    ;



    WHILE over= 0 DO

      # 断点续传
      IF (f_TABLEINFOID > prevTableInfoId)

        THEN

            SET @TABLEINFOID = f_TABLEINFOID;
            SET f_TABLEINFOID = f_TABLEINFOID + tableInfoIdOffset;

            insert into trs_hycloud_iip.xwcmtableinfo
              (
                TABLEINFOID,
                TABLENAME,
                ANOTHERNAME,
                TABLEDESC,
                CRUSER,
                CRTIME,
                OWNERTYPE,
                OWNERID
              )
              VALUES
                (
                  f_TABLEINFOID,
                  concat(f_TABLENAME, citySuffix),
                  concat(f_ANOTHERNAME, citySuffix),
                  concat(f_TABLEDESC, citySuffix),
                  f_CRUSER,
                  f_CRTIME,
                  f_OWNERTYPE,
                  f_OWNERID
                );

            SELECT CONCAT('成功迁移 xwcmtableinfo[TABLEINFOID=', @TABLEINFOID, ']') ;
    END IF ;

		FETCH cur INTO
      f_TABLEINFOID,
      f_TABLENAME,
      f_ANOTHERNAME,
      f_TABLEDESC,
      f_CRUSER,
      f_CRTIME,
      f_OWNERTYPE,
      f_OWNERID
      ;
		
	END WHILE;
	
	CLOSE cur;

  SELECT '迁移 xwcmtableinfo 完毕';
		
	END$$
DELIMITER ;