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 required collection and put it in the cursor has an. 
OPENMy_cursor;--Open cursor 
FETCH 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 from My_cursor; -- Read the next line of data   
  End 
  close< Span style= "color: #000000;" > My_cursor; -- close cursor  
  Deallocate My_cursor; -- go                 

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

DECLARE @UserId varchar(50)
DECLAREMy_cursorCURSOR --Defining cursors 
For(SELECTUseridFromDbo. Memberaccount)--Find the required collection and put it in the cursor has an. 
OPENMy_cursor;--Open cursor 
FETCH NEXT FromMy_cursorInto @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 from my_cursor into< Span style= "color: #000000;" >  @UserId -- reads the next line of data (the UserID in the Memberaccount table is placed in the @userid variable). Span style= "color: #008080;" >  
 end close My_cursor; -- close cursor  
 deallocate My_cursor; -- 
 go                 

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