CREATE PROCEDURE Column_Insert ( @Parent_ID int, @Column_Name nvarchar(50), @Column_Intro nvarchar(1000) ) AS Declare @Err As int Set @Err=0 Begin Tran --判断父类是否已存在该类别名 Declare @Column_Name_Count As int Select @Column_Name_Count = count(Column_ID) From Tb_Column where Column_Name=@Column_Name and Parent_ID=@Parent_ID IF @Column_Name_Count <>0 begin Set @Err =1 Goto theEnd end --通过现有记录获取栏目ID Declare @Column_ID As int Declare @Column_Depth As int Select @Column_ID = Max(Column_ID) From Tb_Column IF @Column_ID Is Not Null Set @Column_ID = @Column_ID+1 Else Set @Column_ID =1 --判断是否是顶级栏目,设置其Column_Path和Column_Order Declare @Column_Path As nvarchar(1000) Declare @Column_Order As int IF @Parent_ID =0 Begin Set @Column_Path =Ltrim(Str(@Column_ID)) Select @Column_Order = Max(Column_Order) From Tb_Column IF @Column_Order Is Not Null Set @Column_Order = @Column_Order +1 Else --如果没有查询到记录,说明这是第一条记录 Set @Column_Order =1 --深度 Set @Column_Depth =1 End Else Begin --获取父节点的路径和深度 Select @Column_Path = Column_Path ,@Column_Depth = Column_Depth From Tb_Column Where Column_ID=@Parent_ID IF @Column_Path Is Null Begin Set @Err =2 Goto theEnd End --获取同父节点下的最大序号 Select @Column_Order = Max(Column_Order) From Tb_Column Where Column_Path like ''+@Column_Path+'|%' Or Column_ID = @Parent_ID IF @Column_Order Is Not Null --如果序号存在,那么将该序号后的所有序号都加1 Begin --更新当前要插入节点后所有节点的序号 Update Tb_Column Set Column_Order = Column_Order +1 Where Column_Order >@Column_Order --同父节点下的最大序号加上1,构成自己的序号 Set @Column_Order = @Column_Order +1 End Else Begin Set @Err=2 Goto theEnd End --父节点的路径加上自己的ID号,构成自己的路径 Set @Column_Path = @Column_Path +'|'+ Ltrim(Str(@Column_ID)) --深度 Set @Column_Depth = @Column_Depth+1 End Insert Into Tb_Column(Column_ID,Column_Name,Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Intro) Values(@Column_ID,@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column_Order,@Column_Intro) IF @@Error<>0 Begin Set @Err=2 Goto theEnd End --更新当前记录之后的记录的ORDER --Update Tb_Column Set Column_Order = Column_Order+1 Where Column_Order > @Column_Order theEnd: IF @Err=0 Begin Commit Tran Return @Column_ID End Else Begin IF @Err=1 Begin Rollback Tran print @Err Return -100--其父节点有同名子节点 End Else Begin Rollback Tran print @Err Return -200--未知错误 End EndGO
CREATE PROCEDURE Column_Delete ( @Column_ID int ) AS Declare @Err As int Set @Err =0 Begin Tran --首先查询该节点下是否有子节点 declare @childnum int Select @childnum = count(Column_ID) From Tb_Column Where Parent_ID = @Column_ID IF @childnum<>0 Begin Set @Err =1 Goto theEnd End --获取该节点的Column_Order,为了删除后整理其他记录的顺序 Declare @Column_Order As int Select @Column_Order = Column_Order From Tb_Column Where Column_ID = @Column_ID IF @Column_Order Is NUll Begin Set @Err =2 Goto theEnd End --更新其他记录的Column_Order Update Tb_Column Set Column_Order = Column_Order -1 Where Column_Order >@Column_Order IF @@Error<>0 Begin Set @Err =3 Goto theEnd End --删除操作 Delete From Tb_Column Where Column_ID=@Column_ID IF @@Error<>0 Begin Set @Err =4 Goto theEnd End --更新其他记录的Column_ID --Update Tb_Column Set Column_ID= Column_ID -1 Where Column_ID >@Column_ID --IF @@Error<>0 -- Begin -- Set @Err =5 -- Goto theEnd -- End theEnd: IF @Err =0 Begin Commit Tran Return 0--删除成功 End Else Begin IF @Err=1 Begin Rollback Tran Return 1--有子节点 End Else Begin Rollback Tran Return 2--未知错误 End End GO
CREATEPROCEDURE Column_Update ( @Column_IDint, @Parent_IDint, @Column_Namenvarchar(50), @Column_Intronvarchar(1000) ) AS Declare@ErrAsint Set@Err=0 BeginTran --获取修改前的:Parent_ID,Column_Depth,Column_Order Declare@oParent_IDAsint Declare@oColumn_DepthAsint Declare@oColumn_OrderAsint Declare@oColumn_PathAsnvarchar(1000) Select@oParent_ID= Parent_ID, @oColumn_Depth= Column_Depth,@oColumn_Order= Column_Order, @oColumn_Path= Column_Path From Tb_Column Where Column_ID =@Column_ID IF@oParent_IDIsNull Begin Set@Err=1 Goto theEnd End --如果父ID没有改变,则直接修改栏目名和栏目简介 IF@oParent_ID=@Parent_ID Begin Update Tb_Column Set Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Set@Err=2 Goto theEnd End Declare@nColumn_PathAsnvarchar(1000) Declare@nColumn_DepthAsint Declare@nColumn_OrderAsint --获取当前节点作为父节点所包含的节点数[包括自身] 注:如果返回 “1” 说明是单节点 Declare@theCountAsint Select@theCount=Count(Column_ID) From Tb_Column Where Column_ID=@Column_IDOr Column_Path like''+@oColumn_Path+'|%' IF@theCountIsNull Begin Set@Err=3 Goto theEnd End IF@Parent_ID=0--如果是设置为顶级节点,将节点设置为最后一个顶级节点 Begin --Print '设置为顶级栏目' Set@nColumn_Path=Ltrim(Str(@Column_ID)) Set@nColumn_Depth=1 Select@nColumn_Order=Max(Column_Order) From Tb_Column IF@nColumn_OrderIsNULL Begin Set@Err=4 Goto theEnd End Set@nColumn_Order=@nColumn_Order-@theCount+1 --更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序 --Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的:Column_Order' Update Tb_Column Set Column_Order = Column_Order-@theCountWhere (Column_Order >@oColumn_Order) And (Column_Path Notlike''+@oColumn_Path+'|%') IF@@Error<>0 Begin Set@Err=7 Goto theEnd End --Print '更新本栏目的:Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro' --Print 'Order : '+Ltrim(Str(@nColumn_Order)) Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth,Column_Order =@nColumn_Order, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=5 Goto theEnd End --Print '更新本栏目下的所有子栏目的:Column_Path,Column_Depth,Column_Order' Update Tb_Column Set Column_Path =Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+( @nColumn_Order-@oColumn_Order) Where Column_Path like''+@oColumn_Path+'|%' IF@@Error<>0 Begin Set@Err=6 Goto theEnd End End Else Begin --获取未来父节点的相关信息,并设置本节点的相关值 Select@nColumn_Depth= Column_Depth,@nColumn_Path= Column_Path From Tb_Column Where Column_ID =@Parent_ID IF@nColumn_DepthIsNULLOr@nColumn_PathIsNull Begin Set@Err=8 Goto theEnd End Set@nColumn_Depth=@nColumn_Depth+1 Select@nColumn_Order=Max(Column_Order) From Tb_Column Where Column_ID =@Parent_IDOr Column_Path like''+@nColumn_Path+'|%' IF@nColumn_OrderIsNULL Begin Set@Err=9 Goto theEnd End Set@nColumn_Path=@nColumn_Path+'|'+Ltrim(Str(@Column_ID)) IF@nColumn_Order=@oColumn_Order+1--如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变 Begin Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=10 Goto theEnd End End Set@nColumn_Order=@nColumn_Order+1 --更新三部分 1 本树更改之前的后面(或前面)记录的顺序 1 节点本身 3 所有子节点 --分为向上移或象下移 --Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的:Column_Order' IF@nColumn_Order<@oColumn_Order Begin Update Tb_Column Set Column_Order = Column_Order+@theCountWhere Column_Order<@oColumn_OrderAnd Column_Order >=@nColumn_OrderAnd (Column_Path Notlike''+@oColumn_Path+'|%') And Column_ID<>@Column_ID IF@@Error<>0 Begin Set@Err=12 Goto theEnd End End Else Begin Update Tb_Column Set Column_Order = Column_Order-@theCountWhere Column_Order >@oColumn_OrderAnd Column_Order<@nColumn_OrderAnd (Column_Path Notlike''+@oColumn_Path+'|%') And Column_ID<>@Column_ID IF@@Error<>0 Begin Set@Err=13 Goto theEnd End End --Print '更新本栏目的:Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro' Print'Order : '+Ltrim(Str(@nColumn_Order)) IF@nColumn_Order>@oColumn_Order Set@nColumn_Order=@nColumn_Order-@theCount Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path =@nColumn_Path,Column_Depth =@nColumn_Depth,Column_Order =@nColumn_Order, Column_Name =@Column_Name,Column_Intro =@Column_IntroWhere Column_ID =@Column_ID IF@@Error<>0 Begin Set@Err=10 Goto theEnd End --Print '更新本栏目下的所有子栏目的:Column_Paht,Column_Depth,Column_Order' Update Tb_Column Set Column_Path =Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+(@nColumn_Order-@oColumn_Order) Where Column_Path like''+@oColumn_Path+'|%' IF@@Error<>0 Begin Set@Err=11 Goto theEnd End End theEnd: IF@Err<>0--如果有错误则返回错误号 Begin RollbackTran Return@Err End Else--如果没有错误就返回0 Begin CommitTran Return0 End GO
CREATEPROCEDURE Column_List AS SELECT Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth, Column_Order, Column_Intro FROM Tb_Column ORDERBY Column_Order GO