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