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

DECLAREMy_cursorCURSOR --Defining Cursors for(SELECT *  fromDbo. Memberaccount)--find the desired set to be placed in the cursorOPENMy_cursor;--Open CursorFETCH NEXT  fromMy_cursor;--read the first row of data while @ @FETCH_STATUS = 0      BEGIN          --UPDATE dbo. Memberaccount SET UserName = UserName + ' A ' WHERE current of my_cursor; --Update        --DELETE from dbo. Memberaccount WHERE Current of my_cursor; --Delete        FETCH NEXT  fromMy_cursor;--reads the next row of data    END  CLOSEMy_cursor;--Close CursorsdeallocateMy_cursor;--Releasing CursorsGO  

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

DECLARE @UserId varchar( -)   DECLAREMy_cursorCURSOR --Defining Cursors for(SELECTUserid fromDbo. Memberaccount)--find the desired set to be placed in the cursorOPENMy_cursor;--Open CursorFETCH NEXT  fromMy_cursor into @UserId;--reads the first row of data (puts the UserID in the Memberaccount table into the @userid variable) while @ @FETCH_STATUS = 0      BEGIN          PRINT @UserId;--Print data (print the UserID in the Memberaccount table)        UPDATEDbo. MemberserviceSETServiceTime= DATEADD(Month,6,getdate())WHEREUserid= @UserId;--Update Data        FETCH NEXT  fromMy_cursor into @UserId;--reads the next row of data (the UserID in the Memberaccount table is placed in the @userid variable)    END  CLOSEMy_cursor;--Close CursorsdeallocateMy_cursor;--Releasing CursorsGO  

The above two examples should be able to solve all of the requirements of our use of loops in SQL, if not enough, we can expand according to the above two examples, we hope to help you solve some of the similar problems.

Loops, for loops, cursors in SQL