捡到一个题目,题目如下,不清楚自己做的对于不对,还请各位看官指正。
题目:
构建数据表smMenu,主要字段有:ID、菜单名、菜单类名、上级菜单ID
测试用初始数据如下:
销售管理
报价管理
制订报价单
报价单查询
订单管理
订单录入
订单审核
计划管理
主计划
MRP计划
用递归算法将菜单数据插入到原生Tree控件,显示所有菜单(按上下级结构显示)
要求写出递归函数的定义及简单调用代码。
USE E8RFIDEng
go
/*************************************************************
** creator:JustinYang DateTime 2016-03-26
** function:针对表生成树
** 传入参数: 无
** 输出参数:@smMenu
****************************************************************/
create procedure sp_MakeTeee
as
begin
UPDATE smMenu SET upperid = id WHERE upperid IS NULL OR upperid = 0
DECLARE @smMenu Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
--遍历所有根节点,其中调用递归函数生成树
DECLARE maketree CURSOR FAST_FORWARD FOR --建立游标
SELECT id FROM smMenu WITH (NOLOCK) WHERE ISNULL(treeid, 0) = 0 AND upperid = id ORDER BY upperid ASC, id ASC
OPEN maketree
DECLARE @id INT
DECLARE @childorderid INT
SET @childorderid = 0
DECLARE @treeid VARCHAR(100)
DECLARE @sId VARCHAR(2000) --已经处理过的节点
SET @sid = ','
DECLARE @streeid VARCHAR(2000) --已经处理过的节点树节点,与@sid一一对应
SET @streeid = ','
FETCH NEXT FROM maketree INTO @id
WHILE @@fetch_status = 0
BEGIN
IF CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) < 0
BEGIN
SET @childorderid = @childorderid + 1
SET @treeid = CAST(@childorderid AS VARCHAR(3))
WHILE (LEN(@treeid) < 2)
BEGIN
SET @treeid = '0' + @treeid
END
SET @treeid = '1' + @treeid
--将当前节点加入已经处理节点
SET @sId = @sID + CAST(@id AS VARCHAR(10)) + ','
SET @streeid = @streeid + @treeid + ','
--递归处理子节点
INSERT INTO @smMenu(id,treeid,menuNM,upperMenu,upperid)
SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)
--加入根节点
INSERT INTO @smMenu
( id ,
treeid ,
menuNM ,
upperMenu ,
upperid
)
VALUES ( @id , -- id - int
@treeid , -- treeid - varchar(6000)
'' , -- menuNM - varchar(50)
'' , -- upperMenu - varchar(50)
0 -- upperid - int
)
END
FETCH NEXT FROM maketree INTO @id
END
SELECT * FROM @smMenu--返回需要的结果,treeid即为树id
CLOSE maketree
DEALLOCATE maketree
END
GO
/*******************************************************
** creator:JustinYang DateTime:2016-03-26
** function:处理树的当前节点,获取
** 输入参数:@iid当前节点ID
** @itreeid 当前节点树ID
** @sId 已经处理过的节点 用于优化作用
** 输出参数:@smMenu表
** 调用方式:Select dbo.diGuiMakeTree()
*************************************************************/
create function diGuiMakeTree(
@iid int,--当前节点
@itreeid INT,
@sId varchar(2000)
)
RETURNS @maketreetable TABLE(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
as
begin
--DECLARE @maketreetable Table(id INT,treeid VARCHAR(6000),menuNM VARCHAR(50),upperMenu VARCHAR(50),upperid INT)
declare maketree cursor FAST_FORWARD for --建立游标
SELECT id FROM smMenu WITH (NOLOCK) WHERE ISNULL(treeid, 0) = 0 AND upperid = @iid AND CHARINDEX(','+CAST(@iid AS VARCHAR(10))+',',@sId)<0 ORDER BY upperid ASC, id ASC
OPEN maketree
DECLARE @childorderid INT
SET @childorderid = 0--兄弟节点个数
DECLARE @id INT --当前处理节点
FETCH NEXT FROM maketree INTO @id
WHILE @@fetch_status = 0
BEGIN
IF (CHARINDEX(',' + CAST(@id AS VARCHAR(10)) + ',',@sid) > 0)
BEGIN
DECLARE @treeid VARCHAR(100)--当前节点对应的树节点
--计算treeid
SET @childorderid = @childorderid + 1
SET @treeid = CAST(@childorderid AS VARCHAR(3))
WHILE (LEN(@treeid) < 3)
BEGIN
SET @treeid = '0' + @treeid
END
SET @treeid = @itreeid + @treeid
SET @sId = @sId + CAST(@id AS VARCHAR(10)) + ','
--递归处理子节点
INSERT INTO @maketreetable(id,treeid,menuNM,upperMenu,upperid)
SELECT id,treeid,menuNM,upperMenu,upperid FROM dbo.diGuiMakeTree(@id, @treeid, @sId)
--处理当前节点
INSERT INTO @maketreetable
( id ,
treeid ,
menuNM ,
upperMenu ,
upperid
)
VALUES ( @id , -- id - int
@treeid , -- treeid - varchar(6000)
'' , -- menuNM - varchar(50)
'' , -- upperMenu - varchar(50)
@iid -- upperid - int
)
END
--跳转到下一个兄弟节点
FETCH NEXT FROM maketree INTO @id
END
CLOSE maketree
DEALLOCATE maketree
RETURN
END
真诚欢迎指正!