SQL无限分类存储过程整理1
优点:字段较少，有增删改查功能，不过查询太笼统。
缺点:
1.不算是在很正的无限分类,ClassPath这个字段定义限制。
2.主键CLASSID不是自增的，使用CODESMITH批量生成多层架构代码中会导致出错。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArticleClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ArticleClass]
GO
CREATE TABLE [dbo].[ArticleClass] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,--主键（注：非标识）
[ClassName] [nvarchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,--分类名称
[ParentID] [int] NOT NULL ,--父分类ID（默认值0）
[ClassPath] [nvarchar] (1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,--分类路径
[ClassDepth] [int] NOT NULL ,--分类深度(默认值0）
[ClassOrder] [int] NOT NULL ,--分排序(默认值0)
[ClassDescription] [nvarchar] (1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,--分类说明
[iSystem] [int] NULL --是否是系统默认分类
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticleClass] ADD
CONSTRAINT [DF_ArticleClass_ParentID] DEFAULT (0) FOR [ParentID],
CONSTRAINT [DF_ArticleClass_ClassDepth] DEFAULT (0) FOR [ClassDepth],
CONSTRAINT [DF_ArticleClass_ClassOrder] DEFAULT (0) FOR [ClassOrder],
CONSTRAINT [DF_ArticleClass_iSystem] DEFAULT (0) FOR [iSystem],
CONSTRAINT [PK_ArticleClass] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'主键（非标识）', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassID'
GO
exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassName'
GO
exec sp_addextendedproperty N'MS_Description', N'父分类ID', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ParentID'
GO
exec sp_addextendedproperty N'MS_Description', N'分类路径', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassPath'
GO
exec sp_addextendedproperty N'MS_Description', N'分类深度', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassDepth'
GO
exec sp_addextendedproperty N'MS_Description', N'分类排序', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassOrder'
GO
exec sp_addextendedproperty N'MS_Description', N'分类说明', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'ClassDescription'
GO
exec sp_addextendedproperty N'MS_Description', N'是否是系统分类', N'user', N'dbo', N'table', N'ArticleClass', N'column', N'iSystem'
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Insert_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Insert_ArticleClass]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--新建分类的存储过程
/*
exec sp_Insert_ArticleClass 0, '農業及漁業','Agriculture and Fishing'
exec sp_Insert_ArticleClass 1, '農作物種植業','Agriculture and Fishing'
exec sp_Insert_ArticleClass 2, '蔬菜種植業','Agriculture and Fishing'
exec sp_Insert_ArticleClass 3, '蔬菜的種植','Agriculture and Fishing'
exec sp_Insert_ArticleClass 2, '其他農作物種植業','Agriculture and Fishing'
exec sp_Insert_ArticleClass 4, '花卉的種植','Agriculture and Fishing'
exec sp_Insert_ArticleClass 4, '㈬果的種植','Agriculture and Fishing'
exec sp_Insert_ArticleClass 4, '其他農作物的種植','Agriculture and Fishing'
*/
--exec sp_Delete_ArticleClass 2
--select * from ArticleClass
CREATE PROCEDURE sp_Insert_ArticleClass
(
@ParentID int,
@ClassName nvarchar(50),
@ClassDescription nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--通过现有记录获取栏目ID
Declare @ClassID As int
Declare @ClassDepth As int
Select @ClassID = Max(ClassID) From ArticleClass
IF @ClassID Is Not Null
Set @ClassID = @ClassID+1
Else
Set @ClassID = 1
--判断是否是顶级栏目，设置其ClassPath和ClassOrder
Declare @ClassPath As nvarchar(1000)
Declare @ClassOrder As int
IF @ParentID = 0
Begin
Set @ClassPath =Ltrim(Str(@ClassID))
Select @ClassOrder = Max(ClassOrder) From ArticleClass
IF @ClassOrder Is Not Null
Set @ClassOrder = @ClassOrder + 1
Else --如果没有查询到记录，说明这是第一条记录
Set @ClassOrder = 1
--深度
Set @ClassDepth = 1
End
Else
Begin
--获取父节点的路径和深度
Select @ClassPath = ClassPath ,@ClassDepth = ClassDepth From ArticleClass Where ClassID=@ParentID
IF @ClassPath Is Null
Begin
Set @Err = 1
Goto theEnd
End
--获取同父节点下的最大序号
Select @ClassOrder = Max(ClassOrder) From ArticleClass Where ClassPath like ''+@ClassPath+'%' Or ClassID = @ParentID
IF @ClassOrder Is Not Null --如果序号存在，那么将该序号后的所有序号都加1
Begin
--更新当前要插入节点后所有节点的序号
Update ArticleClass Set ClassOrder = ClassOrder +1 Where ClassOrder>@ClassOrder
--同父节点下的最大序号加上1，构成自己的序号
Set @ClassOrder = @ClassOrder + 1
End
Else
Begin
Set @Err=1
Goto theEnd
End
--父节点的路径加上自己的ID号，构成自己的路径
Set @ClassPath = @ClassPath + ',' + Ltrim(Str(@ClassID))
--深度
Set @ClassDepth = @ClassDepth+1
End
Insert Into ArticleClass(ClassID,ClassName,ParentID,ClassPath,ClassDepth,ClassOrder,ClassDescription) Values(@ClassID,@ClassName,@ParentID,@ClassPath,@ClassDepth,@ClassOrder,@ClassDescription)
IF @@Error<>0
Begin
Set @Err=1
Goto theEnd
End
--更新当前记录之后的记录的ORDER
--Update ArticleClass Set ClassOrder = ClassOrder+1 Where ClassOrder > @ClassOrder
theEnd:
IF @Err=0
Begin
Commit Tran
Return @ClassID
End
Else
Begin
Rollback Tran
Return 0
End
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Delete_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Delete_ArticleClass]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--删除分类存储过程
--exec sp_Delete_ArticleClass 1
--select * from ArticleClass
CREATE PROCEDURE sp_Delete_ArticleClass
(
@ClassID int
)
AS
Declare @Err As int
Set @Err = 0
Begin Tran
--首先查询该节点下是否有子节点
Select ClassID From ArticleClass Where ParentID = @ClassID
IF @@RowCount<>0
Begin
Set @Err = 1
Goto theEnd
End
--获取该节点的ClassOrder,为了删除后整理其他记录的顺序
Declare @ClassOrder As int
Select @ClassOrder = ClassOrder From ArticleClass Where ClassID = @ClassID
IF @ClassOrder Is NUll
Begin
Set @Err =2
Goto theEnd
End
--更新其他记录的ClassOrder
Update ArticleClass Set ClassOrder = ClassOrder -1 Where ClassOrder >@ClassOrder
IF @@Error<>0
Begin
Set @Err =3
Goto theEnd
End
--删除操作
Delete From ArticleClass Where ClassID=@ClassID
IF @@Error<>0
Begin
Set @Err =4
Goto theEnd
End
--更新其他记录的ClassID
--Update ArticleClass Set ClassID= ClassID - 1 Where ClassID >@ClassID
--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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Update_ArticleClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Update_ArticleClass]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--更新分类记录的存储过程
--exec sp_Update_ArticleClass @ClassID,@ParentID,@ClassName,@ClassDescription
--select * from ArticleClass
CREATE PROCEDURE sp_Update_ArticleClass
(
@ClassID int,
@ParentID int,
@ClassName nvarchar(50),
@ClassDescription nvarchar(1000)
)
AS
Declare @Err As int
Set @Err=0
Begin Tran
--获取修改前的：ParentID,ClassDepth,ClassOrder
Declare @oParentID As int
Declare @oClassDepth As int
Declare @oClassOrder As int
Declare @oClassPath As nvarchar(1000)
Select @oParentID = ParentID, @oClassDepth = ClassDepth,@oClassOrder= ClassOrder, @oClassPath = ClassPath From ArticleClass Where ClassID= @ClassID
IF @oParentID Is Null
Begin
Set @Err = 1
Goto theEnd
End
--如果父ID没有改变，则直接修改栏目名和栏目简介
IF @oParentID = @ParentID
Begin
Update ArticleClass Set ClassName = @ClassName,ClassDescription = @ClassDescription Where ClassID = @ClassID
IF @@Error <> 0
Set @Err = 2
Goto theEnd
End
Declare @nClassPath As nvarchar(1000)
Declare @nClassDepth As int
Declare @nClassOrder As int
--获取当前节点作为父节点所包含的节点数[包括自身] 注：如果返回 “1” 说明是单节点
Declare @theCount As int
Select @theCount = Count(ClassID) From ArticleClass Where ClassID=@ClassID Or ClassPath like ''+@oClassPath+'%'
IF @theCount Is Null
Begin
Set @Err = 3
Goto theEnd
End
IF @ParentID=0 --如果是设置为顶级节点，将节点设置为最后一个顶级节点
Begin
--Print '设置为顶级栏目'
Set @nClassPath = Ltrim(Str(@ClassID))
Set @nClassDepth =1
Select @nClassOrder = Max(ClassOrder) From ArticleClass
IF @nClassOrder Is NULL
Begin
Set @Err = 4
Goto theEnd
End
Set @nClassOrder = @nClassOrder - @theCount + 1
--更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
--Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：ClassOrder'
Update ArticleClass Set ClassOrder = ClassOrder-@theCount Where (ClassOrder >@oClassOrder) And (ClassPath Not like ''+@oClassPath+'%')
IF @@Error <> 0
Begin
Set @Err = 7
Goto theEnd
End
--Print '更新本栏目的：ParentID,ClassPath,ClassDepth,ClassOrder,ClassName,ClassDescription'
Print 'Order : '+Ltrim(Str(@nClassOrder))
Update ArticleClass Set ParentID=@ParentID,ClassPath = @nClassPath,ClassDepth = @nClassDepth,ClassOrder = @nClassOrder, ClassName = @ClassName,ClassDescription = @ClassDescription Where ClassID = @ClassID
IF @@Error <> 0
Begin
Set @Err = 5
Goto theEnd
End
--Print '更新本栏目下的所有子栏目的：ClassPath,ClassDepth,ClassOrder'
Update ArticleClass Set ClassPath = Replace(ClassPath,@oClassPath,@nClassPath),ClassDepth = ClassDepth + (@nClassDepth-@oClassDepth),ClassOrder = ClassOrder+( @nClassOrder-@oClassOrder) Where ClassPath like ''+@oClassPath+'%'
IF @@Error <> 0
Begin
Set @Err = 6
Goto theEnd
End
End
Else
Begin
--获取未来父节点的相关信息,并设置本节点的相关值
Select @nClassDepth = ClassDepth,@nClassPath = ClassPath From ArticleClass Where ClassID = @ParentID
IF @nClassDepth Is NULL Or @nClassPath Is Null
Begin
Set @Err = 8
Goto theEnd
End
Set @nClassDepth = @nClassDepth +1
Select @nClassOrder =Max(ClassOrder) From ArticleClass Where ClassID = @ParentID Or ClassPath like ''+@nClassPath+'%'
IF @nClassOrder Is NULL
Begin
Set @Err = 9
Goto theEnd
End
Set @nClassPath = @nClassPath +''+ Ltrim(Str(@ClassID))
IF @nClassOrder = @oClassOrder+1 --如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
Begin
Update ArticleClass Set ParentID=@ParentID,ClassPath = @nClassPath,ClassDepth = @nClassDepth, ClassName = @ClassName,ClassDescription = @ClassDescription Where ClassID = @ClassID
IF @@Error <> 0
Begin
Set @Err = 10
Goto theEnd
End
End
Set @nClassOrder = @nClassOrder + 1
--更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身 3 所有子节 点
--分为向上移或象下移
--Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的：ClassOrder'
IF @nClassOrder < @oClassOrder
Begin
Update ArticleClass Set ClassOrder = ClassOrder+@theCount Where ClassOrder<@oClassOrder And ClassOrder >=@nClassOrder And (ClassPath Not like ''+@oClassPath+'%') And ClassID<>@ClassID
IF @@Error <> 0
Begin
Set @Err = 12
Goto theEnd
End
End
Else
Begin
Update ArticleClass Set ClassOrder = ClassOrder-@theCount Where ClassOrder >@oClassOrder And ClassOrder<@nClassOrder And (ClassPath Not like ''+@oClassPath+'%') And ClassID<>@ClassID
IF @@Error <> 0
Begin
Set @Err = 13
Goto theEnd
End
End
--Print '更新本栏目的：ParentID,ClassPath,ClassDepth,ClassOrder,ClassName,ClassDescription'
Print 'Order : '+Ltrim(Str(@nClassOrder))
IF @nClassOrder > @oClassOrder
Set @nClassOrder = @nClassOrder - @theCount
Update ArticleClass Set ParentID=@ParentID,ClassPath = @nClassPath,ClassDepth = @nClassDepth,ClassOrder = @nClassOrder, ClassName = @ClassName,ClassDescription = @ClassDescription Where ClassID = @ClassID
IF @@Error <> 0
Begin
Set @Err = 10
Goto theEnd
End
--Print '更新本栏目下的所有子栏目的：Column_Paht,ClassDepth,ClassOrder'
Update ArticleClass Set ClassPath = Replace(ClassPath,@oClassPath,@nClassPath),ClassDepth = ClassDepth + (@nClassDepth-@oClassDepth),ClassOrder = ClassOrder+(@nClassOrder-@oClassOrder) Where ClassPath like ''+@oClassPath+'%'
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
