mssqlserver 无限级分类存储过程的应用

建成的存储过程

查询结果截图

用到的代码

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tb_Column]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Tb_Column] GO Create TABLE [dbo].[Tb_Column] ( [Column_ID] [int] NOT NULL , [Column_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Parent_ID] [int] NULL , [Column_Path] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL , [Column_Depth] [int] NULL , [Column_Order] [int] NULL , [Column_Intro] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO Alter TABLE [dbo].[Tb_Column] ADD CONSTRAINT [DF_Tb_Column_Parent_ID] DEFAULT (0) FOR [Parent_ID], CONSTRAINT [DF_Tb_Column_Column_Depth] DEFAULT (0) FOR [Column_Depth], CONSTRAINT [DF_Tb_Column_Column_Order] DEFAULT (0) FOR [Column_Order], CONSTRAINT [PK_Tb_Column] PRIMARY KEY CLUSTERED ( [Column_ID] ) ON [PRIMARY] GO exec sp_addextendedproperty N'MS_Description', N'分类深度(默认值0）', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_Depth' GO exec sp_addextendedproperty N'MS_Description', N'int 主键（注：非标识）', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_ID' GO exec sp_addextendedproperty N'MS_Description', N'分类说明', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_Intro' GO exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_Name' GO exec sp_addextendedproperty N'MS_Description', N'排序(默认值0)', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_Order' GO exec sp_addextendedproperty N'MS_Description', N'分类路径', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Column_Path' GO exec sp_addextendedproperty N'MS_Description', N'父分类ID（默认值0）', N'user', N'dbo', N'table', N'Tb_Column', N'column', N'Parent_ID' GO 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_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 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 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 --下面是两个执行的例子 exec sp_Column_List exec sp_Column_Insert 9,"中国新闻8","zhoguoxinwen1"