loop traversal in SQL Server (normal loop and cursor loop)

SQL often uses loops, and here's a look at common loops and cursor loops

1, first need a test table data student

2. Normal circulation

1) Cycle 5 times to modify student table information

--Looping through modification records--
DECLARE @i int
Set @i=0
While @i<5
Begin
Update Student Set demo = @i+5 where [email protected]
Set @[email protected] +1
End
--View Results--
SELECT * FROM Student

2) query results after execution

3. Cursor loop (no transaction)

1) Modify the information according to the actual data of the student table
---cursor loop traversal--
Begin
declare @a int, @error int
DECLARE @temp varchar (50)
Set @a=1
Set @error =0
--declares that the cursor is UID
DECLARE order_cursor cursor
for (select [Uid] from Student)
--Open cursor--
Open Order_cursor
--Start loop cursor variable--
FETCH NEXT from Order_cursor to @temp
While @ @FETCH_STATUS = 0--Returns the state of the last cursor executed by the FETCH statement--
Begin
Update Student Set [email protected],[email protected] where [email protected]
Set @[email protected]+1
Set @error = @error + @ @ERROR--logs are correct after each run of SQL, 0 correct
FETCH NEXT from Order_cursor into @temp--go to the next cursor, no dead loop
End
Close Order_cursor--closing cursors
Deallocate order_cursor--Release cursor
End
Go
--View Results--
SELECT * FROM Student

2) query results after execution

4. Cursor loop (transaction)

1) According to the actual circulation of student table information

---cursor loop traversal--
Begin
declare @a int, @error int
DECLARE @temp varchar (50)
Set @a=1
Set @error =0
BEGIN TRAN--declaration of affairs
--declares that the cursor is UID
DECLARE order_cursor cursor
for (select [Uid] from Student)
--Open cursor--
Open Order_cursor
--Start loop cursor variable--
FETCH NEXT from Order_cursor to @temp
While @ @FETCH_STATUS = 0--Returns the state of the last cursor executed by the FETCH statement--
Begin
Update Student Set [email protected],[email protected] where [email protected]
Set @[email protected]+1
Set @error = @error + @ @ERROR--logs are correct after each run of SQL, 0 correct
FETCH NEXT from Order_cursor to @temp--go to the next cursor
End
If @error =0
Begin
Commit Tran--COMMIT Transaction
End
Else
Begin
Rollback TRAN--ROLLBACK TRANSACTION
End
Close Order_cursor--closing cursors
Deallocate order_cursor--Release cursor
End
Go
--View Results--
SELECT * FROM Student

2) After executing the query results:

loop traversal in SQL Server (normal loop and cursor loop)