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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmtemplate;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmtemplate(IN prevTemplateId BIGINT(20),
                                              IN templateIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmtemplate';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';


    DECLARE  f_TEMPID int(11) DEFAULT '0';
    DECLARE  f_TEMPNAME varchar(150) DEFAULT '0';
    DECLARE  f_TEMPDESC varchar(200) DEFAULT NULL;
    DECLARE  f_TEMPEXT varchar(50) DEFAULT 'htm';
    DECLARE  f_TEMPTEXT longtext;
    DECLARE  f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_APDMODIFIED smallint(6) DEFAULT '0';
    DECLARE  f_TEMPTYPE smallint(6) DEFAULT '0';
    DECLARE  f_TEMPFORMID int(11) DEFAULT '0';
    DECLARE  f_ISPARSED smallint(6) DEFAULT '0';
    DECLARE  f_FOLDERTYPE int(11) DEFAULT '103';
    DECLARE  f_ROOTID int(11) DEFAULT '0';
    DECLARE  f_FOLDERID int(11) DEFAULT '0';
    DECLARE  f_ROOTTYPE int(11) DEFAULT '0';
    DECLARE  f_OUTPUTFILENAME varchar(50) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSER varchar(60) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDTIME datetime DEFAULT NULL;
    DECLARE  f_VISUAL int(11) DEFAULT NULL;




    DECLARE cur CURSOR FOR
		SELECT
      TEMPID,
      TEMPNAME,
      TEMPDESC,
      TEMPEXT,
      TEMPTEXT,
      ATTRIBUTE,
      CRUSER,
      CRTIME,
      APDMODIFIED,
      TEMPTYPE,
      TEMPFORMID,
      ISPARSED,
      FOLDERTYPE,
      ROOTID,
      FOLDERID,
      ROOTTYPE,
      OUTPUTFILENAME,
      LASTMODIFIEDUSER,
      LASTMODIFIEDTIME,
      VISUAL
    FROM wcmtemplate
		ORDER BY TEMPID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPID,
    f_TEMPNAME,
    f_TEMPDESC,
    f_TEMPEXT,
    f_TEMPTEXT,
    f_ATTRIBUTE,
    f_CRUSER,
    f_CRTIME,
    f_APDMODIFIED,
    f_TEMPTYPE,
    f_TEMPFORMID,
    f_ISPARSED,
    f_FOLDERTYPE,
    f_ROOTID,
    f_FOLDERID,
    f_ROOTTYPE,
    f_OUTPUTFILENAME,
    f_LASTMODIFIEDUSER,
    f_LASTMODIFIEDTIME,
    f_VISUAL
    ;


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


    WHILE isover= 0 DO

      # 断点续传
      IF (f_TEMPID > prevTemplateId)

        THEN

            SET @TEMPID = f_TEMPID;
            SET f_TEMPID = f_TEMPID + templateIdOffset;

            IF (f_FOLDERTYPE = 103)
              THEN
                SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_FOLDERID and  end_id >= f_FOLDERID), siteIdOffset);
                SET f_FOLDERID = f_FOLDERID + @site_offset_num;
            END IF ;

            IF (f_FOLDERTYPE = 101)
              THEN
                SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_FOLDERID and  end_id >= f_FOLDERID), channelIdOffset);
                SET f_FOLDERID = f_FOLDERID + @channel_offset_num;
            END IF ;

            IF (f_ROOTTYPE = 103)
              THEN
                SET @root_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_ROOTID and  end_id >= f_ROOTID), siteIdOffset);
                SET f_ROOTID = f_ROOTID + @root_offset_num;
            END IF ;

            IF (f_ROOTTYPE = 101)
              THEN
                SET @rootcl_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_ROOTID and  end_id >= f_ROOTID), channelIdOffset);
                SET f_ROOTID = f_ROOTID + @rootcl_offset_num;
            END IF ;


            insert into trs_hycloud_iip.wcmtemplate
              (
                TEMPID,
                TEMPNAME,
                TEMPDESC,
                TEMPEXT,
                TEMPTEXT,
                ATTRIBUTE,
                CRUSER,
                CRTIME,
                APDMODIFIED,
                TEMPTYPE,
                TEMPFORMID,
                ISPARSED,
                FOLDERTYPE,
                ROOTID,
                FOLDERID,
                ROOTTYPE,
                OUTPUTFILENAME,
                LASTMODIFIEDUSER,
                LASTMODIFIEDTIME,
                VISUAL
              )
              VALUES
                (
                  f_TEMPID,
                  f_TEMPNAME,
                  f_TEMPDESC,
                  f_TEMPEXT,
                  f_TEMPTEXT,
                  f_ATTRIBUTE,
                  f_CRUSER,
                  f_CRTIME,
                  f_APDMODIFIED,
                  f_TEMPTYPE,
                  f_TEMPFORMID,
                  f_ISPARSED,
                  f_FOLDERTYPE,
                  f_ROOTID,
                  f_FOLDERID,
                  f_ROOTTYPE,
                  f_OUTPUTFILENAME,
                  f_LASTMODIFIEDUSER,
                  f_LASTMODIFIEDTIME,
                  f_VISUAL
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, templateIdOffset, prevTemplateId, @TEMPID);
    END IF ;

		FETCH cur INTO
      f_TEMPID,
      f_TEMPNAME,
      f_TEMPDESC,
      f_TEMPEXT,
      f_TEMPTEXT,
      f_ATTRIBUTE,
      f_CRUSER,
      f_CRTIME,
      f_APDMODIFIED,
      f_TEMPTYPE,
      f_TEMPFORMID,
      f_ISPARSED,
      f_FOLDERTYPE,
      f_ROOTID,
      f_FOLDERID,
      f_ROOTTYPE,
      f_OUTPUTFILENAME,
      f_LASTMODIFIEDUSER,
      f_LASTMODIFIEDTIME,
      f_VISUAL
    ;
		
	END WHILE;
	
	CLOSE cur;

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



  END $$
DELIMITER ;