136.生成编码规则调整处理（T-SQL语句【函数】）

痛定思痛。 2024-02-19 19:09 75阅读 0赞

CREATE FUNCTION dbo.f_ChangeCodeRule(
    @Old_CodeRule  varchar(50),  --以逗号分隔的旧的编码规则,每层编码的长度,比如1,2,3,表示有三层编码,第一层长度为1,第二层长度为2,第三层长度为3
    @New_CodeRule varchar(50),   --以逗号分隔的旧的编码规则,如果某个层次的编码长度为0,表示删除该层编码
    @CharFill       char(1),     --扩充编码时,填充的字符
    @Position       int,         --为0,从编码的最前面开始压缩或者填充,为-1或者大于旧编码的长度,从最后一位开始处理,为其他值,从指定的位置后开始处理
    @FieldName     sysname       --编码字段名
    )RETURNS nvarchar(4000)
    AS
    BEGIN
    	IF ISNULL(@CharFill,'')='' SET @CharFill=N'0'
    	DECLARE @old_Code TABLE(ID int IDENTITY,CodeLen int,CodeLens int,Code nvarchar(200))
    	DECLARE @new_Code TABLE(ID int IDENTITY,CodeLen int)
    	--插分旧编码规则到表
    	DECLARE @CodeLen varchar(10),@CodeLens varchar(10)
    	SET @CodeLens=1
    	WHILE CHARINDEX(N',',@Old_CodeRule)>0
    	BEGIN
    		SELECT @CodeLen=LEFT(@Old_CodeRule,CHARINDEX(N',',@Old_CodeRule)-1),
    			@Old_CodeRule=STUFF(@Old_CodeRule,1,CHARINDEX(N',',@Old_CodeRule),N'')
    		INSERT @old_Code VALUES(@CodeLen,@CodeLens,N'SUBSTRING('+@FieldName+N','+@CodeLens+N','+@CodeLen+N')')
    		SET @CodeLens=@CodeLens+CAST(@CodeLen as int)
    	END
    	INSERT @old_Code VALUES(@Old_CodeRule,@CodeLens,N'SUBSTRING('+@FieldName+N','+@CodeLens+N','+@Old_CodeRule+N')')
    
    	--插分新编码规则到表
    	WHILE CHARINDEX(N',',@New_CodeRule)>0
    	BEGIN
    		INSERT @new_Code VALUES(LEFT(@New_CodeRule,CHARINDEX(N',',@New_CodeRule)-1))
    		SET @New_CodeRule=STUFF(@New_CodeRule,1,CHARINDEX(N',',@New_CodeRule),N'')
    	END
    	INSERT @new_Code VALUES(@New_CodeRule)
    
    	--生成编号规则修改处理语句
    	DECLARE @sql nvarchar(4000)
    	SET @sql=''
    	SELECT @sql=@sql
    		+CASE 
    			WHEN n.CodeLen=0 THEN ''                   --新编码长度为0,表示去掉这段编码
    			ELSE N'+CASE WHEN LEN('+@FieldName
    				+N')<'+CAST(o.CodeLens as varchar)
    				+N' THEN '''' ELSE '+CASE
    					WHEN n.CodeLen=o.CodeLen THEN N'+'+o.Code  --新旧编码长度相同时不需要处理
    					WHEN n.CodeLen>o.CodeLen THEN CASE         --扩充编码长度的处理,根据@Position和旧编码长度决定编码的填充位置
    						WHEN @Position=-1 OR @Position>=o.CodeLen
    						THEN N'+'+o.Code
    							+N'+'+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''')
    						ELSE N'+STUFF('+o.Code
    							+N','+CAST(@Position+1 as varchar)
    							+N',0,'+QUOTENAME(REPLICATE(@CharFill,n.CodeLen-o.CodeLen),N'''')
    							+N')'
    						END
    					ELSE CASE                                  --收缩编码长度的处理,根据@Position和新编码长度决定编码的截取位置
    						WHEN @Position=-1 OR @Position>n.CodeLen
    						THEN '+LEFT('+o.Code+N','+CAST(n.CodeLen as varchar)+N')'
    						ELSE N'+STUFF('+o.Code
    							+N','+CAST(@Position+1 as varchar)
    							+N','+CAST(o.CodeLen-n.CodeLen as varchar)
    							+N','''')'
    						END
    					END
    				+N' END'
    			END
    	FROM @old_Code o,@new_Code n
    	WHERE o.ID=n.ID
    	RETURN(STUFF(@sql,1,1,N''))
    END