树形菜单的二叉链表实现--数据库实现（增删改存储过程实现）

数据库表结构
CREATE TABLE [dbo].[Level](
[id] [int] NOT NULL,
[lId] [int] NULL,
[rId] [int] NULL,
[tree] [int] NULL,
[nodeName] [varchar](50) NOT NULL,
[fId] [int] NULL,
[tp] [int] NULL,
[levelId] [int] NULL,
 CONSTRAINT [PK_Level_1] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

存储过程实现增删改查功能

删除

USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[LevelDelete]    Script Date: 2015/12/29 22:30:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LevelDelete]
( 
    @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 Level WHERE ID = @ID  
    IF @DID != 0  
    BEGIN 
select @NUM=@WID/2
        DELETE FROM Level WHERE LID BETWEEN @LID AND @RID

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


    END 
    SELECT @DID  

添加

USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[LevelInsert]    Script Date: 2015/12/29 22:31:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[LevelInsert]  
(  
    @Root INT,  
    @NodeName NVARCHAR(50),
@tp int
)  
AS 
    DECLARE @LID AS INT,@RID AS INT,@NID AS INT,@Tree AS INT,@NkID AS INT,@CNT AS INT

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

set @nkid=(select count(*)+1 from Level)


    IF @Root = 0  
    BEGIN 
SELECT @RID=RID  FROM Level WHERE ID=@Root
SET @NID=@RID/2
UPDATE  Level  SET RID = RID + 2 WHERE id=0
INSERT INTO Level(id,LID,RID,Tree,NodeName,fid,tp)      VALUES (@NID,@RID,@RID + 1,1,@NodeName,@root,@tp)  

       -- SELECT TOP 1 @RID = RID + 1  
       -- FROM Level ORDER BY RID DESC 
    END 
    ELSE 
    BEGIN 
        SELECT @LID=LID,@RID = RID, @Tree = Tree + 1  
        FROM Level WHERE ID = @Root  


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

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


INSERT INTO Level(id,LID,RID,Tree,NodeName,fid,tp)      VALUES (@NID,@RID,@RID + 1,@Tree,@NodeName,@root,@tp)  

        SET @NkID = SCOPE_IDENTITY()  
    END 
    SELECT @NkID 

查询

USE [JPKCDB]
GO
/****** Object:  StoredProcedure [dbo].[LevelSelect]    Script Date: 2015/12/29 22:31:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LevelSelect]
-- Add the parameters for the stored procedure here
@Root INT,
@Tree INT
AS
BEGIN


SELECT Z.ID,z.lid,z.rid,Z.Tree,Z.NodeName,z.fid,z.tp
FROM Level AS Z,Level 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