通用无限级分类的存储过程

  • I0_519759
    了解作者
  • 2.6KB
    文件大小
  • rar
    文件格式
  • 0
    收藏次数
  • VIP专享
    资源类型
  • 0
    下载次数
  • 2022-06-01 09:34
    上传日期
一个通用的无限级分类存储过程,包含插入,更新,与删除!非常不错的
无限级分类的存储过程.rar
  • 无限级分类的存储过程.sql
    10.2KB
内容介绍
表结构: 表名: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)分类说明 Create table Tb_Column ( Column_ID int primary key , Column_Name nvarchar(50), Parent_ID int, Column_Path nvarchar(1000), Column_Depth int, Column_Order int, Column_Intro nvarchar(1000), ) --存储过程一:新建分类 alter 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_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=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_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=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 --存储过程二:删除分类 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 --存储过程三:编辑分类 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 = @Parent_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 Colum
评论
    相关推荐