你好，我是朱晓峰。今天，我来和你聊一聊游标。

咱们前面学习的MySQL数据操作语句，都是针对结果集合的。也就是说，每次处理的对象都是一个数据集合。如果需要逐一处理结果集中的记录，就会非常困难。

虽然我们也可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。

这个时候，就可以用到游标。所谓的游标，也就是能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。

这么说可能有点抽象，我举一个生活中的例子，你一看就明白了。比如，你想去某个城市旅游，现在需要订酒店。你打开预订酒店的App，设置好价格区间后进行搜索，得到了一个酒店列表。接下来，你可能要逐条查看列表中每个酒店的客户评价，最后选择一个口碑不错的酒店。这个逐条搜索并对选中的数据进行操作的过程，就相当于游标对数据记录进行操作的过程。

今天我就来给你讲一讲游标的使用方法，同时还会通过一个案例串讲，帮助你更好地使用游标，让你能够轻松地处理数据集中的记录。

游标的使用步骤

游标只能在存储程序内使用，存储程序包括存储过程和存储函数。关于存储过程，我们上节课刚刚学过，这里我简单介绍一下存储函数。创建存储函数的语法是：

CREATE FUNCTION 函数名称 （参数）RETURNS 数据类型 程序体

存储函数与存储过程很像，但有几个不同点：

存储函数必须返回一个值或者数据表，存储过程可以不返回。
存储过程可以通过CALL语句调用，存储函数不可以。
存储函数可以放在查询语句中使用，存储过程不行。
存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

这节课，我们主要学习下游标在存储过程中的使用方法，因为游标在存储过程中更常用。游标在存储函数中的使用方法和在存储过程中的使用方法是一样的。

在使用游标的时候，主要有4个步骤。

第一步，定义游标。语法结构如下：

DECLARE 游标名 CURSOR FOR 查询语句

这里就是声明一个游标，它可以操作的数据集是“查询语句”返回的结果集。

第二步，打开游标。语法结构如下：

OPEN 游标名称；

打开游标之后，系统会为游标准备好查询的结果集，为后面游标的逐条读取结果集中的记录做准备。

第三步，从游标的数据结果集中读取数据。语法结构是这样的：

FETCH 游标名 INTO 变量列表；

这里的意思是通过游标，把当前游标指向的结果集中那一条记录的数据，赋值给列表中的变量。

需要注意的是，游标的查询结果集中的字段数，必须跟INTO后面的变量数一致，否则，在存储过程执行的时候，MySQL会提示错误。

第四步，关闭游标。语法结构如下：

CLOSE 游标名；

用完游标之后，你一定要记住及时关闭游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

知道了基本步骤，下面我就结合超市项目的实际案例，带你实战一下。

案例串讲

在超市项目的进货模块中，有一项功能是对进货单数据进行验收。其实就是在对进货单的数据确认无误后，对进货单的数据进行处理，包括增加进货商品的库存，并修改商品的平均进价。下面我用实际数据来演示一下这个操作流程。

这里我们要用到进货单头表（demo.importheadl）、进货单明细表（demo.importdetails）、库存表（demo.inventory）和商品信息表（demo.goodsmaster）。

进货单头表：

进货单明细表：

库存表：

商品信息表：

要验收进货单，我们就需要对每一个进货商品进行两个操作：

在现有库存数量的基础上，加上本次进货的数量；
根据本次进货的价格、数量，现有商品的平均进价和库存，计算新的平均进价：（本次进货价格 * 本次进货数量+现有商品平均进价 * 现有商品库存）/（本次进货数量+现有库存数量）。

针对这个操作，如果只用我们在第4讲里学习的SQL语句，完成起来就比较困难。

因为我们需要通过应用程序来控制操作流程，做成一个循环操作，每次只查询一种商品的数据记录并进行处理，一直到把进货单中的数据全部处理完。这样一来，应用必须发送很多的SQL指令到服务器，跟服务器的交互多，不仅代码复杂，而且也不够安全。

这个时候，如果使用游标，就很容易了。因为所有的操作都可以在服务器端完成，应用程序只需要发送一个命令调用存储过程就可以了。现在，我们就来看看如何用游标来解决这个问题。

