SqlServer procedure存储过程中 cursor游标 while循环遍历
1、 普通while循环执行SQL
declare @i int
set @i=1
while @i<=50
begin
exec [dbo].[LineCalendar] @lineId= @i
set @i=@i+1
end
2-1、cursor游标while循环(没有事务)
使用游标的顺序: 声名游标、打开游标、读取数据、关闭游标、删除游标。
begin
declare @a int,@error int
declare @temp varchar(50)
set @a=1
set @error=0
declare order_cursor cursor for (select [Uid] from Student)
open order_cursor
fetch next from order_cursor into @temp
while @@FETCH_STATUS = 0
begin
update Student set Age=15+@a,demo=@a where Uid=@temp
set @a=@a+1
set @error= @error + @@ERROR
fetch next from order_cursor into @temp
end
close order_cursor
deallocate order_cursor
end
go
2-2、cursor游标while循环(事务tran)
begin
declare @a int,@error int
declare @temp varchar(50)
set @a=1
set @error=0
begin tran
declare order_cursor cursor
for (select [Uid] from Student)
open order_cursor
fetch next from order_cursor into @temp
while @@FETCH_STATUS = 0
begin
update Student set Age=20+@a,demo=@a where Uid=@temp
set @a=@a+1
set @error= @error + @@ERROR
fetch next from order_cursor into @temp
end
if @error=0
begin
commit tran
end
else
begin
rollback tran
end
close order_cursor
deallocate order_cursor
end
go