数据库多级分类代码（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)<=Len(@Code) And Code=Left(@Code,Len(Code)))
Delete From Category  Where ClassID=@ClassID  
 
Go
 
--**************************
-- 根据编号获取一条分类记录
--***************************
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Select]
GO
 
Create  PROCEDURE Category_Select
    @ClassID int
AS
SELECT [ClassID],[ClassName],[Code],[DataNum], [Info]
 
FROM [Category] 
WHERE
    [ClassID]=@ClassID
Go  
 
 
--**************************
-- 移动分类的排序
--*******************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Category_Move]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Category_Move]
Go
 
Create     Proc Category_Move
@ClassID int,
@IsUp bit=1
As
Declare @maskStr nvarchar(200)
Declare @tempStr nvarchar(200)
Declare @Code nvarchar(200)
Set @Code=''
Set @tempStr=''
Select @Code=Code From [Category] Where ClassID=@ClassID
Set @maskStr=REPLICATE(N'-',Len(@Code))
If  @Code !='' And ( (Len(@Code) % 4) =0 )
   Begin
     If(@isUp=1)
       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 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)<=Len(@ClassCode)
       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