我用代码创建了一个存储过程demo.mytest（）。当然，你也完全可以在Workbench中创建存储过程，非常简单，我就不多说了。创建存储过程的代码如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE demo.mytest(mylistnumber INT)
-> BEGIN
-> DECLARE mystockid INT;
-> DECLARE myitemnumber INT;
-> DECLARE myquantity DECIMAL(10,3);
-> DECLARE myprice DECIMAL(10,2);
-> DECLARE done INT DEFAULT FALSE; -- 用来控制循环结束
-> DECLARE cursor_importdata CURSOR FOR -- 定义游标
-> SELECT b.stockid,a.itemnumber,a.quantity,a.importprice
-> FROM demo.importdetails AS a
-> JOIN demo.importhead AS b
-> ON (a.listnumber=b.listnumber)
-> WHERE a.listnumber = mylistnumber;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 条件处理语句
->
-> OPEN cursor_importdata; -- 打开游标
-> FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice; -- 读入第一条记录
-> REPEAT
-> -- 更新进价
-> UPDATE demo.goodsmaster AS a,demo.inventory AS b
-> SET a.avgimportprice = (a.avgimportprice*b.invquantity+myprice*myquantity)/(b.invquantity+myquantity)
-> WHERE a.itemnumber=b.itemnumber AND b.stockid=mystockid AND a.itemnumber=myitemnumber;
-> -- 更新库存
-> UPDATE demo.inventory
-> SET invquantity = invquantity + myquantity
-> WHERE stockid = mystockid AND itemnumber=myitemnumber;
-> -- 获取下一条记录
-> FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice;
-> UNTIL done END REPEAT;
-> CLOSE cursor_importdata;
-> END
-> //
Query OK, 0 rows affected (0.02 sec)
-> DELIMITER ;

这段代码比较长，核心操作有6步，我来给你详细解释下。

把MySQL的分隔符改成“//”。
开始程序体之后，我定义了4个变量，分别是mystockid、myitemnumber、myquantity和myprice，这几个变量的作用是，存储游标中读取的仓库编号、商品编号、进货数量和进货价格数据。
定义游标。这里我指定了游标的名称，以及游标可以处理的数据集（mylistnumber指定的进货单的全部进货商品明细数据）。
定义条件处理语句“DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;”。
打开游标，读入第一条记录，然后开始执行数据操作。
关闭游标，结束程序。

可以看到，在这个存储过程中，我用到了条件处理语句，它的作用是告诉系统，在存储程序遇到问题的时候，应该如何处理。

条件处理语句

条件处理语句的语法结构：

DECLARE 处理方式 HANDLER FOR 问题 操作；

下面我结合刚刚的“DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;”，来解释一下条件处理语句是如何工作的。

语法结构中的“问题”是指SQL操作中遇到了什么问题。比如这里的问题是“NOT FOUND”，意思就是游标走到结果集的最后，没有记录了。也就是说，数据集中的所有记录都已经处理完了。
执行的操作是“SET done=TRUE”，done是我定义的用来标识数据集中的数据是否已经处理完成的一个标记。done=TRUE，意思是数据处理完成了。
处理方式有2种选择，分别是“CONTINUE”和“EXIT”，表示遇到问题，执行了语法结构中的“操作”之后，是选择继续运行程序，还是选择退出，结束程序。

所以，这个条件处理语句的意思就是：当游标读到结果集的最后，没有记录了，设置操作完成标识为真，然后继续运行程序。

在存储过程的第5步，为了逐一处理每一条记录，我还使用了流程控制语句。

解决复杂问题不可能通过一个SQL语句完成，我们需要执行多个SQL操作。流程控制语句的作用就是控制存储过程中SQL语句的执行顺序，是我们完成复杂操作必不可少的一部分。下面我就给你具体讲解一下。

流程控制语句

MySQL的流程控制语句也只能用于存储程序。主要有3类。

跳转语句：ITERATE和LEAVE语句。
循环语句：LOOP、WHILE和REPEAT语句。
条件判断语句：IF语句和CASE语句。

接下来我依次讲解一下跳转语句、循环语句和条件判断语句。

跳转语句

ITERATE语句：只能用在循环语句内，表示重新开始循环。
LEAVE语句：可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。

循环语句

LOOP语句的语法结构是：

标签：LOOP
操作
END LOOP 标签;

