/**递归删除菜单**/
if exists (select * from sysobjects where name='proc_menu_P_delete')
drop proc proc_menu_P_delete
go
create proc proc_menu_P_delete(@id int)
as
declare @count int,@tmpid int,@err int
select @count=count(1) from tb_menu where pId=@id--通过父Id获得子集数据
if(@count>0)--判断是否存在子集数据
begin
--根据父Id获得子集并将子集数据Id保存到游标中
declare ar_cursor cursor local for select id from tb_menu where pid=@id
open ar_cursor--打开游标
fetch next from ar_cursor into @tmpid --取值
/**@@FETCH_STATUS是MSSQL的一个全局变量(0:FETCH 语句成功,-1:FETCH 语句失败或此行不在结果集中,-2:被提取的行不存在
@@fetch_status值的改变是通过fetch next from实现的,“FETCH NEXT FROM Cursor”)**/
while @@FETCH_STATUS=0
begin
exec proc_menu_P_delete @tmpid--将游标中的子集Id作为参数调用存储过程（递归）
--递归出来以后将游标中的下一个Id赋值给变量
fetch next from ar_cursor into @tmpid
end
close ar_cursor
deallocate ar_cursor
end
delete tb_menu where Id=@id
go
/**删除菜单**/
if exists (select * from sysobjects where name='proc_menu_delete')
drop proc proc_menu_delete
go
create proc proc_menu_delete(@id int)
as
begin
declare @cNum int,@pId int
begin try
begin tran
select @cNum=-(CNum+1),@pId=pId from tb_Menu where Id=@id
exec proc_menu_updatecNum @pId,@cNum--修改父节点的子节点个数
exec proc_menu_P_delete @id--删除自身及其子节点
commit tran
end try
begin catch
rollback tran
end catch
end
go
/**修改父节点的子节点数量**/
if exists (select * from sysobjects where name='proc_menu_updatecNum')
drop proc proc_menu_updatecNum
go
create proc proc_menu_updatecNum(@pId int,@cNum int)
as
declare @tmpId int,@_pId int
select @_pId=pId from tb_menu where id=@pId
if(@_pId<>0)
begin
declare cor_CNum cursor local for select pId from tb_menu where id=@pId
open cor_CNum
fetch next from cor_CNum into @tmpId
while @@FETCH_STATUS=0
begin
exec proc_menu_updatecNum @tmpId,@cNum
fetch next from cor_CNum into @tmpId
end
close cor_CNum
deallocate cor_CNum
end
update tb_Menu set cNum=cNum+@cNum where id=@pId
go
/**修改子节点的深度**/
if exists (select * from sysobjects where name='proc_menu_updateLevel')
drop proc proc_menu_updateLevel
go
create proc proc_menu_updateLevel(@id int,@level int)
as
begin
declare @tmpid int,@count int
select @count=COUNT(1) from tb_Menu where pId=@id
if(@count<>0)
begin
declare cur_level cursor local for select id from tb_menu where pId=@id
open cur_level
fetch next from cur_level into @tmpid
while(@@FETCH_STATUS=0)
begin
exec proc_menu_updateLevel @tmpid,@level
fetch next from cur_level into @tmpid
end
close cur_level
deallocate cur_level
end
update tb_Menu set level=level+@level where Id=@id
end
go
/**修改节点顺序：针对Jquery的zTree控件的拖动节点排序功能**/
if exists (select * from sysobjects where name='proc_menu_updateSort')
drop proc proc_menu_updateSort
go
create proc proc_menu_updateSort(@id int,@newpId int,@sibId int,@dir int)
as
begin
/**
@id：被移动的节点，@newpId：新父节点，@sibId：新父节点下的兄弟节点，@dir：方向，移动目标前面：1，移到后面：-1
假设：原节点父节点为1，兄弟节点值以及顺序为：5,4,3,2,1，目标父节点为2，兄弟节点值以及顺序为：3,2,1
a：现将原节点4，移动到父节点为2的子节点2的“前”面 改变后的值为：
原：5,4,3,2,1 =>(移走3并且将大于3的减1)=> 4,3,2,1
新：3,2,1 =>(将大于2的节点加1)=>4,2,1 =>(新插入的排序为：目标节点排序值+1)=>4,3(插入值),2,1
b：现将原节点4，移动到父节点为2的子节点2的“后”面 改变后的值为：
原：5,4,3,2,1 =>(移走3并且将大于3的减1)=> 4,3,2,1
新：3,2,1 =>(将大于等于2的节点加1)=>4,3,1 =>(新插入的排序为：目标节点排序值+1)=>4,3,2(插入值),1
**/
begin try
begin tran
--修改原兄弟节点的顺序,大于被移走的节点全部-1
declare @oldsort int,@oldpId int,@cNum int,@level int
select @oldsort=sort,@oldpId=pId,@cNum=CNum,@level=level from tb_Menu where Id=@id
update tb_Menu set sort=sort-1 where sort>@oldsort and pId=@oldpId
if(@sibId<>0)--是否指定了兄弟节点
begin
--修改新兄弟节点的顺序、修改移动后自己节点顺序
declare @newsort int,@sibIdsort int
--获得目标节点的排序，并将目标节点的父节点重新付给传进来的@newpId(防止参数@newpId传错)
select @sibIdsort=sort,@newpId=pId from tb_Menu where Id=@sibId
if(@dir=1)--移动到目标节点的前面
begin
update tb_Menu set sort=sort+1 where pId=@newpId and sort>@sibIdsort
update tb_Menu set sort=@sibIdsort+1 where id=@id
end
else if(@dir=-1)
begin
update tb_Menu set sort=sort+1 where pId=@newpId and sort>=@sibIdsort
update tb_Menu set sort=@sibIdsort where Id=@id
end
end
else if(@newpId<>0 and @sibId=0)--直接移动某个父节点最下面，即没有选中目标兄弟节点
begin
update tb_Menu set sort=sort+1 where pId=@newpId
update tb_Menu set sort=1 where Id=@id
end
if(@newpId<>@oldpId)--判断是否改变了父节点
begin
declare @newpLevel int
set @cNum=@cNum+1
if(@newpId<>0)
begin
select @newpLevel=level from tb_Menu where id=@newpId
end
else
begin
set @newpLevel=0
end
update tb_Menu set pId=@newpId where Id=@id
declare @levelCount int
PRINT @newpLevel
set @levelCount=@newpLevel+1-@level
exec proc_menu_updateLevel @id,@levelCount
exec proc_menu_updatecNum @newpId,@cNum
set @cNum=-(@cNum)
exec proc_menu_updatecNum @oldpId,@cNum
end
commit tran
end try
begin catch
rollback tran
end catch
end
go
/**新增节点**/
if exists (select * from sysobjects where name='proc_menu_add')
drop proc proc_menu_add
go
create proc proc_menu_add(@name varchar(50),@pId int,@id int output)
as
begin
begin try
begin tran
declare @pLevel int
if(@pId<>0)
begin
select @pLevel=level+1 from tb_Menu where Id=@pId
end
else
begin
set @pLevel=1
end
insert into tb_Menu(name,twoname,pId,level,cNum,state,sort,dt,url,des)
values(@name,'',@pId,@pLevel,0,1,0,getdate(),'','')
set @id=@@IDENTITY
select @id
update tb_Menu set sort=sort+1 where pId=@pId--兄弟元素排序加1
exec proc_menu_updatecNum @pId,1--修改父节点的子节点数量
commit tran
end try
begin catch
set @id=0
rollback tran
end catch
end
go
/**根据节点获得所有子节点数据**/
--方法1：连接查询
if exists (select * from sysobjects where name='proc_menu_select')
drop procedure proc_menu_select
go
create proc proc_menu_select(@id int)
as
declare @i int
declare @count int, @tmpid int
create table #tb(
id int,
name varchar(50),
twoname varchar(50),
pId int,
level int,
state int,
sort int,
url varchar(100)
)
select @count=COUNT(1) from tb_Menu where pId=@id
select @i=level-1 from tb_Menu where pId=@id
insert #tb select m.id,m.name,m.twoname,m.pId,m.level,m.state,m.sort,m.url from tb_Menu m where pId=@id
while(@count<>0)
begin
set @i=@i+1
select @count=COUNT(1) from tb_Menu m left join #tb on #tb.id=m.pId where #tb.level=@i
insert #tb select m.id,m.name,m.twoname,m.pId,m.level,m.state,m.sort,m.url from tb_Menu m
left join #tb on #tb.id=m.pId where #tb.level=@i
end
select id,name,twoname,pId,level,state,sort,url from #tb order by sort desc
go
--方法2：递归查询
if exists(select * from sysobjects where name='proc_menu_sele')
drop proc proc_menu_sele
go
create proc proc_menu_sele(@id int,@t int)
as
if(@t<>0)
begin
declare @tmppId int
select @tmppId=pId from tb_Menu where Id=@id
create table #tab(
id int,
name varchar(50),
twoname varchar(50),
pId int,
level int,
state int,
sort int,
url varchar(100)
)
end
declare @tmpid int,@count int
select @count=COUNT(1) from tb_Menu where Id=@id
if(@count<>0)
begin
insert INTO #tab select id,name,twoname,pId,level,state,sort,url from tb_Menu where Id=@id
declare cur_select cursor local for select id from tb_menu where pId=@id
open cur_select
fetch next from cur_select into @tmpid
while(@@FETCH_STATUS=0)
begin
exec proc_menu_sele @tmpid,0
fetch next from cur_select into @tmpid
end
close cur_select
deallocate cur_select
end
else
begin
insert INTO #tab select id,name,twoname,pId,level,state,sort,url from tb_Menu where Id=@id
end
declare @p int
select @p=pId from tb_Menu where Id=@id
if(@p=@tmppId)
begin
select * from #tab order by sort desc
end
go
