SQL Server 游标处理 循环
测试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');
简单循环处理
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 ] ] ]

Outils d'IA chauds

Undresser.AI Undress
Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover
Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool
Images de déshabillage gratuites

Clothoff.io
Dissolvant de vêtements AI

AI Hentai Generator
Générez AI Hentai gratuitement.

Article chaud

Outils chauds

Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise
Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP

Dreamweaver CS6
Outils de développement Web visuel

SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)