if exists (select 1
from sysobjects
where id = object_id('tb_Menu')
and type = 'U')
drop table tb_Menu
go
/*==============================================================*/
/* Table: tb_Menu */
/*==============================================================*/
create table tb_Menu (
Id int identity,
name varchar(50) null,
twoname varchar(50) null,
pId int null,
level int null,
state int null,
sort int null,
dt datetime null,
url varchar(100) null,
des varchar(1) null,
cNum int null,
constraint PK_TB_MENU primary key (Id)
)
go
declare @id1 int
exec proc_menu_add '节点1',0,@id1 out
declare @id2 int
exec proc_menu_add '节点1-1',1,@id2 out
declare @id3 int
exec proc_menu_add '节点1-1-1',2,@id3 out
declare @id4 int
exec proc_menu_add '节点1-1-2',2,@id4 out
declare @id5 int
exec proc_menu_add '节点1-2',1,@id5 out
declare @id6 int
exec proc_menu_add '节点1-2-1',5,@id6 out
declare @id7 int
exec proc_menu_add '节点1-2-2',5,@id7 out
--exec proc_menu_delete 2@id int,@newpId int,@sibId int,@dir int
--exec proc_menu_updateSort 1,2,4,-1
select * from tb_menu;
------------------------------------递归删除菜单----------------------------------------
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 --取值
while @@FETCH_STATUS=0--是MSSQL的一个全局变量(0 FETCH 语句成功,-1 FETCH 语句失败或此行不在结果集中,-2 被提取的行不存在,@@fetch_status值的改变是通过fetch next from实现的,“FETCH NEXT FROM Cursor”)
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
------------------------------------end-------------------------------------------
------------------------------------修改父节点的子节点数量-----------------------------
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
-------------------------------------end---------------------------------------------
------------------------------------修改子节点的深度----------------------------------
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
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
select @oldsort=sort,@oldpId=pId,@cNum=CNum 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
select @sibIdsort=sort,@newpId=pId from tb_Menu where Id=@sibId--获得目标节点的排序，并将目标节点的父节点重新付给传进来的@newpId(防止参数@newpId传错)
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
select @newpLevel=level from tb_Menu where id=@newpId
update tb_Menu set pId=@newpId,level=@newpLevel+1 where Id=@id
exec proc_menu_updatecNum @newpId,@cNum
set @cNum=-(@cNum)
exec proc_menu_updatecNum @oldpId,@cNum
end
print @@error
commit tran
end try
begin catch
print @@error
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
select * from tb_menu;
exec proc_menu_updateSort 5,6,0,1
select * from tb_menu;
浙公网安备 33010602011771号