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

DROP PROCEDURE IF EXISTS update_data_relation_appendix_2;
DELIMITER $$
CREATE PROCEDURE update_data_relation_appendix_2(
                                  IN citySuffix VARCHAR(20),
                                  IN preTableId BIGINT(20),
                                  IN preDocId BIGINT(20))
	BEGIN 
	
	  DECLARE isover INT DEFAULT 0;

    DECLARE  f_DBFIELDINFOID int(11) DEFAULT NULL;
    DECLARE  f_TABLENAME varchar(50) DEFAULT '0';
    DECLARE  f_TABLEID int(11) DEFAULT '0';
    DECLARE  f_FIELDNAME varchar(50) DEFAULT '0';
    DECLARE  f_ANOTHERNAME varchar(200) DEFAULT '0';
    DECLARE  f_FIELDTYPE int(11) DEFAULT '0';
    DECLARE  f_DBTYPE int(11) DEFAULT '0';
    DECLARE  f_DBLENGTH int(11) DEFAULT '0';
    DECLARE  f_DEFAULTVALUE varchar(200) DEFAULT NULL;
    DECLARE  f_ENMVALUE varchar(1000) DEFAULT NULL;
    DECLARE  f_NOTNULL smallint(6) DEFAULT NULL;
    DECLARE  f_CLASSID int(11) DEFAULT '0';
    DECLARE  f_DBSCALE int(11) DEFAULT '0';
    DECLARE  f_CRUSER varchar(30) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_VALIDATOR varchar(200) DEFAULT NULL;
    DECLARE  f_RADORCHK smallint(6) DEFAULT NULL;
    DECLARE  f_NOTEDIT smallint(6) DEFAULT '0';
    DECLARE  f_HIDDENFIELD smallint(6) DEFAULT '0';



  DECLARE cur CURSOR FOR
		SELECT
      DBFIELDINFOID,
      TABLENAME,
      TABLEID,
      FIELDNAME,
      ANOTHERNAME,
      FIELDTYPE,
      DBTYPE,
      DBLENGTH,
      DEFAULTVALUE,
      ENMVALUE,
      NOTNULL,
      CLASSID,
      DBSCALE,
      CRUSER,
      CRTIME,
      VALIDATOR,
      RADORCHK,
      NOTEDIT,
      HIDDENFIELD
    FROM xwcmdbfieldinfo
    where FIELDTYPE in (32, 20)
		ORDER BY TABLEID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;

	OPEN cur;

	FETCH cur INTO
    f_DBFIELDINFOID,
    f_TABLENAME,
    f_TABLEID,
    f_FIELDNAME,
    f_ANOTHERNAME,
    f_FIELDTYPE,
    f_DBTYPE,
    f_DBLENGTH,
    f_DEFAULTVALUE,
    f_ENMVALUE,
    f_NOTNULL,
    f_CLASSID,
    f_DBSCALE,
    f_CRUSER,
    f_CRTIME,
    f_VALIDATOR,
    f_RADORCHK,
    f_NOTEDIT,
    f_HIDDENFIELD
    ;

    WHILE isover= 0 DO

      # 断点续传
      IF (f_TABLEID > preTableId)

        THEN
              SET @metaTableExists = (SELECT count(1) from information_schema.tables where table_schema = 'trs_hycloud_iip' and table_name = concat('wcmmetatable', f_TABLENAME, citySuffix) );

              select CONCAT('开始更新 :', @metaTableExists, '====:','wcmmetatable', f_TABLENAME, citySuffix, '-字段名称:', f_FIELDNAME, ';字段类型:', f_FIELDTYPE) info;
              IF (@metaTableExists > 0)
                then
                  #相关图片
                  if (f_FIELDTYPE = 32)
                    then
                    set @u_p_sql = CONCAT('UPDATE trs_hycloud_iip.wcmmetatable',f_TABLENAME, citySuffix,' a,
                                          relphoto_doc b
                                          SET a.',f_FIELDNAME,' = b.appendixids
                                          WHERE a.MetaDataId = b.docid and a.MetaDataId > ',preDocId,' ;');
                        prepare stmt from @u_p_sql;
                        EXECUTE stmt;
                        deallocate prepare stmt;
                  end if;
                  #相关视频
                  if (f_FIELDTYPE = 20)
                    then
                    set @u_v_sql = CONCAT('UPDATE trs_hycloud_iip.wcmmetatable',f_TABLENAME, citySuffix,' a,
                                          relvideo_doc b
                                          SET a.',f_FIELDNAME,' = b.appendixids
                                          WHERE a.MetaDataId = b.docid and a.MetaDataId > ',preDocId,' ;');
                        prepare stmt from @u_v_sql;
                        EXECUTE stmt;
                        deallocate prepare stmt;
                  end if;

                  SET @log_sql = build_migration_log_sql('update_data_ralation_appendix', preTableId, now());
                  prepare stmt from @log_sql;
                  EXECUTE stmt;
                deallocate prepare stmt;
              end if;
    END IF ;

		FETCH cur INTO
      f_DBFIELDINFOID,
      f_TABLENAME,
      f_TABLEID,
      f_FIELDNAME,
      f_ANOTHERNAME,
      f_FIELDTYPE,
      f_DBTYPE,
      f_DBLENGTH,
      f_DEFAULTVALUE,
      f_ENMVALUE,
      f_NOTNULL,
      f_CLASSID,
      f_DBSCALE,
      f_CRUSER,
      f_CRTIME,
      f_VALIDATOR,
      f_RADORCHK,
      f_NOTEDIT,
      f_HIDDENFIELD
      ;
		
	END WHILE;

	CLOSE cur;
	select CONCAT('update_data_relation_appendix_2 执行完成') info;

  END $$
DELIMITER ;