Home  >  Article  >  php教程  >  SQL Server cursor handling loop

SQL Server cursor handling loop

高洛峰
高洛峰Original
2016-12-14 11:49:211113browse

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 ] ] ]


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn