SQL中Cursor的标准语法

浏览数：19 / 时间：2015年06月12日

游标 (Cursor) 使用户可以逐行访问由数据库返回的查询结果集。
使用 Cursor 的一个主要的原因就是把集合操作转换成单个记录处理方式。

 1 ISO Syntax
 2 DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
 3      FOR select_statement 
 4      [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
 5 [;]
 6 Transact-SQL Extended Syntax
 7 DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
 8      [ FORWARD_ONLY | SCROLL ] 
 9      [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
10      [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
11      [ TYPE_WARNING ] 
12      FOR select_statement 
13      [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
14 [;]

使用 Cursor 遍历数据

 1 IF OBJECT_ID(N‘[TestTable]‘, ‘U‘) IS NOT NULL
 2     DROP TABLE [TestTable];
 3 
 4 CREATE TABLE [TestTable] (
 5     [ID] [bigint] IDENTITY(1, 1) NOT NULL
 6     ,[FirstName] [nvarchar](256) NULL
 7     ,[LastName] [nvarchar](256) NULL
 8     );
 9 
10 INSERT INTO [TestTable] (
11     [FirstName]
12     ,[LastName]
13     )
14 VALUES (
15     ‘Dennis‘
16     ,‘Gao‘
17     );
18 
19 INSERT INTO [TestTable] (
20     [FirstName]
21     ,[LastName]
22     )
23 VALUES (
24     ‘Good‘
25     ,‘Morning‘
26     );
27 
28 DECLARE @first_name VARCHAR(50);
29 DECLARE @last_name VARCHAR(50);
30 
31 -- declare cursor
32 DECLARE test_table_cursor CURSOR LOCAL SCROLL STATIC
33 FOR
34 SELECT [FirstName]
35     ,[LastName]
36 FROM [TestTable]
37 ORDER BY [FirstName];
38 
39 -- open the cursor
40 OPEN test_table_cursor;
41 
42 FETCH NEXT
43 FROM test_table_cursor
44 INTO @first_name
45     ,@last_name;
46 
47 WHILE @@FETCH_STATUS = 0
48 BEGIN
49     -- print the name
50     PRINT @first_name + ‘ ‘ + @last_name;
51 
52     FETCH NEXT
53     FROM test_table_cursor
54     INTO @first_name
55         ,@last_name;
56 END
57 
58 -- close the cursor
59 CLOSE test_table_cursor;
60 
61 -- deallocate the cursor
62 DEALLOCATE test_table_cursor;