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

DECLARE my_cursor Cursor--Defining cursors
For (SELECT * FROM dbo. Memberaccount)--Find the desired set to cursor has an
OPEN My_cursor; --Open cursor
FETCH NEXT from My_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 My_cursor; --Close cursor
Deallocate my_cursor; --Releasing cursors
GO

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

DECLARE my_cursor Cursor--Defining cursors
For (SELECT UserId from dbo. Memberaccount)--Find the desired set to cursor has an
OPEN My_cursor; --Open cursor
FETCH NEXT from My_cursor to @UserId; --Read the first row of data (place the UserID in the Memberaccount table in the @userid variable)
While @ @FETCH_STATUS = 0
    BEGIN
        PRINT @UserId; --Print data (print the UserID in the Memberaccount table)
        UPDATE dbo. Memberservice SET ServiceTime = DATEADD (Month, 6, GETDATE ()) WHERE UserId = @UserId; --Update data
        FETCH NEXT from My_cursor to @UserId; --Reads the next row of data (the UserID in the Memberaccount table is placed in the @userid variable)
    END
CLOSE My_cursor; --Close cursor
Deallocate my_cursor; --Releasing cursors
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.

Transferred from: http://www.cnblogs.com/zengen/archive/2011/08/10/2133423.html