SQL Server 游标处理 循环
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');
简单循环处理
DECLARE
@id INT, @value VARCHAR(10);
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT id, value FROM test_main;
-- 打开游标.
OPEN c_test_main;
--填充数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
--假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN
PRINT @value;
--填充下一条数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
END;
-- 关闭游标
CLOSE c_test_main;
--释放游标.
DEALLOCATE c_test_main;
END;
go
ONE
TWO
THREE
用于更新的游标
DECLARE
@id INT, @value VARCHAR(10);
BEGIN
--定义游标.
DECLARE c_test_main CURSOR FOR
SELECT id, value FROM test_main
FOR UPDATE;
--打开游标.
OPEN c_test_main;
--填充数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
--假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN
PRINT @value;
--更新数据.
UPDATE
test_main
SET
value = value + '1'
WHERE
CURRENT OF c_test_main;
--填充下一条数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
END;
--关闭游标
CLOSE c_test_main;
--释放游标.
DEALLOCATE c_test_main;
END;
go
ONE
(1行受影响)
TWO
Three
SELECT * FROM test_main;
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 ] ] ]