测试3条数据
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');
1> DECLARE
2> @id INT, @value VARCHAR(10);
3> BEGIN
4> -- 定义游标.
5> DECLARE c_test_main CURSOR FAST_FORWARD FOR
6> SELECT id, value FROM test_main;
7> -- 打开游标.
8> OPEN c_test_main;
9> --填充数据.
10> FETCH NEXT FROM c_test_main INTO @id, @value ;
11> --假如检索到了数据,才处理.
12> WHILE @@fetch_status = 0
13> BEGIN
14> PRINT @value ;
15> --填充下一条数据.
16> FETCH NEXT FROM c_test_main INTO @id, @value ;
17> END;
18> -- 关闭游标
19> CLOSE c_test_main;
20> --释放游标.
21> DEALLOCATE c_test_main;
22> END;
23> go
ONE
TWO
THREE
用于更新的游标
1> DECLARE
2> @id INT, @value VARCHAR(10);
3> BEGIN
4> --定义游标.
5> DECLARE c_test_main CURSOR FOR
6> SELECT id, value FROM test_main
7> FOR UPDATE;
8> --打开游标.
9> OPEN c_test_main;
10> --填充数据.
11> FETCH NEXT FROM c_test_main INTO @id, @value ;
12> --假如检索到了数据,才处理.
13> WHILE @@fetch_status = 0
14> BEGIN
15> PRINT @value ;
16> --更新数据.
17> UPDATE
18> test_main
19> SET
20> value = value + '1'
21> WHERE
22> CURRENT OF c_test_main;
23> --填充下一条数据.
24> FETCH NEXT FROM c_test_main INTO @id, @value ;
25> END;
26> --关闭游标
27> CLOSE c_test_main;
28> --释放游标.
29> DEALLOCATE c_test_main;
30> END;
31> go
ONE
(1行受影响)
TWO
Three
1> SELECT * FROM test_main;
2> go
id value
----------- ----------
1 ONE1
2 TWO1
3 Three1
(3 行受影响)
支持来回滚动的游标
注:这里为了测试,将 test_main 表的数据,增加至11条。
DECLARE
@id INT, @value VARCHAR(10);
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR SCROLL FOR
SELECT id, value FROM test_main;
-- 打开游标.
OPEN c_test_main;
-- 填充数据.
FETCH FIRST FROM c_test_main INTO @id, @value ;
PRINT '游标中的第一行:' + @value ;
-- 填充数据.
FETCH LAST FROM c_test_main INTO @id, @value ;
PRINT '游标中的最后一行:' + @value ;
-- 填充数据.
FETCH ABSOLUTE 3 FROM c_test_main INTO @id, @value ;
PRINT '游标中的第3行[绝对地址]:' + @value ;
-- 填充数据.
FETCH RELATIVE -2 FROM c_test_main INTO @id, @value ;
PRINT '游标中的第-2行[相对地址]:' + @value ;
-- 填充数据.
FETCH PRIOR FROM c_test_main INTO @id, @value ;
PRINT '游标中的上一行:' + @value ;
-- 填充数据.
FETCH NEXT FROM c_test_main INTO @id, @value ;
PRINT '游标中的下一行:' + @value ;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END;
go
SQL-92语法
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 ] ] ]