140.移动节点处理的通用存储过程

╰+攻爆jí腚メ 2024-02-19 19:09 23阅读 0赞

CREATE PROC p_Move_CopyCode
    @TableName  sysname,        --调整编码规则的表名
    @FieldName  sysname,        --编码字段名
    @CodeRule   varchar(50),    --以逗号分隔的编码规则，每层编码的长度，比如1,2,3,表示有三层编码，第一层长度为1，第二层长度为2，第三层长度为3
    @Code      varchar(50),     --要复制或者移动的节点编码
    @ParentCode varchar(50),    --移动到该编码的节点下
    @IsCopy    bit=0            --0为移动处理,否则为复制处理
    AS
    --参数检查
    IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
    BEGIN
    	RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
    	RETURN
    END
    IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
    BEGIN
    	RAISERROR(N'列名"%s"在用户表"%s"中无效',1,16,@FieldName,@TableName)
    	RETURN	
    END
    IF ISNULL(@CodeRule,'')='' 
    BEGIN
    	RAISERROR(N'必须编码规则字符串',1,16)
    	RETURN	
    END
    IF PATINDEX(N'%[^0-9^,]%',@CodeRule)>0
    BEGIN
    	RAISERROR(N'编码规则字符串"%s"中只能包含数字和逗号(,)',1,16,@CodeRule)
    	RETURN	
    END
    IF ISNULL(@Code,'')='' RETURN
    		
    
    --生成编码规则修改字符串
    DECLARE @CodeLen int,@CodeLens varchar(10),@Pos varchar(10),
    	@Old_CodeRule varchar(50),@New_CodeRule varchar(50),
    	@s nvarchar(4000),
    	@Code1 varchar(100),@Code2 varchar(100)
    
    IF ISNULL(@ParentCode,'')='' 
    	SELECT @ParentCode=N'',
    		@New_CodeRule=@CodeRule
    SET @CodeLens=0
    WHILE CHARINDEX(N',',@CodeRule)>0
    BEGIN
    	SET @CodeLen=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule)-1)
    	IF @CodeLens+@CodeLen=LEN(@Code)
    	BEGIN
    		SELECT @Old_CodeRule=@CodeRule,
    			@Pos=@CodeLens
    		IF @New_CodeRule>'' GOTO lb_CalcCodeLens
    	END
    	SELECT @CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule),N''),
    		@CodeLens=@CodeLens+@CodeLen
    	IF @CodeLens=LEN(@ParentCode)
    	BEGIN
    		SET @New_CodeRule=@CodeRule
    		IF @Old_CodeRule>'' GOTO lb_CalcCodeLens
    	END
    END
    IF @Old_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@Code)
    	SELECT @Old_CodeRule=@CodeRule,
    			@Pos=@CodeLens
    IF @New_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@ParentCode)
    BEGIN
    	RAISERROR(N'移动编码"%s"到编码"%s"下导致编码长度溢出编码规则允许的长度',1,16,@Code,@ParentCode)
    	RETURN
    END
    
    lb_CalcCodeLens:
    SET @s=N'SET @CodeLens=@CodeLens+'+REPLACE(@CodeRule,N',',N'+')
    EXEC sp_executesql @s,N'@CodeLens int OUTPUT',@CodeLens OUTPUT
    
    IF @Old_CodeRule IS NULL
    BEGIN
    	RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@Code)
    	RETURN
    END
    IF @New_CodeRule IS NULL
    BEGIN
    	RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@ParentCode)
    	RETURN
    END
    
    DECLARE @Parent_Chk nvarchar(4000),@Delete_old nvarchar(4000),@where nvarchar(4000)
    SELECT @TableName=QUOTENAME(@TableName),
    	@FieldName=QUOTENAME(@FieldName),
    	@s=CASE 
    		WHEN @Old_CodeRule=@New_CodeRule THEN N'New_no'
    		ELSE dbo.f_ChangeCodeRule(@Old_CodeRule,@New_CodeRule,'',0,N'New_No')
    	END,
    	@Code1=QUOTENAME(@Code+N'%',N''''),
    	@Code2=QUOTENAME(@Code,N''''),
    	@ParentCode=QUOTENAME(@ParentCode,N''''),
    	@Parent_Chk=CASE 
    		WHEN @ParentCode=N'''''' THEN N''
    		ELSE 'IF NOT EXISTS(SELECT * FROM '+@TableName
    			+N' WHERE '+@FieldName+N'='+@ParentCode+N')
    	BEGIN
    		RAISERROR(N''编码"%s"不存在'',1,16,'+@ParentCode+N')
    		ROLLBACK TRAN
    		RETURN
    	END' END,
    	@Delete_old=CASE 
    		WHEN @IsCopy=0 
    		THEN N'DELETE a FROM '+@TableName
    			+N' a,# b WHERE a.'+@FieldName+N'=b.Old_No'
    		ELSE N'' END,
    	@where=CASE
    		WHEN @IsCopy=0 THEN N'AND Old_No<>a.Old_No'
    		ELSE N'' END
    	
    --检查并完成删除处理
    EXEC(N'BEGIN TRAN
    '+@Parent_Chk+N'
    --将处理后的编码与处理前的编码保存到临时表
    SELECT Old_No,New_No='+@ParentCode+N'+'+@s+N'
    INTO # FROM(
    	SELECT Old_No='+@FieldName+N',
    		New_No=STUFF('+@FieldName+N',1,'+@Pos+N','''')
    	FROM '+@TableName+N' WITH(XLOCK,TABLOCK)
    	WHERE '+@FieldName+N' LIKE '+@Code1+N')a
    
    --编码重复检测
    SELECT Err=N''超过编码规则能处理的长度'',* INTO #1 FROM # WHERE LEN(New_No)>'+@CodeLens+N'
    UNION ALL
    SELECT Err=N''转换后编码重复'',* FROM # a
    WHERE EXISTS(
    	SELECT * FROM # WHERE New_No=a.New_No AND Old_No<>a.Old_No)
    UNION ALL
    SELECT Err=N''转换后与表中现有的编码重复'',* FROM # a
    WHERE EXISTS(
    	SELECT * FROM '+@TableName+N' 
    	WHERE '+@FieldName+N'=a.New_No '+@where+N')
    IF @@ROWCOUNT>0
    	SELECT * FROM #1 ORDER BY Err,New_No
    ELSE
    BEGIN
    	--移动编码
    	SELECT a.* INTO #2 
    	FROM '+@TableName+N' a,# b
    	WHERE a.'+@FieldName+N'=b.Old_No
    	UPDATE a SET '+@FieldName+N'=b.New_No
    	FROM #2 a,# b
    	WHERE a.'+@FieldName+N'=b.Old_No
    '+@Delete_old+N' --如果是移动,先进行删除处理
    	INSERT '+@TableName+N' SELECT * FROM #2
    END
    COMMIT TRAN')