关于这个语句，需要注意的是，LOOP循环不能自己结束，需要用跳转语句ITERATE或者LEAVE来进行控制。

WHILE语句的语法结构：

WHILE 条件 DO
操作
END WHILE;

WHILE循环通过判断条件是否为真来决定是否继续执行循环中的操作，你要注意一点，WHILE循环是先判断条件，再执行循环体中的操作。

REPEAT语句的语法结构：

REPEAT
操作
UNTIL 条件 END REPEAT；

REPEAT循环也是通过判断条件是否为真来决定是否继续执行循环内的操作的，与WHILE不同的是，REPEAT循环是先执行操作，后判断条件。

最后我来讲讲条件判断语句：IF语句和CASE语句。

条件判断语句

IF语句的语法结构是：

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

这里“[]”中的内容是可选的。IF语句的特点是，不同的表达式对应不同的操作。

CASE语句的语法结构是：

CASE 表达式
WHEN 值1 THEN 操作1
[WHEN 值2 THEN 操作2]……
[ELSE 操作N]
END CASE;

这里“[]”中的内容是可选的。CASE语句的特点是，表达式不同的值对应不同的操作。

到这里，我们处理进货单验收的存储过程就创建好了。现在，让我们来运行一下这个存储过程，看看能不能得到我们想要的结果：

mysql> CALL demo.mytest(1234);          -- 调用存储过程，验收单号是1234的进货单
Query OK, 0 rows affected (11.68 sec)   -- 执行成功了
 
mysql> select * from demo.inventory;    -- 查看库存，已经改过来了
+---------+------------+-------------+
| stockid | itemnumber | invquantity |
+---------+------------+-------------+
| 1 | 1 | 15.000 |
| 1 | 2 | 23.000 |
+---------+------------+-------------+
2 rows in set (0.00 sec)
 
mysql> select * from demo.goodsmaster;     -- 查看商品信息表，平均进价也改过来了
+------------+---------+-----------+---------------+------+------------+----------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice | avgimportprice |
+------------+---------+-----------+---------------+------+------------+----------------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 | 31.00 |
| 2 | 0002 | 笔 | NULL | 包 | 5.00 | 2.87 |
+------------+---------+-----------+---------------+------+------------+----------------+
2 rows in set (0.00 sec)

很显然，库存和平均价格都被正确地计算出来了。

最后，有个小问题要提醒你注意：如果一个操作要用到另外一个操作的结果，那我们一定不能搞错操作的顺序。比如，在刚刚的例子中，我是先计算平均价格，后消减库存数量，这就是因为，计算平均价格的时候会用到库存数量，如果先消减库存数量，平均价格的计算就会不准。

总结

这节课，我们学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，我还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。

游标是MySQL的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。但是游标会消耗系统资源，所以我建议你养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

思考题

假设我有一个数据表demo.test，具体信息如下所示：

你能自己写一个简单的存储过程，用游标来逐一处理一个数据表中的数据吗？

要求：编号为偶数的记录，myquant=myquant+1；编号是奇数的记录，myquant=myquant+2。

欢迎在留言区写下你的思考和答案，我们一起交流讨论。如果你觉得今天的内容对你有所帮助，欢迎你把它分享给你的朋友或同事，我们下节课见。

精选留言

朱晓峰

2021-05-17 17:52:27

你好，我是朱晓峰，下面我就来公布一下上节课思考题的答案：

上节课，我们学习了存储过程。下面是思考题的答案：

DELIMITER //
CREATE PROCEDURE demo.test(IN a INT,OUT b INT)
BEGIN
SET b = a + 1;
END
//
DELIMITER ;
虹炎

2021-05-18 20:37:41

这里没看懂：
DECLARE done INT DEFAULT FALSE; -- 用来控制循环结束
-- done 是int 类型，默认又是false??
REPEAT
...... 省略
UNTIL done END REPEAT;
-- repeat 内容执行一次后，这个done 应该是false ,就直接退出了循环呢？?

作者回复

MySQL没有boolean类型，把true处理为1，把false处理为0。

UNTIL done，表示一直到done为true才退出。

2021-05-21 12:02:01
彭彬

2021-09-22 15:37:55

MySQL的语法变态到了极点，从Oracle中取一点，从SQL Server中取一点，要把学生搞晕

作者回复

？

2021-11-11 17:21:31
SevenMonths

