[转载]MSSQL存储过程数据库多级分类代码

[转载]共享一个数据库多级分类代码（MSSQL存储过程版) – 遗忘海岸 – 博客园.

说明

相信多级分类在任何一个信息系统中都会用到，网上也能找到很多版本，下面这个是基于MSSQL存储过程版的，

手上还有VB跟C#版的，不过这几年数据库一直用MSSQL,编程语言却从VBScript到C#又到PB, 搞到现在这套分类代码分别用VB、C#、PB实现了一遍,浪费了不少时间，NND神马多数据库啊！！！哥被忽悠了。

分类采用前缀编码的方式，编码使用字符串类型的，当然也有使用二进制实现的牛人^_^.

表结构
说明(表Category，ClassId,ClassName,Code 为分类相关字段，DataNum,Info等是根据具体情况额外增减)

存储过程

--******************************
-- 多级分类存储过程
-- WDFrog 2012-2-15
-- http://wdfrog.cnblogs.com
--******************************

--******************************
--数据表定义
--******************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Category]
GO

CREATE TABLE [dbo].[Category] (
[ClassID] [int] NOT NULL ,
[ClassName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Code] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DataNum] [int] NULL ,

[Info] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Category] ADD
CONSTRAINT [DF_Category_DataNum] DEFAULT (0) FOR [DataNum],
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO

--*************************
-- 添加分类存储过程
--***************************

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Add]
GO

Create Proc Category_Add
@ClassName nvarchar(50),
@DataNum int ,

@Info nvarchar(1000),
@ParentID int -- 0表示根类别
As
Declare @EditCode int
Declare @StepLen int
Declare @matchStr nvarchar(50)
Declare @typeCode nvarchar(50)
Declare @Code nvarchar(200)
Declare @MyCode nvarchar(200)
Declare @ParentCode nvarchar(200)
Declare @selfCode int
Set @editCode=1
Set @StepLen=4
Set @matchStr=REPLICATE('_',@StepLen) --4个_
set @typeCode=''
Set @Code=''
Set @MyCode=''
Set @selfCode=0
Set @ParentCode=''

Select @ParentCode=Code From [Category] Where ClassID=@ParentID

If(@editCode=1)
Begin
--获取子类中编号最大的Code,column.ParentCode + matchStr中
Select Top 1 @MyCode= Code From [Category] Where Code Like @ParentCode + @matchStr Order By Code DESC
If @@ROWCOUNT >0
Begin
Set @selfCode=Cast(Right(@MyCode,@StepLen) As Int ) +1
Set @typeCode=Replicate('0',@StepLen-1) + Cast(@selfCode As nvarchar)
Set @typeCode=Right(@typeCode,@StepLen)
Set @typeCode=@ParentCode + @TypeCode
End
Else
Begin
Set @typeCode=@ParentCode +Replicate('0',@StepLen-1)+'1'
End
End
Declare @ClassID int
Set @ClassID=0
--获取最大ClassID
Select @ClassId=Max(ClassID) From [Category]
If Not @ClassID Is Null
Begin
Set @ClassId=@ClassID +1
End
Else
Begin
Set @ClassID=1
End

Insert into [Category]
(ClassID,ClassName,Code,DataNum, Info)
values
(@ClassID,@ClassName,@typeCode,@DataNum, @Info)

Select @ClassID As ClassID
Go

--********************
-- 修改分类存储过程
--*********************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Update]
GO

Create Proc Category_Update
@ClassID int , --需要修改的ClassID
@ClassName nvarchar(50),

@Info nvarchar(1000),
@ParentID int
As
Declare @EditCode int
Declare @StepLen int
Declare @matchStr nvarchar(50)
Declare @typeCode nvarchar(50)
Declare @Code nvarchar(200)
Declare @MyCode nvarchar(200)
Declare @ParentCode nvarchar(200)
Declare @selfCode int
Set @editCode=0
Set @StepLen=4
Set @matchStr=REPLICATE('_',@StepLen) --4个_
set @typeCode=''
Set @Code=''
Set @MyCode=''
Set @selfCode=0
Set @ParentCode=''

Select @ParentCode=Code From [Category] Where ClassID=@ParentID
Select @Code=Code From [Category] Where ClassID=@ClassID

--修改原有类别
--确定是否要修改Code字段
--查看是否改变了直接父类别(上一级)
If @ParentCode != Left(@code,len(@code)-@StepLen)
Begin
--过滤选择自己做为父类
If(@ParentCode !=@Code)
Begin
--过滤选择自己的子类为父类
If Len(@ParentCode) > Len(@Code)
Begin
--因为 Len(@ParentCode) > Len(@Code) 所以可以Left(@ParentCode,Len(@Code))
If Left(@ParentCode,Len(@Code)) != @Code --如果相等则为选择自己的子类为父类
Begin
Set @EditCode=1
End
End
Else
Begin
Set @EditCode=1
End
End

End

If(@editCode=1)
Begin
--获取子类中编号最大的Code,column.ParentCode + matchStr中
Select Top 1 @MyCode= Code From [Category] Where Code Like @ParentCode + @matchStr Order By Code DESC
--是否有子类
If @@ROWCOUNT >0
Begin
Set @selfCode=Cast(Right(@MyCode,@StepLen) As Int ) +1
Set @typeCode=Replicate('0',@StepLen-1) + Cast(@selfCode As nvarchar)
Set @typeCode=Right(@typeCode,@StepLen)
Set @typeCode=@ParentCode + @TypeCode
End
Else --没有子类那么编号从1开始
Begin
Set @typeCode=@ParentCode +Replicate('0',@StepLen-1)+'1'
End
End

