SqlServer表内带ParentId标识父集项级联修改子集项
先看下数据库表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
诚心的希望,,有高手能指教下...
觉得自己真的写得不好....崩完了..
源码下载
