Loops, for loops, cursors in SQL

Source: Internet
Author: User

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

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.