数据库多级分类代码（MSSQL存储过程版)

--****************************** -- 多级分类存储过程 -- 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


                  

	 	
                    

                        

                            

                                
< 上一篇
我的MYSQL学习心得 备份和恢复(详细)
                            
                            

                                 
下一篇 >
MYSQL: Cannot delete or update a parent row: a foreign key constraint fails