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 forthNote: For testing here, the data of the test_main table is increased to 11. DECLARE @id INT, @value VARCHAR(10);
BEGIN
DECLARE c_test_main CURSOR SCROLL FOR
SELECT id, value FROM test_main;
-- Open the cursor.
OPEN c_test_ main;
-- Fill in the data.
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;
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.
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 ] ] ]