--失掉编码累计
set@re=right(@idheader+cast(@idasvarchar),@idlen)
select@pid=pidfromtbwhereid=@id
while@@rowcount>0
select@re=right(@idheader+cast(@pidasvarchar),@idlen)+,+@re
,@pid=pidfromtbwhereid=@pid
return(@re)
end
go
--2.自界说函数--检测某个编码动身,是不是被轮回援用
createfunctionf_chkid(@idint)
returnsbit--轮回,前往1,不然前往0
as
begin
declare@rebit,@pidint
set@re=0
--检测
select@pid=pidfromtbwhereid=@id
while@@rowcount>0
begin
if@pid=@id
begin
set@re=1
gotolbErr
end
select@pid=pidfromtbwhereid=@pid
end
lbErr:
return(@re)
end
go
/*--数据复制
假如表中包括自界说字段,必要修正存储历程
存在嵌套不凌驾32层的成绩.
--*/
--3.复制指定结点下的子结点到另外一个结点下
createprocp_copy
@s_idint,--复制该项下的一切子项
@d_idint,--复制到此项下
@new_idint--新增添项的入手下手编号
as
declare@nidint,@oidint,@namevarchar(20)
selectid,nameinto#tempfromtbwherepid=@s_idandid<@new_id
whileexists(select1from#temp)
begin
select@oid=id,@name=namefrom#temp
insertintotbvalues(@d_id,@name)
set@nid=@@identity
execp_copy@oid,@nid,@new_id
deletefrom#tempwhereid=@oid
end
go
--4.批量复制的存储历程--复制指定结点及其上面的一切子结点,并天生新结点
createprocp_copystr
@s_idvarchar(8000)--要复制项的列表,用逗号分开
as
declare@nidint,@oidint,@namevarchar(20)
set@s_id=,+@s_id+,
selectid,nameinto#tempfromtb
wherecharindex(,+cast(idasvarchar)+,,@s_id)>0
whileexists(select1from#temp)
begin
select@oid=id,@name=namefrom#temp
insertintotbvalues(@oid,@name)
set@nid=@@identity
execp_copy@oid,@nid,@nid
deletefrom#tempwhereid=@oid
end
go
--6.失掉指定id的子id列表
createfunctionf_getchildid(@idint)
returns@retable(idint)
as
begin
insertinto@reselectidfromtbwherepid=@id
while@@rowcount>0
insertinto@reselecta.id
fromtbainnerjoin@rebona.pid=b.id
wherea.idnotin(selectidfrom@re)
return
end
go
--7.失掉指定id的父id列表
createfunctionf_getparentid(@idint)
returns@retable(idint)
as
begin
declare@pidint
select@pid=pidfromtbwhereid=@id
while@pid0
begin
insertinto@revalues(@pid)
select@pid=pidfromtbwhereid=@pid
end
return
end
go