存储过程实现无限级分类添加，修改，册除问题

悬赏园豆：100 [已关闭问题]

1.实现如下表结构

2.表结构代码

Code

GO
/****** 对象:  Table [dbo].[My_BookClass]    脚本日期: 07/27/2009 12:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[My_BookClass](
    [ClassID] [int] IDENTITY(1,1) NOT NULL,
    [BClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [ParentID] [int] NULL CONSTRAINT [DF__My_BookCl__Paren__44952D46]  DEFAULT ((0)),
    [ParentPath] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
    [RootID] [int] NULL CONSTRAINT [DF__My_BookCl__RootI__4589517F]  DEFAULT ((0)),
    [Depth] [int] NULL,
    [Child] [int] NULL CONSTRAINT [DF__My_BookCl__Child__467D75B8]  DEFAULT ((0)),
    [PrevID] [int] NULL CONSTRAINT [DF__My_BookCl__PrevI__477199F1]  DEFAULT ((0)),
    [NextID] [int] NULL CONSTRAINT [DF__My_BookCl__NextI__4865BE2A]  DEFAULT ((0)),
    [OrderID] [int] NULL CONSTRAINT [DF__My_BookCl__Order__4959E263]  DEFAULT ((0)),
    [Settings] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [description] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [keywords] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [looks] [bit] NULL CONSTRAINT [DF__My_BookCl__looks__4A4E069C]  DEFAULT ((0)),
    [IsShow] [bit] NULL CONSTRAINT [DF__My_BookCl__IsSho__4B422AD5]  DEFAULT ((1)),
    [Commend] [bit] NULL CONSTRAINT [DF__My_BookCl__Comme__4C364F0E]  DEFAULT ((0)),
    [CreateTime] [datetime] NULL CONSTRAINT [DF__My_BookCl__Creat__4D2A7347]  DEFAULT (getdate()),
 CONSTRAINT [PK_MY_BOOKCLASS] PRIMARY KEY CLUSTERED 
(
    [ClassID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'ClassID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'书类名' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'BClassName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父栏目ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'ParentID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'栏目路径' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'ParentPath'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'根栏目ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'RootID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'栏目层数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'Depth'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'子栏目数' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'Child'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'同级的上一个栏目ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'PrevID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'同级的下一个栏目ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'NextID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'OrderID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'相关属性,如样式' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'Settings'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别描述' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'description'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键词' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'keywords'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核=1，未审=0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'looks'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否显示: 显示=1，不显示=0' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'IsShow'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别推荐=1,' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'Commend'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass', @level2type=N'COLUMN', @level2name=N'CreateTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'图书类别' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'My_BookClass'

已经实现的添加存储过程

Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[My_BookClass_Create]
(
    @ClassID int output,
    @BClassName nvarchar(20),
    @ParentID int,    
    @Settings nvarchar(100),
    @description nvarchar(300),
    @keywords nvarchar(300),
    @looks bit,
    @IsShow bit,
    @Commend bit,    
    
    @action int =0
    
)
AS
Declare @OrderID int,@Depth int,@RootID int,@PrevID int,@NextID int
Declare @ParentPath varchar(100)
Begin


        if @ParentID = 0 
            begin
                set @Depth = 0 
                set @ParentPath = '0'
                set @RootID = 0 
            end 
        else
            begin
                select @Depth = Depth + 1,@ParentPath = ParentPath + ',' + ltrim(str(ClassID)), @RootID = RootID from [My_BookClass] where ClassID = @ParentID --在父类的Depth基础上+1            
            end 
        
        
        if(exists(Select * from [My_BookClass] where ParentID = @ParentID))
            begin
                select top 1 @PrevID = [ClassID] , @OrderID = OrderID + 1 from [My_BookClass] where ParentID = @ParentID ORder by OrderID Desc,[ClassID] Desc
            end 
        else
            begin
                set @PrevID = 0
                set @OrderID = 1
            end 

        insert into [My_BookClass](BClassName,ParentID,OrderID,Settings,[description],keywords,looks,IsShow,Commend,CreateTime,RootID,PrevID,Depth,ParentPath) values
                    (@BClassName,@ParentID,@OrderID,@Settings,@description,@keywords,@looks,@IsShow,@Commend,getdate(),@RootID,@PrevID,@Depth,@ParentPath)
        
        set @ClassID = @@IDENTITY 

        --@PrevID <> 0 说明此类的存在上节点，则要更新上节点所对应的下节点为自身
        if @PrevID <> 0 
            begin
                Update [My_BookClass] set [NextID] = @ClassID where [ClassID] = @PrevID
            end 
        
        --@RootID 说明@ParentID = 0 是顶级节点，则它的根节点也就为自身
        if @RootID = 0 
            begin
                Update [My_BookClass] set [RootID] = ClassID where [ClassID] = @ClassID
            end 
        
        Update [My_BookClass] set Child = Child + 1 where ClassID = @ParentID 
    end

3.现在已经实现对表的添加，请各位高手补充修改及删除的存储过程

（注：删除可进行单个或批量删除）

数据库 SQL Server

土农民 | 初学一级 | 园豆：19
提问于：2009-07-27 12:34

< >

其他回答(1)

你这个结构比较老,而且控制起来比较繁琐,现在我用的结果只用控制存储过程就可以对整个无限级树状结构进行增删改查

davidsdad | 园豆：235 (菜鸟二级) | 2009-07-27 16:56

你的这个怎么做的啊。可以给我说说么！！！！

ylellen | 园豆：202 (菜鸟二级) | 2013-07-21 15:25

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

存储过程实现无限级分类添加，修改，册除问题

欢迎，请先登录或者注册。