批量同步实体上的扩展字段绑定的值集名称到多语表的组合名称中IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[P_SyncFieldCombineName]'))DROPPROC[dbo].[P_SyncFieldCombineName];GO--================================================--批量同步实体上的拓展字段绑定的值集名称到多语表的组合名称中，需要传入实体的fullname和扩展字段名称‘--该存储过程依赖两个函数fn_ReplacePosStr,f_getdefinevalue--================================================CREATEPROC[dbo].[P_SyncFieldCombineName](@FullNameVARCHAR(100),@DescFieldNameVARCHAR(80))ASDECLARE@ContextTypeVARCHAR(10);DECLARE@TableNameVARCHAR(100);SET@TableName=(SELECTDefaultTableNameFROMUBF_MD_ClassWHEREFullName=@FullName);DECLARE@IsPublicSegBIT;DECLARE@IsPrivateSegBIT;DECLARE@NumberINT;DECLARE@DNumberINT;SET@IsPublicSeg=0;SET@IsPrivateSeg=0;--先判断公共扩展字段还是全局段，全局段序号需加50，如果都不是则跳出IFCHARINDEX('Private',@DescFieldName)>0BEGINSET@IsPrivateSeg=1;SET@ContextType='Global';SET@DNumber=SUBSTRING(@DescFieldName,PATINDEX('%[0-9]%',@DescFieldName),LEN(@DescFieldName)-PATINDEX('%[0-9]%',@DescFieldName)+1);SET@Number=50+@DNumber;END;ELSEIFCHARINDEX('Pub',@DescFieldName)>0BEGINSET@IsPublicSeg=1;SET@ContextType='Public';SET@Number=SUBSTRING(@DescFieldName,PATINDEX('%[0-9]%',@DescFieldName),LEN(@DescFieldName)-PATINDEX('%[0-9]%',@DescFieldName)+1);SET@DNumber=@Number;END;ELSEBEGINRETURN;END;IF@Number<=0BEGINRETURN;END;DECLARE@prexVARCHAR(30);SET@prex=SUBSTRING(@DescFieldName,1,CHARINDEX('_',@DescFieldName)-1);--目前此脚本仅支持无档案类型和自定义类型的拓展字段处理DECLARE@ValidateEnumINT;SET@ValidateEnum=(SELECTE.ValidateTypeFROMUBF_MD_ClassAINNERJOINBase_DescFlexFieldDefBONA.Local_ID=B.EntityTypeINNERJOINBase_DescFlexContextCONB.ID=C.DescFlexFieldDefINNERJOINBase_DescFlexSegmentDONC.ID=D.DescFlexContextINNERJOINbase_valuesetdefEOND.ValueSetDef=E.IDWHEREC.ContextValue=@ContextTypeANDD.Number=@DNumberANDA.FullName=@FullName);DECLARE@sqlNVARCHAR(1000)='';IF@ValidateEnum=2BEGIN--无档案SET@sql='UPDATEASET'+@prex+'_CombineName=dbo.[fn_ReplacePosStr](B.'+@DescFieldName+',A.'+@prex+'_CombineName,'+CAST(@NumberASCHAR(3))+')';SET@sql+='FROMdbo.'+@TableName+'_TrlA';SET@sql+='INNERJOIN'+@TableName+'BONA.ID=B.ID';PRINT@sql;EXECsp_executesql@sql;END;ELSEIF@ValidateEnum=3BEGIN--自定义类型SET@sql='UPDATEAsetA.'+@prex+'_CombineName=dbo.[fn_ReplacePosStr](C.Name,A.'+@prex+'_CombineName,'+CAST(@NumberASVARCHAR(10))+')';SET@sql+='FROMdbo.'+@TableName+'_TrlA';SET@sql+='INNERJOIN'+@TableName+'BONA.ID=B.ID';SET@sql+='innerjoindbo.[f_getdefinevalue]('''+@FullName+''','+CAST(@DNumberASVARCHAR(10))+','''+@ContextType+''')asConB.'+@DescFieldName+'=C.CodeandA.SysMLFlag=C.SysMLFlag';PRINT@sql;EXECsp_executesql@sql;END;GOIFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[f_getdefinevalue]'))DROPFUNCTION[dbo].[f_getdefinevalue];GO--================================================--根据实体全名和上下文名称，查询某一个序号的扩展字段的值集值--================================================CREATEFUNCTION[dbo].[f_getdefinevalue](@FullNameVARCHAR(50),@NumberINT,@ContextTypeVARCHAR(10))RETURNS@resultTABLE(CodeVARCHAR(100),NameVARCHAR(500),SysMLFlagVARCHAR(10))ASBEGIN--FillthetablevariablewiththerowsforyourresultsetINSERTINTO@resultSELECTE.Code,E1.Name,E1.SysMLFlagFROMUBF_MD_ClassAINNERJOINBase_DescFlexFieldDefBONA.Local_ID=B.EntityTypeINNERJOINBase_DescFlexContextCONB.ID=C.DescFlexFieldDefINNERJOINBase_DescFlexSegmentDONC.ID=D.DescFlexContextINNERJOINBase_DefineValueEOND.ValueSetDef=E.ValueSetDefINNERJOINBase_DefineValue_trlE1ONE.id=E1.idWHEREC.ContextValue=@ContextTypeANDD.Number=@NumberANDA.FullName=@FullName;RETURN;END;GOIFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[fn_ReplacePosStr]'))DROPFUNCTION[dbo].[fn_ReplacePosStr]GO--================================================--用于替换组合名称中某一序号的值，分割符为#@#--================================================CREATEFUNCTION[dbo].[fn_ReplacePosStr](@strVARCHAR(2000),@expVARCHAR(MAX),@posINT)RETURNSVARCHAR(MAX)ASBEGINIF@str=''OR@pos<1OR@pos>80OR@exp=''RETURN@exp;DECLARE@iINT;DECLARE@jINT;DECLARE@sINT;DECLARE@vVARCHAR(500);DECLARE@sepVARCHAR(500);SET@sep='#@#';SET@s=1;SET@j=0;SET@v='';WHILE@s<=LEN(@exp)+1BEGINSET@j=@j+1;SET@i=CHARINDEX(@sep,@exp,@s);IF@i<1BEGINSET@v=SUBSTRING(@exp,@s,LEN(@exp)-@s+1);BREAK;END;ELSEBEGINSET@v=SUBSTRING(@exp,@s,@i-@s);END;IF@j=@posBREAK;SET@s=@i+3;END;IF@j<@posRETURN@exp;IF@i>0RETURNISNULL(SUBSTRING(@exp,0,@s)+ISNULL(@str,'')+SUBSTRING(@exp,@i,LEN(@exp)-@i+1),@exp);RETURNISNULL(SUBSTRING(@exp,0,@s)+ISNULL(@str,''),@exp);END;GO
