SQL Server loop traversal (normal loop and cursor loop)

Source: Internet
Author: User

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:

SQL Server loop traversal (normal loop and cursor loop)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.