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

use trs_data_migration;

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmviewinfo;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmviewinfo(IN citySuffix VARCHAR(20),
                                              IN prevViewId BIGINT(20),
                                              IN viewIdOffset BIGINT(20))
	BEGIN 
	
	DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE tabel_migr_table_name varchar(255) DEFAULT 'xwcmtableinfo';

    DECLARE  f_VIEWINFOID int(11) DEFAULT NULL;
    DECLARE  f_VIEWNAME varchar(100) DEFAULT NULL;
    DECLARE  f_VIEWDESC varchar(400) DEFAULT NULL;
    DECLARE  f_MAINTABLENAME varchar(50) DEFAULT NULL;
    DECLARE  f_MAINTABLEID int(11) 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  f_HIDDENAPPENDIX smallint(6) DEFAULT '0';
    DECLARE  f_ISSPECIAL int(11) DEFAULT NULL;
    DECLARE  f_VIEWCLASSID int(11) DEFAULT NULL;
    DECLARE  f_VIEWDESCCON varchar(500) DEFAULT NULL;
    DECLARE  f_METASYNFIELDS varchar(500) DEFAULT NULL;
    DECLARE  f_TEMPLATEPATH varchar(50) DEFAULT NULL;
    DECLARE  f_HIDDENFILEAPPENDIX smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENIMGAPPENDIX smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENLINKAPPENDIX smallint(6) DEFAULT '0';
    DECLARE  f_Attribute varchar(500) DEFAULT NULL;




  DECLARE cur CURSOR FOR
		SELECT
      VIEWINFOID,
      VIEWNAME,
      VIEWDESC,
      MAINTABLENAME,
      MAINTABLEID,
      CRUSER,
      CRTIME,
      OWNERTYPE,
      OWNERID,
      HIDDENAPPENDIX,
      ISSPECIAL,
      VIEWCLASSID,
      VIEWDESCCON,
      METASYNFIELDS,
      TEMPLATEPATH,
      HIDDENFILEAPPENDIX,
      HIDDENIMGAPPENDIX,
      HIDDENLINKAPPENDIX,
      null
    FROM trs_data_migration.xwcmviewinfo
		ORDER BY VIEWINFOID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_VIEWINFOID,
    f_VIEWNAME,
    f_VIEWDESC,
    f_MAINTABLENAME,
    f_MAINTABLEID,
    f_CRUSER,
    f_CRTIME,
    f_OWNERTYPE,
    f_OWNERID,
    f_HIDDENAPPENDIX,
    f_ISSPECIAL,
    f_VIEWCLASSID,
    f_VIEWDESCCON,
    f_METASYNFIELDS,
    f_TEMPLATEPATH,
    f_HIDDENFILEAPPENDIX,
    f_HIDDENIMGAPPENDIX,
    f_HIDDENLINKAPPENDIX,
    f_Attribute
    ;


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



    WHILE isover= 0 DO

      # 断点续传
      IF (f_VIEWINFOID > prevViewId)

        THEN

            SET @VIEWINFOID = f_VIEWINFOID;
            SET f_VIEWINFOID = f_VIEWINFOID + viewIdOffset;

            SET @table_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = tabel_migr_table_name AND start_id < f_MAINTABLEID and  end_id >= f_MAINTABLEID), 0);
            SET f_MAINTABLEID = f_MAINTABLEID + @table_offset_num;

            insert into trs_hycloud_iip.xwcmviewinfo
              (
                VIEWINFOID,
                VIEWNAME,
                VIEWDESC,
                MAINTABLENAME,
                MAINTABLEID,
                CRUSER,
                CRTIME,
                OWNERTYPE,
                OWNERID,
                HIDDENAPPENDIX,
                ISSPECIAL,
                VIEWCLASSID,
                VIEWDESCCON,
                METASYNFIELDS,
                TEMPLATEPATH,
                HIDDENFILEAPPENDIX,
                HIDDENIMGAPPENDIX,
                HIDDENLINKAPPENDIX,
                Attribute
              )
              VALUES
                (
                  f_VIEWINFOID,
                  concat(f_VIEWNAME, citySuffix),
                  concat(f_VIEWDESC, citySuffix),
                  concat(f_MAINTABLENAME, citySuffix),
                  f_MAINTABLEID,
                  f_CRUSER,
                  f_CRTIME,
                  f_OWNERTYPE,
                  f_OWNERID,
                  f_HIDDENAPPENDIX,
                  f_ISSPECIAL,
                  f_VIEWCLASSID,
                  f_VIEWDESCCON,
                  f_METASYNFIELDS,
                  f_TEMPLATEPATH,
                  f_HIDDENFILEAPPENDIX,
                  f_HIDDENIMGAPPENDIX,
                  f_HIDDENLINKAPPENDIX,
                  f_Attribute
                );

          SET @log_sql = trs_data_migration.build_migration_log_sql(migr_table_name, @VIEWINFOID, now());

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call trs_data_migration.setOffset(migr_table_name, viewIdOffset, prevViewId, @VIEWINFOID);
    END IF ;

		FETCH cur INTO
      f_VIEWINFOID,
      f_VIEWNAME,
      f_VIEWDESC,
      f_MAINTABLENAME,
      f_MAINTABLEID,
      f_CRUSER,
      f_CRTIME,
      f_OWNERTYPE,
      f_OWNERID,
      f_HIDDENAPPENDIX,
      f_ISSPECIAL,
      f_VIEWCLASSID,
      f_VIEWDESCCON,
      f_METASYNFIELDS,
      f_TEMPLATEPATH,
      f_HIDDENFILEAPPENDIX,
      f_HIDDENIMGAPPENDIX,
      f_HIDDENLINKAPPENDIX,
      f_Attribute
      ;
		
	END WHILE;
	
	CLOSE cur;

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



  END $$
DELIMITER ;