SqlServer表内带ParentId标识父集项级联修改子集项

最新推荐文章于 2022-05-15 19:33:09 发布

weixin_30576859

最新推荐文章于 2022-05-15 19:33:09 发布

阅读量286

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/monomania/archive/2011/07/11/monomania.html

版权

先看下数据库表BookType的数据:

需求:

如果用户在分类上面进行了修改，

比如修改了根节点文学，把根节点文学修改为“文学2”，

那我就得在数据库用SQL语句进行更新，

我更新第一条的数据了，就是Update文学为“文学2”，

但是现在问题是，怎么样把他所属的下面的分类的那些包括文学的都改为“文学2”呢？？

比如文学->中国文学修改为文学2->中国文学这样的。SQL语句怎么Update他本身所属的分类下面的对应值。

执行相应的操作后:

BookType表数据(libCode表)改变为:

我需要的只BookTypeId和BookTypeName两个参数,,就完成这样的修改.

下面再发下BookType表的结构:

CREATE TABLE BOOKTYPE

1 USE [ BANKTYPE ]
2 GO
3 /* ***** 对象: Table [dbo].[BookType] 脚本日期: 07/11/2011 13:24:46 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 SET ANSI_PADDING ON
9 GO
10 CREATE TABLE [ dbo ] . [ BookType ] (
11 [ BookTypeID ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
12 [ BookTypeName ] [ varchar ] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL ,
13 [ ParentID ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
14 [ LibCode ] [ nvarchar ] ( 255 ) COLLATE Chinese_PRC_CI_AS NULL ,
15 [ ValueCode ] [ varchar ] ( 400 ) COLLATE Chinese_PRC_CI_AS NULL ,
16 [ TypeFlag ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
17 CONSTRAINT [ PK_BookType ] PRIMARY KEY CLUSTERED
18 (
19 [ BookTypeID ] ASC
20 ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
21 ) ON [ PRIMARY ]
22
23 GO
24 SET ANSI_PADDING OFF

-----------------------------------------------------------------------------------------------------------------------------------------------

这里发我下我个人的实现方法,我用了一个存储过程和两个FUNCTION是用来处理字符串分割的

FUNCTION:

Get_StrArrayLength

1 USE [ bankDB ]
2 GO
3 /* ***** 对象: UserDefinedFunction [dbo].[Get_StrArrayLength] 脚本日期: 07/11/2011 15:07:21 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9 CREATE function [ dbo ] . [ Get_StrArrayLength ]
10 (
11 @str varchar ( max ), -- 要分割的字符串
12 @split varchar ( 10 ) -- 分隔符号
13 )
14 returns int
15 as
16 begin
17 declare @location int
18 declare @start int
19 declare @length int
20
21 set @str = ltrim ( rtrim ( @str ))
22 set @location = charindex ( @split , @str )
23 set @length = 1
24 while @location <> 0
25 begin
26 set @start = @location + 1
27 set @location = charindex ( @split , @str , @start )
28 set @length = @length + 1
29 end
30 return @length
31 end

Get_StrArrayStrOfIndex

1 USE [ bankDB ]
2 GO
3 /* ***** 对象: UserDefinedFunction [dbo].[Get_StrArrayStrOfIndex] 脚本日期: 07/11/2011 15:07:26 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 CREATE function [ dbo ] . [ Get_StrArrayStrOfIndex ]
9 (
10 @str varchar ( 1024 ), -- 要分割的字符串
11 @split varchar ( 10 ), -- 分隔符号
12 @index int -- 取第几个元素
13 )
14 returns varchar ( 1024 )
15 as
16 begin
17 declare @location int
18 declare @start int
19 declare @next int
20 declare @seed int
21
22 set @str = ltrim ( rtrim ( @str ))
23 set @start = 1
24 set @next = 1
25 set @seed = len ( @split )
26
27 set @location = charindex ( @split , @str )
28 while @location <> 0 and @index > @next
29 begin
30 set @start = @location + @seed
31 set @location = charindex ( @split , @str , @start )
32 set @next = @next + 1
33 end
34 if @location = 0 select @location = len ( @str ) + 1
35 -- 这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
36
37 return substring ( @str , @start , @location - @start )
38 end

PROC存储过程:

　　这里我把遍历的子节点深度写到第10层....所以代码相当多.

　　其实并非自己想这么写,只是因为自己用了游标做遍历,如何使用递归法来做,就无法关闭游标,或找不到游标,会报错.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_BookType_Modify]
	@BookTypeId varchar(50),
	@BookTypeName varchar(100)
 AS 
	DECLARE @Templibcode varchar(255),@Related int,@ChildName varchar(100),@ParentIsExistsOfName int --定义变量

	Select @ParentIsExistsOfName=len(ParentId)  from BookType where BookTypeId = @BookTypeId and TypeFlag = '1'  
		if(len(@ParentIsExistsOfName) <> 0)
		begin
				UPDATE BookType set BookTypeName = @BookTypeName ,libcode = '' where BookTypeId = @BookTypeId and TypeFlag = '1'  
				declare @tempId varchar(50), @ind int, @cout int, @valueCode varchar(255)
				declare @enName varchar(100)
				declare @nowName varchar(100)
				
				set @ind = 1
				select @valueCode = valuecode from booktype where booktypeid = @BookTypeId and TypeFlag = '1'  

				set @cout=dbo.Get_StrArrayLength(@valueCode,',')
				while(@ind <= @cout)
					begin
						select @tempId = dbo.Get_StrArrayStrOfIndex(@valueCode,',',@ind)
						Select @enName=BookTypeName from bookType where bookTypeId = @tempId and TypeFlag = '1'  
						select @nowName=libcode from booktype where bookTypeID = @BookTypeId and TypeFlag = '1'  
						if len(@nowName) <> 0
										begin
										update Booktype set libcode = @nowName + '->'+@enName where booktypeid = @BookTypeId and TypeFlag = '1'  
										end
						else
										begin
										update Booktype set libcode = @enName  where booktypeid = @BookTypeId 	and TypeFlag = '1'  
										end
									set @ind =@ind+1

					end
			
		end
		else
			begin
				UPDATE BookType set BookTypeName = @BookTypeName ,libcode = @BookTypeName where BookTypeId = @BookTypeId and TypeFlag = '1'  
			end
	SELECT @Related = COUNT(ParentId) from BookType where parentId=@BookTypeId and TypeFlag = '1'  

	if @Related <> 0
begin
		declare customerCursor cursor for select booktypeid from booktype where parentid = @BookTypeId and TypeFlag = '1'  
			open customerCursor
			declare @typeid varchar(max)
				fetch next from customerCursor into @typeid	
					while(@@fetch_status=0)
					begin
						declare @tempValueCode varchar(255),@tempchilid varchar(50),@count int ,@endName varchar(100)
						declare @i int 
						set @i = 1
							update Booktype set libcode = '' where booktypeid = @typeid  and TypeFlag = '1'  
						select @tempValuecode = valuecode from booktype where booktypeid = @typeid and TypeFlag = '1'  
						set @count=dbo.Get_StrArrayLength(@tempValuecode,',')
							while(@i <= @count)
								begin 
									
									Select @endName=libcode from bookType where bookTypeId = @typeid and TypeFlag = '1'  
									select @tempchilid = dbo.Get_StrArrayStrOfIndex(@tempValuecode,',',@i)
									select @ChildName = bookTypeName from bookType where BookTypeId = @tempchilID and TypeFlag = '1'  
									print convert(varchar(5),len(@endName))
									if len(@endName) <> 0
										begin
										update Booktype set libcode = @endName +'->'+@ChildName where booktypeid = @typeid  and TypeFlag = '1'  
										end
									else
										begin
										update Booktype set libcode = @ChildName where booktypeid = @typeid and TypeFlag = '1'  
										end
									set @i =@i+1
								end




						

								Declare @chilExists int
								Select @chilExists=count(parentId) from BookType where parentId = @typeid and TypeFlag = '1'  
								if(@chilExists <> 0)
									begin
									--不能使用递归,因为无法关闭游标,第三个叶子
									declare childCursor cursor for select booktypeid from booktype where parentid = @typeid and TypeFlag = '1'  
									open childCursor
										declare @tid nvarchar(max)
										fetch next from childCursor into @tid	
										while(@@fetch_status=0)
										begin
											declare @VCode varchar(255),@tem varchar(50),@ct int ,@en varchar(100),@ChName varchar(100)
											Select @VCode = valueCode from BookType where BookTypeId = @tid and TypeFlag = '1'  
											declare @k int 
											set @k = 1
											update Booktype set libcode = '' where booktypeid = @tid  and TypeFlag = '1'  
											set @ct=dbo.Get_StrArrayLength(@VCode,',')
											while(@k <= @ct)
												begin 
									
												Select @en=libcode from bookType where bookTypeId = @tid and TypeFlag = '1'  
												select @tem = dbo.Get_StrArrayStrOfIndex(@VCode,',',@k) 
												select @ChName = bookTypeName from bookType where BookTypeId = @tem and TypeFlag = '1'  
												if len(@en) <> 0
													begin
														update Booktype set libcode = @en +'->'+@ChName where booktypeid = @tid and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName where booktypeid = @tid and TypeFlag = '1'  
													end
												set @k =@k+1
												end
												--第四层

								Declare @chilExists4 int
								Select @chilExists4=count(parentId) from BookType where parentId = @tid and TypeFlag = '1'  
								if(@chilExists4 <> 0)
									begin
									declare childCursor4 cursor for select booktypeid from booktype where parentid = @tid and TypeFlag = '1'  
									open childCursor4
										declare @tid4 nvarchar(max)
										fetch next from childCursor4 into @tid4	
										while(@@fetch_status=0)
										begin
											declare @VCode4 varchar(255),@tem4 varchar(50),@ct4 int ,@en4 varchar(100),@ChName4 varchar(100)
											Select @VCode4 = valueCode from BookType where BookTypeId = @tid4 and TypeFlag = '1'  
											declare @k4 int 
											set @k4 = 1
											update Booktype set libcode = '' where booktypeid = @tid4  and TypeFlag = '1'  
											set @ct4=dbo.Get_StrArrayLength(@VCode4,',')
											while(@k4 <= @ct4)
												begin 
									
												Select @en4=libcode from bookType where bookTypeId = @tid4 and TypeFlag = '1'  
												select @tem4 = dbo.Get_StrArrayStrOfIndex(@VCode4,',',@k4) 
												select @ChName4 = bookTypeName from bookType where BookTypeId = @tem4 and TypeFlag = '1'  
												if len(@en4) <> 0
													begin
														update Booktype set libcode = @en4 +'->'+@ChName4 where booktypeid = @tid4 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName4 where booktypeid = @tid4 and TypeFlag = '1'  
													end
												set @k4 =@k4+1
												end 
												--第五层触发
								Declare @chilExists5 int
								Select @chilExists5=count(parentId) from BookType where parentId = @tid4 and TypeFlag = '1'  
								if(@chilExists5 <> 0)
									begin
									declare childCursor5 cursor for select booktypeid from booktype where parentid = @tid4 and TypeFlag = '1'  
									open childCursor5
										declare @tid5 nvarchar(max)
										fetch next from childCursor5 into @tid5	
										while(@@fetch_status=0)
										begin
											declare @VCode5 varchar(255),@tem5 varchar(50),@ct5 int ,@en5 varchar(100),@ChName5 varchar(100)
											Select @VCode5 = valueCode from BookType where BookTypeId = @tid5 and TypeFlag = '1'  
											declare @k5 int 
											set @k5 = 1
											update Booktype set libcode = '' where booktypeid = @tid5  and TypeFlag = '1'  
											set @ct5=dbo.Get_StrArrayLength(@VCode5,',')
											while(@k5 <= @ct5)
												begin 
									
												Select @en5=libcode from bookType where bookTypeId = @tid5 and TypeFlag = '1'  
												select @tem5 = dbo.Get_StrArrayStrOfIndex(@VCode5,',',@k5) 
												select @ChName5 = bookTypeName from bookType where BookTypeId = @tem5 and TypeFlag = '1'  
												if len(@en5) <> 0
													begin
														update Booktype set libcode = @en5 +'->'+@ChName5 where booktypeid = @tid5 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName5 where booktypeid = @tid5 and TypeFlag = '1'  
													end
												set @k5 =@k5+1
												end 
												--第6层触发
																					Declare @chilExists6 int
								Select @chilExists6=count(parentId) from BookType where parentId = @tid5 and TypeFlag = '1'  
								if(@chilExists6 <> 0)
									begin
									declare childCursor6 cursor for select booktypeid from booktype where parentid = @tid5 and TypeFlag = '1'  
									open childCursor6
										declare @tid6 nvarchar(max)
										fetch next from childCursor6 into @tid6	
										while(@@fetch_status=0)
										begin
											declare @VCode6 varchar(255),@tem6 varchar(50),@ct6 int ,@en6 varchar(100),@ChName6 varchar(100)
											Select @VCode6 = valueCode from BookType where BookTypeId = @tid6 and TypeFlag = '1'  
											declare @k6 int 
											set @k6 = 1
											update Booktype set libcode = '' where booktypeid = @tid6  and TypeFlag = '1'  
											set @ct6=dbo.Get_StrArrayLength(@VCode6,',')
											while(@k6 <= @ct6)
												begin 
									
												Select @en6=libcode from bookType where bookTypeId = @tid6 and TypeFlag = '1'  
												select @tem6 = dbo.Get_StrArrayStrOfIndex(@VCode6,',',@k6) 
												select @ChName6 = bookTypeName from bookType where BookTypeId = @tem6 and TypeFlag = '1'  
												if len(@en6) <> 0
													begin
														update Booktype set libcode = @en6 +'->'+@ChName6 where booktypeid = @tid6 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName6 where booktypeid = @tid6 and TypeFlag = '1'  
													end
												set @k6 =@k6+1
												end 
												--第7层触发
																					Declare @chilExists7 int
								Select @chilExists7=count(parentId) from BookType where parentId = @tid6 and TypeFlag = '1'  
								if(@chilExists7 <> 0)
									begin
									declare childCursor7 cursor for select booktypeid from booktype where parentid = @tid6 and TypeFlag = '1'  
									open childCursor7
										declare @tid7 nvarchar(max)
										fetch next from childCursor7 into @tid7	
										while(@@fetch_status=0)
										begin
											declare @VCode7 varchar(255),@tem7 varchar(50),@ct7 int ,@en7 varchar(100),@ChName7 varchar(100)
											Select @VCode7 = valueCode from BookType where BookTypeId = @tid7 and TypeFlag = '1'  
											declare @k7 int 
											set @k7 = 1
											update Booktype set libcode = '' where booktypeid = @tid7  and TypeFlag = '1'  
											set @ct7=dbo.Get_StrArrayLength(@VCode7,',')
											while(@k7 <= @ct7)
												begin 
									
												Select @en7=libcode from bookType where bookTypeId = @tid7 and TypeFlag = '1'  
												select @tem7 = dbo.Get_StrArrayStrOfIndex(@VCode7,',',@k7) 
												select @ChName7 = bookTypeName from bookType where BookTypeId = @tem7 and TypeFlag = '1'  
												if len(@en7) <> 0
													begin
														update Booktype set libcode = @en7 +'->'+@ChName7 where booktypeid = @tid7 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName7 where booktypeid = @tid7 and TypeFlag = '1'  
													end
												set @k7 =@k7+1
												end 
												--第8层触发
																					Declare @chilExists8 int
								Select @chilExists8=count(parentId) from BookType where parentId = @tid7 and TypeFlag = '1'  
								if(@chilExists8 <> 0)
									begin
									declare childCursor8 cursor for select booktypeid from booktype where parentid = @tid7 and TypeFlag = '1'  
									open childCursor8
										declare @tid8 nvarchar(max)
										fetch next from childCursor8 into @tid8	
										while(@@fetch_status=0)
										begin
											declare @VCode8 varchar(255),@tem8 varchar(50),@ct8 int ,@en8 varchar(100),@ChName8 varchar(100)
											Select @VCode8 = valueCode from BookType where BookTypeId = @tid8 and TypeFlag = '1'  
											declare @k8 int 
											set @k8 = 1
											update Booktype set libcode = '' where booktypeid = @tid8  and TypeFlag = '1'  
											set @ct8=dbo.Get_StrArrayLength(@VCode8,',')
											while(@k8 <= @ct8)
												begin 
									
												Select @en8=libcode from bookType where bookTypeId = @tid8 and TypeFlag = '1'  
												select @tem8 = dbo.Get_StrArrayStrOfIndex(@VCode8,',',@k8) 
												select @ChName8 = bookTypeName from bookType where BookTypeId = @tem8 and TypeFlag = '1'  
												if len(@en8) <> 0
													begin
														update Booktype set libcode = @en8 +'->'+@ChName8 where booktypeid = @tid8 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName8 where booktypeid = @tid8 and TypeFlag = '1'  
													end
												set @k8 =@k8+1
												end 
------												--第9层触发
																					Declare @chilExists9 int
								Select @chilExists9=count(parentId) from BookType where parentId = @tid8 and TypeFlag = '1'  
								if(@chilExists9 <> 0)
									begin
									declare childCursor9 cursor for select booktypeid from booktype where parentid = @tid8 and TypeFlag = '1'  
									open childCursor9
										declare @tid9 nvarchar(max)
										fetch next from childCursor9 into @tid9	
										while(@@fetch_status=0)
										begin
											declare @VCode9 varchar(255),@tem9 varchar(50),@ct9 int ,@en9 varchar(100),@ChName9 varchar(100)
											Select @VCode9 = valueCode from BookType where BookTypeId = @tid9 and TypeFlag = '1'  
											declare @k9 int 
											set @k9 = 1
											update Booktype set libcode = '' where booktypeid = @tid9  and TypeFlag = '1'  
											set @ct9=dbo.Get_StrArrayLength(@VCode9,',')
											while(@k9 <= @ct9)
												begin 
									
												Select @en9=libcode from bookType where bookTypeId = @tid9 and TypeFlag = '1'  
												select @tem9 = dbo.Get_StrArrayStrOfIndex(@VCode9,',',@k9) 
												select @ChName9 = bookTypeName from bookType where BookTypeId = @tem9 and TypeFlag = '1'  
												if len(@en9) <> 0
													begin
														update Booktype set libcode = @en9 +'->'+@ChName9 where booktypeid = @tid9 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName9 where booktypeid = @tid9 and TypeFlag = '1'  
													end
												set @k9 =@k9+1
												end 
--												--第10层触发
																					Declare @chilExists10 int
								Select @chilExists10=count(parentId) from BookType where parentId = @tid9 and TypeFlag = '1'  
								if(@chilExists10 <> 0)
									begin
									declare childCursor10 cursor for select booktypeid from booktype where parentid = @tid9 and TypeFlag = '1'  
									open childCursor10
										declare @tid10 nvarchar(max)
										fetch next from childCursor10 into @tid10	
										while(@@fetch_status=0)
										begin
											declare @VCode10 varchar(255),@tem10 varchar(50),@ct10 int ,@en10 varchar(100),@ChName10 varchar(100)
											Select @VCode10 = valueCode from BookType where BookTypeId = @tid10 and TypeFlag = '1'  
											declare @k10 int 
											set @k10 = 1
											update Booktype set libcode = '' where booktypeid = @tid10  and TypeFlag = '1'  
											set @ct10=dbo.Get_StrArrayLength(@VCode10,',')
											while(@k10 <= @ct10)
												begin 
									
												Select @en10=libcode from bookType where bookTypeId = @tid10 and TypeFlag = '1'  
												select @tem10 = dbo.Get_StrArrayStrOfIndex(@VCode10,',',@k10) 
												select @ChName10 = bookTypeName from bookType where BookTypeId = @tem10 and TypeFlag = '1'  
												if len(@en10) <> 0
													begin
														update Booktype set libcode = @en10 +'->'+@ChName10 where booktypeid = @tid10 and TypeFlag = '1'  
													end
												else
													begin
														update Booktype set libcode = @ChName10 where booktypeid = @tid10 and TypeFlag = '1'  
													end
												set @k10 =@k10+1
												end 
												--第11层触发
													
												--end 11
											fetch next from childCursor10 into @tid10 end
											close childCursor10
											deallocate childCursor10
											
										end
												--end 10
											fetch next from childCursor9 into @tid9 end
											close childCursor9
											deallocate childCursor9
											
										end
												--end 9
											fetch next from childCursor8 into @tid8 end
											close childCursor8
											deallocate childCursor8
											
										end
												--end 8
											fetch next from childCursor7 into @tid7 end
											close childCursor7
											deallocate childCursor7
											
										end
												--end 7
											fetch next from childCursor6 into @tid6 end
											close childCursor6
											deallocate childCursor6
											
										end
												--end 6
											fetch next from childCursor5 into @tid5 end
											close childCursor5
											deallocate childCursor5
											
										end
												--end 5
											fetch next from childCursor4 into @tid4 end
											close childCursor4
											deallocate childCursor4
											
										end
												--第四层end

										
										
										fetch next from childCursor into @tid end
										close childCursor
										deallocate childCursor
										
									end
												--第三层end
						fetch next from customerCursor into @typeid
					end
		close customerCursor
		deallocate customerCursor
end
--第二层end

诚心的希望,,有高手能指教下...

觉得自己真的写得不好....崩完了..

源码下载

转载于:https://www.cnblogs.com/monomania/archive/2011/07/11/monomania.html

weixin_30576859

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SqlServer表内带ParentId标识父集项级联修改子集项

先看下数据库表BookType的数据:　　需求:如果用户在分类上面进行了修改，比如修改了根节点文学，把根节点文学修改为“文学2”，那我就得在数据库用SQL语句进行更新，我更新第一条的数据了，就是Update文学为“文学2”，但是现在问题是，怎么样把他所属的下面的分类的那些包括文学的都改为“文学2”呢？？比如文学->中国文学修改为文学2->中国文学这样的...
复制链接

扫一扫