SQL Server cursor handling loop

Test 3 pieces of data

CREATE TABLE test_main (
id      INT,
value   VARCHAR(10),
PRIMARY KEY(id)
);

INSERT INTO test_main(id, value) VALUES (1, 'ONE');

INSERT INTO test_main(id, value) VALUES (2, 'TWO');

INSERT INTO test_main (id, value) VALUES (3, 'THREE');

Simple loop processing

DECLARE

@id INT, @value VARCHAR(10);

BEGIN

-- Define cursor.

DECLARE c_test_main CU RSOR FAST_FORWARD FOR

SELECT id, value FROM test_main;

-- Open the cursor.

OPEN c_test_main;

--Fill in data.

FETCH NEXT FROM c_test_main INTO @id, @value;

--If retrieved Data is processed.

WHILE @@fetch_status = 0

BEGIN

PRINT @value;

--Fill in the next data.

FETCH NEXT FROM c_test_main INTO @id, @value;

END;

--Close the cursor

CLOSE c_test_main;

--Release the cursor.

DEALLOCATE c_test_main;

END;

go

ONE

TWO

THREE

Cursor for updates

DECLARE

@ id INT, @value VARCHAR(10);

BEGIN

--Define the cursor.

DECLARE c_test_main CURSOR FOR

SELECT id, value FROM test_main

FOR UPDATE;

--Open the cursor.

OPEN c_test_main ;

​--Fill in data.

​FETCH NEXT FROM c_test_main INTO @id, @value;

​--Only processed if the data is retrieved.

​WHILE @@fetch_status = 0

​BEGIN

​PRINT @value;

​​​​​​​​​UPDATE

​​​​ OF c_test_main;

 ​ —Fill in the next data.

 ​FETCH NEXT FROM c_test_main INTO @id, @value;

END;

--Close the cursor

CLOSE c_test_main;

--Release the cursor.

DEALLOCATE c_test_main;

END;

go

ONE

(1 row affected )

TWO

Three

SELECT * FROM test_main;

go

id                                                                                                                                                                   2 TWO1

3 Three1

(3 rows affected)

Supports cursor rolling back and forth

Note: For testing here, the data of the test_main table is increased to 11.

DECLARE

@id INT, @value VARCHAR(10);
BEGIN

-- Define the cursor.

DECLARE c_test_main CURSOR SCROLL FOR
SELECT id, value FROM test_main;

-- Open the cursor.

OPEN c_test_ main;

-- Fill in the data.

FETCH FIRST FROM c_test_main INTO @id, @value;

PRINT 'The first row in the cursor:' + @value;

-- Fill in the data.

FETCH LAST FROM c_test_main INTO @id, @value ;

PRINT 'The last row in the cursor:' + @value;

-- Fill in data.
FETCH ABSOLUTE 3 FROM c_test_main INTO @id, @value;
PRINT 'The 3rd row in the cursor [absolute address]:' + @value;

-- Fill in data.

FETCH RELATIVE -2 FROM c_test_main INTO @id, @value;
PRINT 'Line -2 in the cursor [relative address]:' + @value;

-- Fill in Data.
FETCH PRIOR FROM c_test_main INTO @id, @value;
PRINT 'Previous row in cursor:' + @value;

-- Fill in data.
FETCH NEXT FROM c_test_main INTO @id, @value;
PRINT ' Next line in the cursor: ' + @value;

-- Close the cursor
CLOSE c_test_main;
-- Release the cursor.

DEALLOCATE c_test_main;

END;

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Transact-SQL扩展语法

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]