欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

SQL Server 游标处理 循环

程序员文章站 2022-04-26 13:07:32
...
测试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 ] ] ]

相关标签: SQL游标