SQL loops, for loops, and cursors

Http://www.cnblogs.com/zengen/archive/2011/08/10/2133423.html

 

When we use SQL statements to process data, we may encounter some operations (ADD, modify, and delete) that need to traverse a table cyclically ), at this time, we need to use for or foreach, which is often used in programming. However, in SQL, writing cycles often seem so hard to read online materials, I cannot find several correct and executable methods for processing data cyclically. Here, I will share with you!

To write SQL statements similar to for loops, I use SQL cursors here. Of course, SQL statements also include for loop and while do. Here I only use the cursor Method for examples, if you are interested in other methods, you can study them. If you are successful, you can reply below and paste the code to share with you!

For example:

1. Use the cursor to update and delete data in the MemberAccount table cyclically

DECLARE My_Cursor CURSOR -- defines the CURSOR
FOR (SELECT * FROM dbo. MemberAccount) -- find the desired set and put it in the cursor.
OPEN My_Cursor; -- OPEN the cursor
FETCHNEXTFROM 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
FETCHNEXTFROM My_Cursor; -- read the next row of data
END
CLOSE My_Cursor; -- CLOSE the cursor
DEALLOCATE My_Cursor; -- release the cursor
GO

2. Update the data in the MemberService table cyclically by using the cursor (update the time each user purchased the Service)

DECLARE @ UserIdvarchar (50)
DECLARE My_Cursor CURSOR -- defines the CURSOR
FOR (SELECT UserId FROM dbo. MemberAccount) -- find the desired set and put it in the cursor.
OPEN My_Cursor; -- OPEN the cursor
FETCHNEXTFROM My_Cursor INTO @ UserId; -- read the first row of data (put the UserId in the MemberAccount table INTO the @ UserId variable)
WHILE @ FETCH_STATUS = 0
BEGIN
PRINT @ UserId; -- PRINT data (PRINT UserId in the MemberAccount table)
UPDATE dbo. MemberService SET ServiceTime = DATEADD (Month, 6, getdate () WHERE UserId = @ UserId; -- UPDATE Data
FETCHNEXTFROM My_Cursor INTO @ UserId; -- read the next row of data (put the UserId in the MemberAccount table INTO the @ UserId variable)
END
CLOSE My_Cursor; -- CLOSE the cursor
DEALLOCATE My_Cursor; -- release the cursor
GO

 

The above two examples can solve all the requirements for loop in SQL. If not, you can expand them based on the above two examples. I hope you can solve some similar problems.