关闭

树形结构的数据库实现,存储过程实现添加删除查询功能

519人阅读 评论(0) 收藏 举报
分类:
USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsDelete]    Script Date: 2015/11/19 23:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BbsDelete]

    @ID INT 
)   
AS 
    DECLARE @LID AS INT, @RID AS INT, @WID AS INT, @DID AS INT, @NUM as int
set @NUM=0
    SET @DID = 0  
    SELECT @DID = ID, @LID = LID, @RID = RID, @WID = RID - LID + 1 FROM Bbs WHERE ID = @ID  
    IF @DID != 0  
    BEGIN 
select @NUM=@WID/2
        DELETE FROM Bbs WHERE LID BETWEEN @LID AND @RID

        UPDATE Bbs SET RID = RID - @WID WHERE RID > @RID
        UPDATE Bbs SET LID = LID - @WID WHERE LID > @RID  
update Bbs set ID=ID-@NUM where ID>@ID
update Bbs set FID=FID-@NUM where FID>@ID


    END 
    SELECT @DID  






USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsInsert]    Script Date: 2015/11/19 23:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   PROCEDURE [dbo].[BbsInsert]  
(  
    @Root INT,  
@tieName nvarchar(100),
@bbsTitle nvarchar(50),
@detail nvarchar(2000),
@pubTime nchar(19),
@publisherId INT,
@tp INT,
@did INT,
@state INT
)  
AS 
    DECLARE @LID AS INT,@RID AS INT,@NID AS INT,@Tree AS INT,@NkID AS INT,@CNT AS INT

SET @LID=0
    SET @RID = 1  
    SET @NID = 0  
    SET @Tree = 0  
SET @CNT=0



-- 表中为空,没有树根节点,则先插入树根节点
set @nkid=(select count(*)+1 from Bbs)
IF (@nkid=1)
BEGIN
INSERT INTO Bbs(id,LID,RID,Tree,fid,tieName,bbsTitle,detail,pubTime,publisherId,tp,did,state)      VALUES (0,1,2,0,-1,@tieName,@bbsTitle,@detail,@pubTime,@publisherId,@tp,@did,@state)  
END
ELSE
BEGIN


--在根节点下插入



SELECT @LID=LID,@RID = RID, @Tree = Tree + 1  
FROM Bbs WHERE ID = @Root  


SET @NID=@ROOT+(@RID-@LID+1)/2

UPDATE  Bbs  SET RID = RID + 2 WHERE RID  >= @RID 
UPDATE  Bbs  SET LID = LID + 2 WHERE LID > @RID  
UPDATE  Bbs  SET ID = ID + 1 WHERE LID > @RID
UPDATE  Bbs  SET fID = fID + 1 WHERE fid> @root AND RID>@RID


INSERT INTO Bbs(id,LID,RID,Tree,fid,tieName,bbsTitle,detail,pubTime,publisherId,tp,did,state)      VALUES (@NID,@RID,@RID + 1,@Tree,@root,@tieName,@bbsTitle,@detail,@pubTime,@publisherId,@tp,@did,@state)  

SET @NkID = SCOPE_IDENTITY()  

SELECT @NkID  


END





USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[BbsSelect]    Script Date: 2015/11/19 23:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BbsSelect]
-- Add the parameters for the stored procedure here
@Root INT,
@Tree INT
AS
BEGIN


SELECT *
FROM Bbs AS Z,Bbs AS P
WHERE P.ID=@Root
AND Z.LID>=P.LID AND Z.RID <=P.RID
AND (@Tree = 0 OR Z.Tree<=P.Tree+@Tree)
ORDER BY Z.LID ASC
END





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:55944次
    • 积分:1708
    • 等级:
    • 排名:千里之外
    • 原创:105篇
    • 转载:1篇
    • 译文:10篇
    • 评论:6条
    最新评论