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

use trs_data_migration;

DROP PROCEDURE IF EXISTS sp_data_migration_xwcmmetaviewemployer;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_xwcmmetaviewemployer(IN prevMetaViewEmployerId BIGINT(20),
                                              IN metaViewEmployerIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE  f_VIEWID int(11) DEFAULT '0';
    DECLARE  f_CHANNELID int(11) DEFAULT '0';
    DECLARE  f_METAVIEWEMPLOYERID int(11) DEFAULT '0';
    DECLARE  f_EmployerType int(11) DEFAULT '101';
    DECLARE  f_EmployerId int(11) DEFAULT '0';
    DECLARE  f_FOLDERTYPE int(11) DEFAULT '101';
    DECLARE  f_FOLDERID int(11) DEFAULT '0';


    DECLARE cur CURSOR FOR
		SELECT
      VIEWID,
      CHANNELID,
      METAVIEWEMPLOYERID,
      EmployerType,
      EmployerId
    FROM trs_data_migration.xwcmmetaviewemployer
		ORDER BY METAVIEWEMPLOYERID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_VIEWID,
    f_CHANNELID,
    f_METAVIEWEMPLOYERID,
    f_EmployerType,
    f_EmployerId
    ;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_METAVIEWEMPLOYERID > prevMetaViewEmployerId)

        THEN

            SET @METAVIEWEMPLOYERID = f_METAVIEWEMPLOYERID;
            SET f_METAVIEWEMPLOYERID = f_METAVIEWEMPLOYERID + metaViewEmployerIdOffset;

            IF (f_FOLDERTYPE = 103)
              THEN SET f_FOLDERID = f_FOLDERID + siteIdOffset;
            END IF ;

            IF (f_FOLDERTYPE = 101)
              THEN SET f_FOLDERID = f_FOLDERID + channelIdOffset;
            END IF ;


            insert into trs_hycloud_iip.xwcmmetaviewemployer
              (
                VIEWID,
                CHANNELID,
                METAVIEWEMPLOYERID,
                EmployerType,
                EmployerId
              )
              VALUES
                (
                  f_VIEWID,
                  f_CHANNELID,
                  f_METAVIEWEMPLOYERID,
                  f_EmployerType,
                  f_EmployerId
                );

          SET @sql = trs_data_migration.build_migration_log_sql('xwcmmetaviewemployer', @METAVIEWEMPLOYERID);

          prepare stmt from @sql;
          EXECUTE stmt;
          deallocate prepare stmt;
    END IF ;

		FETCH cur INTO
      f_VIEWID,
      f_CHANNELID,
      f_METAVIEWEMPLOYERID,
      f_EmployerType,
      f_EmployerId
    ;
		
	END WHILE;
	
	CLOSE cur;

    SET @sql = trs_data_migration.build_migration_log_sql('xwcmmetaviewemployer', -1);

    prepare stmt from @sql;
    EXECUTE stmt;
    deallocate prepare stmt;
		
	END $$
DELIMITER ;