Created
May 9, 2017 02:45
-
-
Save OneYX/3e323a5e5bcdc254108476200bb7b2da to your computer and use it in GitHub Desktop.
存储过程实现无限级分类 -- https://yq.aliyun.com/wenji/110761
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
在做考题的的时候,涉及到分类,虽然不是无限级的,但是,为了以后扩展用,想做成无限级,在网上找找了,一个用存储过程作的,虽然添加,编辑,移动,用的是存储过程,可是在读出来的时候只用了一条Select 语句,感觉挺爽的,下面我把存储过程列出来,我只用到了添加,编辑,没有用到移动。 | |
1,表结构 | |
=================================================== | |
表结构: | |
表名:Tb_Column | |
表结构(所有字段非空): | |
Column_ID int 主键(注:非标识) | |
Column_Name nvarchar(50)分类名称 | |
Parent_ID int 父分类ID(默认值0) | |
Column_Path nvarchar(1000) 分类路径 | |
Column_Depth int分类深度(默认值0) | |
Column_Order int排序(默认值0) | |
Column_Intro nvarchar(1000)分类说明 | |
================================================ | |
2.添加的存储过程 | |
CREATE PROCEDURE sp_Column_Insert | |
( | |
@Parent_ID int, | |
@Column_Name nvarchar(50), | |
@Column_Intro nvarchar(1000) | |
) | |
AS | |
Declare @Err As int | |
Set @Err=0 | |
Begin Tran | |
--通过现有记录获取栏目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 = 1 | |
Goto theEnd | |
End | |
--获取同父节点下的最大序号 | |
Select @Column_Order = Max(Column_Order) From Tb_PicColumn 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=1 | |
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_Name,Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Intro) | |
Values(@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column_Order,@Column_Intro) | |
IF @@Error<>0 | |
Begin | |
Set @Err=1 | |
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 | |
Rollback Tran | |
Return 0 | |
End | |
GO | |
3.删除的存储过程 | |
CREATE PROCEDURE sp_Column_Delete | |
( | |
@Column_ID int | |
) | |
AS | |
Declare @Err As int | |
Set @Err = 0 | |
Begin Tran | |
--首先查询该节点下是否有子节点 | |
Select Column_ID From Tb_Column Where Parent_ID = @Column_ID | |
IF @@RowCount<>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 | |
4.编辑的存储过程(没有用到,我自己写了一个简单的只是编辑名称,没有涉及到移动) | |
CREATE PROCEDURE sp_Column_Update | |
( | |
@Column_ID int, | |
@Parent_ID int, | |
@Column_Name nvarchar(50), | |
@Column_Intro nvarchar(1000) | |
) | |
AS | |
Declare @Err As int | |
Set @Err=0 | |
Begin Tran | |
--获取修改前的:Parent_ID,Column_Depth,Column_Order | |
Declare @oParent_ID As int | |
Declare @oColumn_Depth As int | |
Declare @oColumn_Order As int | |
Declare @oColumn_Path As nvarchar(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_ID Is Null | |
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_Intro Where Column_ID = @Column_ID | |
IF @@Error <> 0 | |
Set @Err = 2 | |
Goto theEnd | |
End | |
Declare @nColumn_Path As nvarchar(1000) | |
Declare @nColumn_Depth As int | |
Declare @nColumn_Order As int | |
--获取当前节点作为父节点所包含的节点数[包括自身] 注:如果返回 “1” 说明是单节点 | |
Declare @theCount As int | |
Select @theCount = Count(Column_ID) From Tb_Column Where Column_ID=@Column_ID Or Column_Path like ''+@oColumn_Path+'|%' | |
IF @theCount Is Null | |
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_Order Is NULL | |
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-@theCount Where (Column_Order >@oColumn_Order) And (Column_Path Not like ''+@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_Intro Where 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_Depth Is NULL Or @nColumn_Path Is Null | |
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 = @Paren | |
t_ID Or Column_Path like ''+@nColumn_Path+'|%' | |
IF @nColumn_Order Is NULL | |
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_Intro Where 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+@theCount Where Column_Order<@oColumn_Order And Column_Order >=@nColumn_Order And (Column_Path Not like ''+@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-@theCount Where Column_Order >@oColumn_Order And Column_Order<@nColumn_Order And (Column_Path Not like ''+@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_Intro Where 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 | |
Rollback Tran | |
Return @Err | |
End | |
Else --如果没有错误就返回0 | |
Begin | |
Commit Tran | |
Return 0 | |
End | |
GO | |
最后一步显示分类(只是一条select语句) | |
CREATE PROCEDURE sp_Column_List | |
AS | |
SELECT Column_ID, Column_Name, Parent_ID, Column_Path, Column_Depth, | |
Column_Order, Column_Intro | |
FROM Tb_Column | |
ORDER BY Column_Order | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment