SQL Cursor 基本用法及举例说明

发表于 分类于 本文字数： 1.9k 阅读时长 ≈ 2 分钟

Cursor 即游标，我对其作用总结“操作想要操作的每条数据”
操作想要操作的每条数据看起来简单，其实如果不用游标有些操作实现起来比较困难，或者效率非常低（虽然游标的效率也低，但有些复杂操作游标相比效率较高一些）。

Cursor 格式

Cursor 一般用法都如下，主要操作在以下“SQL 语句执行过程”处。

1
2
3
4
5
6
7
8
9
10
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
WHILE @@FETCH_STATUS=0
        BEGIN
                  SQL语句执行过程... ...
                  FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
        END
CLOSE 游标名称
DEALLOCATE 游标名称

例 1

比如有个学校用来存储学生姓名的表 Students，结构如下：

1
2
3
Students
  ID INT
  Name NVARCHAR(10)

现在有个需求，要把名字为两位的两个字中间加上两个空格，以显示更美观（在显示的客户端或者网页更改更合适，但这里用于说明，改数据库）。
这个需求很多种方法可以实现，比如采用临时表，先找到名字为两位的数据放在临时表，再去更改，这个方法一方面效率很低，另一方面如果数据量比较大，临时表会占用太大的空间。
这时使用游标的优势就很明显了，正如前面所说的“操作想要操作的每条数据”。按照 Cursor 一般格式。每步的详细介绍看注释。

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @id INT //用于临时存储ID
DECLARE @name NVARCHAR(10) //用于临时存储名字

DECLARE cursor1 CURSOR FOR SELECT Name ID FROM Students WHERE LENGTH(Name) = 2 //选出想要操作的数据，在这里是找出名字长度为2的数据
OPEN cursor1 //打开游标
FETCH NEXT FROM cursor1 INTO @name,@id //将游标所在位置的Name和ID赋值到@name和ID以便操作
WHILE @@FETCH_STATUS = 0 //成功取到数据，即游标还没到头
        BEGIN
                  UPDATE Student SET Name = SUBSTRING(@name, 0, 1) + '  ' + SUBSTRING(@name, 1, 1) WHERE ID = @id //名字中间加空格
                  FETCH NEXT FROM cursor1 INTO @name,@id //游标下移一条数据，再次将Name和ID赋值到@name和ID
        END
CLOSE cursor1 //关闭游标
DEALLOCATE cursor1 //删除游标

例 2

表结构仍和例 1 一样，但现在需求变了：现有的数据都是按 ID 依次递增，并且这些 ID 是根据班级来划分的，比如 ID 1-50 为 1 班，51-100 为 2 班。但是某天 1 班来了个插班生，需要将其 ID 设为 51，2 班的所有 ID 依次加 1。当然这里 ID 不是自增主键，否则就不能更改了。仍按一般格式来做，详细介绍见注释。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE @newName NVARCHAR(10) //插班生的名字，假设已经赋值
DECLARE @id INT //用于临时存储ID

DECLARE cursor1 CURSOR FOR SELECT ID FROM Students WHERE ID > 50 ORDER BY ID DESC //这里需要从大到小排序，防止更改ID时有重复
OPEN cursor1 //打开游标
FETCH NEXT FROM cursor1 INTO @id //初始化游标
WHILE @@FETCH_STATUS=0 //成功取到数据，即游标还没到头
        BEGIN
                  UPDATE Students SET ID = ID + 1 WHERE ID = @id
                  FETCH NEXT FROM cursor1 INTO @id //游标下移一条数据，再次将ID赋值到@id
        END
CLOSE 游标名称
DEALLOCATE 游标名称

INSERT Students(ID, Name) VALUES(51, @newName) 插入插班生记录

此操作其实有更好的方法：UPDATE Students SET ID = ID + 1 WHERE ID > 50，只用一句命令即可实现上述 2 班 ID 增 1，此处是为了举例说明而使用了游标。

总结

使用游标实质是就是对每条数据都执行一个操作，而游标记录了当前要操作的数据所在位置，每次游标下移，即记录位置下移，以实现操作下一条数据，直到满足条件的数据都被操作了一遍。可想而知，游标的效率比较低，数据量比较大时尽量不去使用它。