2021-09-01 22:41:25

在实际开发中没用过游标，实际业务场景用过的小伙伴，分享下呗？
1、什么场景下使用的？
2、效果怎么样？
lesserror

2021-04-17 00:21:59

游标这块儿知识比较模糊，以后如果要使用游标，看这一讲基本够了，老师讲的很细致。以下是简单的总结：

1. 游标只能在存储程序内使用，存储程序包括存储过程和存储函数。
2. 游标为逐条读取结果集中的数据，提供了完美的解决方案。因为不需要后端语言写循环，造成与服务器端MySQL程序产生多次交互。
3. 游标用完就要马上关闭，避免造成性能损耗。

作者回复

对的

2021-05-20 18:19:59
Judy

2022-05-10 09:55:29

老师没有讲数据库的锁机制吗？:(

作者回复

这个议题，超出了这门课的涉及范围，在丁奇老师的《MySQL实战45讲》中，有详细论述，建议您订阅。

2022-11-07 16:51:10
孙念池

2022-02-08 14:06:10

游标的场景是逐条处理，那么用程序写个循环也能实现同样效果吧，只是比起用sql的游标安全性低？效率低？

作者回复

我理解你的意思对比存储程序和程序，我的观点是，存储程序的运行效率更高一些，安全性也更好一些，但是维护成本也更高。

2022-03-12 16:49:28
Geek_9a859a

2021-12-23 20:26:37

delimiter //
create procedure demo.test()-- 创建存储过程
begin
declare myid int;
declare done int default 1;-- 用于循环中的条件判断如果done为1则继续循环，为0则退出循环

declare cursor_test cursor for-- 定义游标，结果集为test16表中的所有Id
select Id
from demo.test16;

declare continue handler for not found set done = 0;-- 异常处理，当循环中fetch不到下一条数据时修改done的值退出循环
open cursor_test;-- 打开游标
fetch cursor_test into myid;-- 获取一条数据

while done-- 进入循环处理数据
do
if myid % 2 = 1 then update demo.test16 set Myquant = Myquant + 2 where Id = myid;-- 当myid为奇数时
else update demo.test16 set Myquant = Myquant + 1 where Id = myid;-- 当myid为偶数时
end if;-- 结束条件判断
fetch cursor_test into myid;-- 获取下一条信息
end while;
close cursor_test;-- 关闭游标
end
//
delimiter ;

作者回复

请参考思考题答案

2022-01-16 13:00:24
大聖

2021-08-02 15:31:08

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_cursor`()
begin
declare quant int;
declare my_id int;
declare done int default FALSE;
declare test_cursor cursor for
select id, myquant from demo.test;
declare continue handler for not found set done = true;
open test_cursor;
repeat
fetch test_cursor into my_id, quant;
if my_id % 2 = 0 then
select quant as debug, my_id as id;
update demo.test set myquant=quant + 1 where id = my_id;
else
update demo.test set myquant=quant + 2 where id = my_id;

end if;
until done end repeat;
close test_cursor;
end

作者回复

请参考思考题答案

2021-09-06 10:29:19
凯

2021-06-13 19:25:00

-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 条件处理语句
->
-> OPEN cursor_importdata; -- 打开游标
-> FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice; -- 读入第一条记录
-> REPEAT

打开游标，不能放到DECLARE CONTINUE HANDLER FOR ... 之前么？放到之前就报错。

作者回复

在存储程序中，定义语句要放在最前面，然后才可以执行打开光标的操作。

2021-07-08 12:10:09
giteebravo

2021-04-16 08:34:14

游标适用于需要对集合中的行进行单独处理的场景。

作者回复

是的

2021-05-20 18:19:36
giteebravo

2021-04-15 08:34:49

工作中已经在使用 function 来创建表、修改字段及返回自增 id 等。

本节课，需要重点理解进货单的验收逻辑，才能很好的掌握游标、流程控制、条件处理等内容。

课后思考题简单有趣。

最后，除了文稿中演示的例子外，还有哪些场景适合使用游标呢？

作者回复

只要是需要对结果集中的记录进行逐条处理的场合，比如处理学生期末考试成绩、处理每天客户的订单数据等等。

2021-05-20 18:13:46

16 | 游标：对于数据集中的记录，该怎么逐条处理？