1. 普通循环
- DECLARE @i int
- set @i=0
- while @i<5
- BEGIN
- update Student set demo = demo+5 WHERE Uid=@i
- set @i=@i +1
- --PRINT @i
- END
2. 游标循环 (没有事务)
--- 游标循环 (没有事务)
- BEGIN
- DECLARE @a INT,@error INT
- DECLARE @temp NVARCHAR(50)
- SET @a=1
- SET @error=0
----- 申明游标为 uid
- DECLARE order_cursor CURSOR
- FOR (SELECT [Uid] FROM Student)
----- 打开游标
OPEN order_cursor
---- 开始循环游标变量
- FETCH NEXT FROM order_cursor INTO @temp
WHILE @@FETCH_STATUS=0 ----- 返回被 FETCH 语句执行的最后游标的状态
- BEGIN
- UPDATE student SET Age=15+@a,demo=@a WHERE uid=@temp
- SET @a=@a+1
SET @error=@error+@@ERROR ------ 记录每次运行 sql 后是否正确 0 正确
FETCH NEXT FROM order_cursor INTO @temp ----- 转到下一个游标, 没有会死循环
END
CLOSE order_cursor ---- 关闭游标
DEALLOCATE order_cursor --- 释放游标
END
3. 游标循环 (含事务)
--- 游标循环遍历 --
- 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 --- 返回被 fetch 语句执行的最后游标的状态
- BEGIN
- UPDATE student SET age=40+@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
转载
https://www.cnblogs.com/onroad2016/p/8650572.html
来源: http://www.bubuko.com/infodetail-2735953.html