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

DROP PROCEDURE IF EXISTS update_data_table;
DELIMITER $$
CREATE PROCEDURE update_data_table(
                            IN citySuffix VARCHAR(20),
                            IN prevtabelId BIGINT(20))
	BEGIN 
	
	DECLARE isover INT DEFAULT 0;
	DECLARE table_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  v_sql longtext DEFAULT '';




  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
    ;
    set @i = 0;
    WHILE isover= 0 DO

      # 断点续传
      IF (f_TABLEINFOID > prevtabelId)

        THEN
              set @max_dbfieldinfo_id = ifnull((SELECT MAX(DBFIELDINFOID) from xwcmdbfieldinfo), 0);
              set @sql = '';
              call update_data_dbfield(f_TABLENAME, f_TABLEINFOID, @max_dbfieldinfo_id + (@i * 9), @sql);

              set @i = @i + 1;
              set @id_field_sql = concat(' ALTER TABLE wcmmetatable', f_TABLENAME,
                  ' CHANGE COLUMN `WCMMetaTable',f_TABLENAME,
                                   'ID` `wcmmetatable',f_TABLENAME, citySuffix,
                                   'ID`  int(11) NOT NULL;');

              set v_sql = concat(v_sql, @sql, @id_field_sql);
              SET @log_sql = build_migration_log_sql('update_data_tabel_id', f_TABLEINFOID, now());
              prepare stmt from @log_sql;
              EXECUTE stmt;
              deallocate prepare stmt;
      END IF ;

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

  END $$
DELIMITER ;