树型菜单的保存与读取的存储过程

最新推荐文章于 2025-05-08 14:17:50 发布

原创最新推荐文章于 2025-05-08 14:17:50 发布 · 679 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #insert #工作 #null #项目管理 #table

SQL Server 2000 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种通过存储过程实现树型菜单动态查询的方法，并提供了一个具体的存储过程示例及测试代码。该方法能有效查询指定节点下的所有子节点。

我们经常使用树型菜单，把树型菜单弄成动态的，经常要查询某个结点下的所有的子结点，或者往某个结点下插入新的结点，可以使用以下代码的存储过程来实现查询某一结点下的所有结点，下面提供了测试代码，

create table UDS_TreeMenu
(
    nodeID int  not null,
    childID int not null,
    nodeText varchar(50) not null,
    nodeURL varchar(1024)
)

/*
create FUNCTION TreeFun2(@id int)
RETURNS @t Table(id int,child_id int,name char(20))
AS

BEGIN
insert into @t(id,child_id,name) select id,child_id,name from Test_connect where id = @id
while(@@rowcount > 0)
begin
insert into @t(id,child_id,name)
select B.id, B.child_id, B.name from @t A Inner Join Test_connect B on B.id=A.child_id where B.child_id
Not In(select Distinct child_id from @t)
end
return
END
*/
go
create procedure GetTree
@ID int
as
declare @treeTable table
(
    nodeID int,
    childID int,
    nodeText varchar(50),
    nodeURL varchar(50)
)

begin
    insert into @treeTable(nodeID,childID,nodeText,nodeURL)
    select nodeID,childID,nodeText,nodeURL from UDS_TreeMenu where nodeID=@ID
    while(@@rowcount>0)
    begin
        insert into @treeTable(nodeID,childID,nodeText,nodeURL)
        select TM.nodeID,TM.childID,TM.nodeText,TM.nodeUrL from @treeTable TT inner join UDS_TreeMenu TM on

TM.nodeID=TT.childID where TM.childID
        not in (select Distinct childID from @treeTable)
    end
    select * from @treeTable
end

delete  UDS_TreeMenu where nodeID<>''

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,0,'我的桌面','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'公告','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'新闻','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'投票','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'通信薄','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'工作日志','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'电子邮件','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(1,1,'个人文档柜','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,0,'考勤管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'员工管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'部门管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'单位管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(2,2,'考勤查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,0,'工作管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'个人工作计划','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'单位工作计划','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'项目管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'报表中心','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(3,3,'工作总结','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,0,'信息交流','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'聊天室','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'在线短信','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'意见反馈','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(4,4,'论坛','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,0,'人事管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,6,'档案信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,7,'部门管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,8,'奖惩管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(5,9,'培训管理','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(6,6,'档案管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(6,6,'档案查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'部门信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'部门查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(7,7,'员工查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(8,8,'奖惩记录','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(8,8,'奖惩维护','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(9,9,'培训记录','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(9,9,'培训维护','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,0,'资源管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,11,'图书资源管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(10,12,'办公用品管理','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书查询','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书类别管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(11,11,'图书信息管理','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(12,12,'办公信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(12,12,'办公用品登记','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,0,'车辆信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,13,'车辆使用申请','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,13,'车辆使用查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(13,14,'车辆管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'使用情况','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'使用审批','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'车辆信息','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(14,14,'维护管理','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,0,'销售管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,16,'客户关系','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,17,'销售管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,18,'供应商','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(15,19,'统计分析','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'客户信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'联系人信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'客户服务管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(16,16,'综合查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'产品信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'销售合同管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'销售记录管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(17,17,'综合查询','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(18,18,'供应商信息管理','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(18,18,'供应商联系人管理','')

insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'客户统计','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'客户服务统计','')
insert into UDS_TreeMenu(nodeID,childID,nodeText,nodeURL) values(19,19,'销售统计','')

exec GetTree 1