文件名称：关于游标使用sql
文件大小：3KB
文件格式：TXT
更新时间：2018-07-15 03:14:25
游标sql
测试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 ] ] ]