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

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

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmtableinfo';

    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 xwcmtableinfo
		ORDER BY TABLEINFOID asc;

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



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


    WHILE isover= 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
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, tableInfoIdOffset, prevTableInfoId, @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 concat('完成迁移 ', migr_table_name) info;


  END $$
DELIMITER ;