Loops, for loops, cursors in SQL

When we use SQL statements to process data, we may encounter some need to iterate over a table and act on it (add, modify, delete), and we need to use the for or foreach that we often use in programming, but writing loops in SQL often seems so laborious, to turn over the information on the Web, There is no way to find a few correct and able to execute the loop processing data, here, I will share with you!

To write like a for loop in SQL, I use a cursor in SQL to implement, of course, there is a for loop in SQL, while does, and so on, I only use the cursor way to do the example, the other way people are interested to study, Successful students can reply to the following and put the code out, share with you!

Gossip less, on the example:

1. Iterate through the cursor to update and delete data from the Memberaccount table

1 DECLAREMy_cursorCURSOR --Defining Cursors2  for(SELECT *  fromDbo. Memberaccount)--find the desired set to be placed in the cursor3 OPENMy_cursor;--Open Cursor4 FETCH NEXT  fromMy_cursor;--read the first row of data5  while @ @FETCH_STATUS = 06     BEGIN7         --UPDATE dbo. Memberaccount SET UserName = UserName + ' A ' WHERE current of my_cursor; --Update8         --DELETE from dbo. Memberaccount WHERE Current of my_cursor; --Delete9         FETCH NEXT  fromMy_cursor;--reads the next row of dataTen     END One CLOSEMy_cursor;--Close Cursors A deallocateMy_cursor;--Releasing Cursors - GO

use cursors to cycle through updates and delete data from dbo.stuinfo tables

1 DECLAREMy_cursorCURSOR --Defining Cursors2  for(SELECT *  fromDbo.stuinfo)--find the desired set to be placed in the cursor3 OPENMy_cursor;--Open Cursor4 FETCH NEXT  fromMy_cursor;--read the first row of data5  while @ @FETCH_STATUS = 06     BEGIN7         --Update dbo.stuinfo SET stuname = stuname + ' AAA ' WHERE current of my_cursor;--Updates8         DELETE  fromDbo.stuinfoWHERE  Current  ofMy_cursor;--Delete9         FETCH NEXT  fromMy_cursor;--reads the next row of dataTen     END One    --Commit Tran A CLOSEMy_cursor;--Close Cursors - deallocateMy_cursor;--Releasing Cursors - GO

2. Use cursors to iterate over the data in the Memberservice table (update the time each user purchased the service)

1 DECLARE @UserId varchar( -) 2 DECLAREMy_cursorCURSOR --Defining Cursors3  for(SELECTUserid fromDbo. Memberaccount)--find the desired set to be placed in the cursor4 OPENMy_cursor;--Open Cursor5 FETCH NEXT  fromMy_cursor into @UserId;--reads the first row of data (puts the UserID in the Memberaccount table into the @userid variable)6  while @ @FETCH_STATUS = 07     BEGIN8         PRINT @UserId;--Print data (print the UserID in the Memberaccount table)9         UPDATEDbo. MemberserviceSETServiceTime= DATEADD(Month,6,getdate())WHEREUserid= @UserId;--Update DataTen         FETCH NEXT  fromMy_cursor into @UserId;--reads the next row of data (the UserID in the Memberaccount table is placed in the @userid variable) One     END A CLOSEMy_cursor;--Close Cursors - deallocateMy_cursor;--Releasing Cursors - GO

Loops, for loops, cursors in SQL