SQL SERVER 使用游标更新数据遇到无法循环更新

[已解决问题] 解决于 2022-09-19 17:37

表结构:
create table TestTb(
[MenuId] [int] NOT NULL PRIMARY KEY IDENTITY(1,1),
[MenuName] varchar NOT NULL,
[MenuLevel] varchar NOT NULL,
[FormName] varchar NOT NULL
)
GO
新增语句:
INSERT INTO TestTb VALUES('基础数据','','')
INSERT INTO TestTb VALUES('系统管理','','')
INSERT INTO TestTb VALUES('合格证式样维护','','')
INSERT INTO TestTb VALUES('COC一致性证书式样维护','','')
INSERT INTO TestTb VALUES('环保随车清单维护','','')
INSERT INTO TestTb VALUES('合格证打印','','')
INSERT INTO TestTb VALUES('COC一致性证书打印','','')
INSERT INTO TestTb VALUES('环保随车清单打印','','')
INSERT INTO TestTb VALUES('OBD和尾气信息','','')
INSERT INTO TestTb VALUES('综合查询','','')
游标更新:
declare @MenuLevel nvarchar(100)
declare @MenuId NVARCHAR(100)
declare @NuM INT=0
declare Up CURSOR for (select MenuId,MenuLevel from TestTb )
open Up
FETCH NEXT FROM Up INTO @MenuId ,@MenuLevel
WHILE @@FETCH_STATUS = 0
begin
if @MenuLevel <>''
set @NuM =@NuM+10;
select cast (@NuM as nvarchar(100))
update TestTb set MenuLevel=@NuM where MenuId=@MenuId
PRINT @MenuId+' '+@MenuLevel;
end
FETCH NEXT FROM Up INTO @MenuId,@MenuLevel;
CLOSE Up; --关闭游标
DEALLOCATE Up; --释
--select * from TestTb
根据id进行更新数据,每一行里面的MenuLevel 前面加10,达到一个排序的效果。