If (@editCode=1)
Begin
Update [Category] Set
ClassName=@ClassName,Code=@typeCode, Info=@Info
where ClassID=@ClassID
End
Else
Begin
Update [Category] Set
ClassName=@ClassName, Info=@Info
where ClassID=@ClassID
End
---修改子类编号(Code)
If(@editCode=1)
Begin
Update [Category] Set
Code=@typeCode + Right(Code,Len(Code)-Len(@Code))
Where Code Like @Code + '%'
End

GO

--************************************
-- 删除一个分类，只允许删除没有子类的分类
--************************************

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Del]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Del]
GO

Create Proc Category_Del
@ClassID int
As
If (Select Count(ClassID) From[Category] Where Code Like(Select Code From [Category] Where ClassID=@ClassID)+'%' And ClassId <> @ClassId ) >0
Begin
RaisError ('不能删除带有子类的分类',16,1)
Return
End

Declare @Code nvarchar(200)
Declare @Value int
Set @Value=0
Select @Code=[Code],@Value=[DataNum] From [Category] Where [ClassID]=@ClassID
Update [Category] Set [DataNum]=[DataNum] - @Value Where [ClassID] In( Select ClassID From [Category] Where Len(Code) 4)
Begin
Select Top 1 @tempStr=Code From [Category] Where Len(Code)=Len(@Code) And Code < @Code And Left(Code,Len(Code)-4)=Left(@Code,Len(@Code)-4) Order By Code DESC
End
Else
Begin
Select Top 1 @tempStr=Code From [Category] Where Len(Code)=Len(@Code) And Code < @Code Order By Code DESC End End Else Begin If(Len(@Code) >4)
Begin
Select Top 1 @tempStr=Code From [Category] Where Len(Code)=Len(@Code) And Code > @Code And Left(Code,Len(Code)-4)=Left(@Code,Len(@Code)-4) Order By Code ASC
End
Else
Begin
Select Top 1 @tempStr=Code From [Category] Where Len(Code)=Len(@Code) And Code >@Code Order By Code ASC
End
End
End
-- //已经是最前(最后)
If @tempStr Is Null Or RTrim(LTrim(@tempStr))=''
Begin
return
End

Declare @CodeLen int
Declare @MAXLEN int
Set @CodeLen=Len(@Code)
Set @MAXLEN=200
--//设置目标类，以及目标类的子类为----0001(目标类)或----00010002(子类)为形式
Update [Category] Set Code=@maskStr +Substring(code,@CodeLen +1,@MAXLEN) Where Left(code,@CodeLen)=@tempStr
--//更新当前交换类(包括子类)Code为目标类Code
Update [Category] Set Code=@tempStr +Substring(Code,@CodeLen+1,@MAXLEN) Where Left(code,@CodeLen)=@Code
--//更新目标类（包括子类)Code为当前交换类Code
Update [Category] Set Code=@Code +Substring(code,@CodeLen +1,@MAXLEN) Where Left(code,@CodeLen)=@maskStr

Go

--****************************
--获取指定分类的父分类信息
--*****************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_QueryParent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_QueryParent]
Go

Create Proc Category_QueryParent
@ClassID int
As
Declare @ClassCode nvarchar(200)
Select @ClassCode=Code From [Category] Where ClassId=@ClassID
Select ClassID,ClassName,Code, DataNum
From [Category]
Where Len(Code) And Code = Left(@ClassCode,Len(Code))
Order By Code

Go

--******************************
-- 获取整个分类目录
--******************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Query]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Query]
Go

Create Proc Category_Query
As
Select [ClassID],[ClassName],[Code], [DataNum] From [Category] Order By [Code]
Go

--*****************************
--重置所有分类为根分类
--*****************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Reset]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Reset]
Go

Create Proc Category_Reset
As
Declare @code nvarchar(200)
Declare @i int
Set @Code=''
Set @i=1

Declare Category_Cursor CURSOR For
Select CODE From [Category]

Open Category_Cursor
Fetch Next From Category_Cursor
WHILE @@FETCH_STATUS=0
Begin
Set @Code=Replicate(N'0',4) + Cast(@i as nvarchar)
Set @Code=Right(@Code,4)
Update [Category] Set Code= @Code WHERE Current Of Category_Cursor
Set @i=@i+1
Fetch Next From Category_Cursor
End
Close Category_Cursor
DEALLOCATE Category_Cursor

Go

--*********************
-- 获取指定分类的分类名称
--************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_SelectClassName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_SelectClassName]
Go

Create Proc Category_SelectClassName
@ClassID int
AS
Select [ClassName] From [Category] Where [ClassID]=@ClassID
Go

--********************
-- 获取指定类的子类，并包括自身
--*********************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_QueryChildren]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_QueryChildren]
Go

Create Proc Category_QueryChildren
@ClassID int
As
Declare @Code nvarchar(200)
Select @Code=[Code] From [Category] Where [ClassID]=@ClassID
Select [ClassID],[ClassName],[Code], [DataNum]
From [Category] Where Code Like @Code +'%' Order By Code

Go

--**********************
-- 获取一级分类列表
--***********************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_QueryRoot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_QueryRoot]
Go

Create Proc Category_QueryRoot
AS
Select [ClassID],[ClassName],[Code], [DataNum] From [Category] Where Len(Code)=4 Order By Code

Go