在 MySQL 中，游标（Cursor）是一种用于遍历和操作结果集的数据库对象。游标可以在存储过程或函数中使用，允许逐行处理查询结果。
使用游标的步骤如下：
（1）声明游标：使用 DECLARE 语句声明游标；
（2）打开游标：使用 OPEN 语句打开游标，将查询结果集绑定到游标上；
（3）检索数据：使用 FETCH 语句从游标中检索一行数据，并将其赋值给变量；
（4）处理数据：在游标的每次迭代中，可以对检索到的数据进行处理，如对数据进行计算、更新表中的数据等。
（5）关闭游标：使用 CLOSE 语句关闭游标，释放与游标相关的资源。
注意：MySQL 只允许我们根据 SELECT 语句从头到尾获取结果集中的每一条记录。我们不能从最后一行到第一行提取，也不能直接跳转到结果集中的特定行。
使用 DECLARE 语句定义游标，语法如下：
DECLARE cursor_name CURSOR FOR SELECT_statement;
其中，cursor_name 为游标名称。
注意，游标的声明必须在所有变量声明之后。在变量声明之前声明游标会产生错误 1337，例如：
mysql> CREATE PROCEDURE bad_cursor() BEGIN DECLARE c CURSOR FOR SELECT * from departments; DECLARE i INT; END// 1337 - Variable or condition declaration after cursor or handler declaration
游标总是与 SELECT 语句相关联；下例显示了一个从客户表中检索某些列的简单游标声明。如：
DECLARE cursor1 CURSOR FOR SELECT id,name FROM `user`;
游标可以在 WHERE 子句中引用存储过程变量，也可以在列表列中引用存储过程变量。游标在 WHERE 子句和 SELECT 列表中都引用了存储过程参数。当游标打开时，它将使用参数变量的值来决定返回哪些记录。
CREATE PROCEDURE getUser(user_id INT) BEGIN -- 声明两个变量 DECLARE id INT; DECLARE username VARCHAR(50); -- 声明一个游标 -- 游标的 SELECT 语句中使用了参数 user_id DECLARE user_cursor CURSOR FOR SELECT user_id, id, name from `user` where id=user_id; END
MySQL 存储程序语言支持三种语句，用于对游标执行操作：
初始化游标的结果集。在从游标中获取任何记录之前，我们必须先打开游标。OPEN 语句的语法非常简单：
OPEN cursor_name;
其中，cursor_name 为游标名称。
从光标处读取下一行，并将光标 “指针” 移动到结果集中的下一行。语法如下：
FETCH cursor_name INTO variable list;
其中，cursor_name 为游标名称。variable list 为变量列表，变量列表必须与游标声明中包含的 SELECT 语句返回的每一列包含一个兼容数据类型的变量。
关闭游标并释放与游标相关的内存。该语句的语法为：
CLOSE cursor_name;
其中，cursor_name 为游标名称。当我们从游标中获取完数据后，或者在更改了影响游标结果集的变量后需要再次打开游标时，我们应该先关闭游标。
下面演示游标最基本的用法，即：
（1）打开游标
（2）获取一条记录
（3）然后关闭游标
这在逻辑上等同于带有 INTO 子句的简单 SELECT。
示例：从游标中获取一条记录
-- 打开游标 OPEN cursor1; -- 获取一条记录 FETCH cursor1 INTO l_customer_name, l_contact_surname, l_contact_firstname; -- 关闭游标 CLOSE cursor1;
游标最常见的处理方式是获取游标的 SELECT 语句所标识的每一条记录，对获取的数据执行一个或多个操作，然后在获取最后一条记录后关闭游标。
示例：展示了如何声明并打开游标，然后在一个循环中从游标中获取记录，最后关闭游标。
CREATE PROCEDURE demo(arg_user_id INT) BEGIN -- 声明变量 DECLARE user_id INT; DECLARE user_name VARCHAR(50); -- 声明游标 DECLARE user_cursor CURSOR FOR SELECT * FROM `user` WHERE id=arg_user_id; -- 打开游标 OPEN user_cursor; -- 获取一条数据 FETCH user_cursor INTO user_id, user_name; -- 输出数据 SELECT user_id, user_name; -- 关闭游标 CLOSE user_cursor; END
调用存储过程：
mysql> call demo(2); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 2 | Helen | +---------+-----------+ 1 row in set (0.05 sec) Query OK, 0 rows affected (0.00 sec)
虽然这段代码看起来合理而完整，但还是存在一个问题，如果没有 id 等于 arg_user_id 的用户信息，此时使用 FETCH 语句 MySQL 将引发 “1329 - No data” 错误。例如：
mysql> call demo(-1); 1329 - No data - zero rows fetched, selected, or processed
为了避免出现这种错误，我们需要声明了一个错误处理程序，它将捕捉 “1329 - No data” 错误并设置一个标志。然后，我们根据该标志判断是否获取到数据。例如：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE;
当出现 “1329 - No data” 错误时，该处理程序会指示 MySQL 做两件事：
（1）将 “not_found” 的值设为 TRUE。
（2）允许程序继续执行。
现在，我们的程序可以检查 not_found 的值。如果它被设置为 TRUE，那么我们就知道没有数据，给出对应提示即可。例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(arg_user_id INT)
BEGIN
-- 声明变量
DECLARE user_id INT;
DECLARE user_name VARCHAR(50);
DECLARE not_found INT DEFAULT FALSE;
-- 声明游标
DECLARE user_cursor CURSOR FOR
SELECT * FROM `user` WHERE id=arg_user_id;
-- 声明处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE;
-- 打开游标
OPEN user_cursor;
-- 获取一条数据
FETCH user_cursor INTO user_id, user_name;
-- 输出数据
IF not_found THEN
SELECT CONCAT('没有找到用户ID=', arg_user_id, '的数据') as message;
ELSE
SELECT user_id, user_name;
END IF;
-- 关闭游标
CLOSE user_cursor;
END调用存储过程，输出如下：
mysql> call demo(-1); +-------------------------+ | message | +-------------------------+ | 没有找到用户ID=-1的数据 | +-------------------------+ 1 row in set (0.05 sec) Query OK, 0 rows affected (0.00 sec)
我们可以使用三种循环结构（简单循环、WHILE 循环和 REPEAT UNTIL 循环）中的任意一种来遍历游标返回的记录。
下面示例使用最简单的结构 LOOP-LEAVE-END LOOP 迭代游标：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE user_id INT; DECLARE user_name VARCHAR(50); -- 声明游标 DECLARE user_cursor CURSOR FOR SELECT * FROM `user`; -- 打开游标 OPEN user_cursor; -- 获取一条数据 myLoop:LOOP -- 获取一行数据 FETCH user_cursor INTO user_id, user_name; -- 输出数据 SELECT user_id, user_name; END LOOP myLoop; -- 关闭游标 CLOSE user_cursor; -- 输出结束信息 SELECT 'FINISHED.' AS message; END
上面示例的逻辑很简单：打开游标，然后迭代获取记录。调用存储过程输出如下：
mysql> call demo(); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Tom | +---------+-----------+ 1 row in set (0.00 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 2 | Helen | +---------+-----------+ 1 row in set (0.01 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 3 | Jemo | +---------+-----------+ 1 row in set (0.01 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 4 | Bill | +---------+-----------+ 1 row in set (0.01 sec) ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
上面虽然正确输出了用户表的信息，但是最后一行确抛出了 “ERROR 1329 (02000): No data” 错误信息，则是因为 FETCH 到游标最后没有数据时再次去 FETCH 抛出的。这将导致以后的循环后续的语句都不会执行，如 “SELECT 'FINISHED.' AS message;” 语句就没有执行。
为了解决这个问题，我们可以试图声明 “ERROR 1329 (02000)” 错误的处理器，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE user_id INT; DECLARE user_name VARCHAR(50); DECLARE not_found INT DEFAULT FALSE; -- 声明游标 DECLARE user_cursor CURSOR FOR SELECT * FROM `user`; -- 声明处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE; -- 打开游标 OPEN user_cursor; -- 获取一条数据 -- 重置状态，推荐这么做 SET not_found = FALSE; myLoop:LOOP FETCH user_cursor INTO user_id, user_name; -- 如果我们试图获取超过结果集末尾的记录，处理程序会将 not_found 设置为 TRUE IF not_found THEN -- 如果 not_found 为 TRUE，则退出循环 LEAVE myLoop; END IF; SELECT user_id, user_name; END LOOP myLoop; -- 重置状态 SET not_found = FALSE; -- 关闭游标 CLOSE user_cursor; SELECT 'FINISHED.' AS message; END
调用存储过程输出如下：
mysql> call demo(); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Tom | +---------+-----------+ 1 row in set (0.05 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 2 | Helen | +---------+-----------+ 1 row in set (0.12 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 3 | Jemo | +---------+-----------+ 1 row in set (0.19 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 4 | Bill | +---------+-----------+ 1 row in set (0.25 sec) +-----------+ | message | +-----------+ | FINISHED. | +-----------+ 1 row in set (0.33 sec) Query OK, 0 rows affected (0.00 sec)
WHILE 循环对于程序员来说非常熟悉，因此似乎是构建游标循环的自然选择。例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE user_id INT; DECLARE user_name VARCHAR(50); DECLARE not_found INT DEFAULT FALSE; -- 声明游标 DECLARE user_cursor CURSOR FOR SELECT * FROM `user`; -- 声明处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE; -- 打开游标 OPEN user_cursor; -- 获取一条数据 -- 重置状态，推荐这么做 SET not_found = FALSE; myLoop:WHILE NOT not_found DO FETCH user_cursor INTO user_id, user_name; IF NOT not_found THEN SELECT user_id, user_name; END IF; END WHILE myLoop; -- 重置状态 SET not_found = FALSE; -- 关闭游标 CLOSE user_cursor; SELECT 'FINISHED.' AS message; END
调用存储过程输出如下：
mysql> call demo(); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Tom | +---------+-----------+ 1 row in set (0.02 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 2 | Helen | +---------+-----------+ 1 row in set (0.05 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 3 | Jemo | +---------+-----------+ 1 row in set (0.08 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 4 | Bill | +---------+-----------+ 1 row in set (0.12 sec) +-----------+ | message | +-----------+ | FINISHED. | +-----------+ 1 row in set (0.16 sec) Query OK, 0 rows affected (0.00 sec)
但实际上，你很可能会发现 REPEAT UNTIL 循环比 WHILE 循环更适合游标循环的结构。REPEAT 至少执行一次主体。在游标处理过程中，我们通常希望在检查游标结果集是否处理完毕之前，至少执行一次取回操作。因此，使用 REPEAT UNTIL 循环可以写出更可读的代码，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE user_id INT; DECLARE user_name VARCHAR(50); DECLARE not_found INT DEFAULT FALSE; -- 声明游标 DECLARE user_cursor CURSOR FOR SELECT * FROM `user`; -- 声明处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found=TRUE; -- 打开游标 OPEN user_cursor; -- 获取一条数据 -- 重置状态，推荐这么做 SET not_found = FALSE; REPEAT -- 迭代一行数据 FETCH user_cursor INTO user_id, user_name; SELECT user_id, user_name; -- 判断 not_found 是否为 TRUE，如果是则终止循环 -- 注意：UNTIL 末尾没有分号 UNTIL not_found END REPEAT; -- 重置状态 SET not_found = FALSE; -- 关闭游标 CLOSE user_cursor; SELECT 'FINISHED.' AS message; END
调用存储过程输出如下：
mysql> call demo(); +---------+-----------+ | user_id | user_name | +---------+-----------+ | 1 | Tom | +---------+-----------+ 1 row in set (0.02 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 2 | Helen | +---------+-----------+ 1 row in set (0.06 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 3 | Jemo | +---------+-----------+ 1 row in set (0.10 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 4 | Bill | +---------+-----------+ 1 row in set (0.14 sec) +---------+-----------+ | user_id | user_name | +---------+-----------+ | 4 | Bill | +---------+-----------+ 1 row in set (0.18 sec) +-----------+ | message | +-----------+ | FINISHED. | +-----------+ 1 row in set (0.24 sec) Query OK, 0 rows affected (0.00 sec)
嵌套游标循环的情况并不少见。例如，一个循环可能会检索感兴趣的客户列表，而内部循环则会检索这些客户的所有订单。与这种嵌套有关的最重要的问题是，当任何一个游标完成时，NOT FOUND 处理程序变量都将被设置，因此需要非常小心，以确保 NOT FOUND 条件不会导致两个游标都关闭。
例如： 一个（有缺陷的）嵌套游标循环
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`()
BEGIN
-- 部门ID
DECLARE l_department_id INT;
-- 员工ID
DECLARE l_employee_id INT;
-- 部门员工数
DECLARE l_emp_count INT DEFAULT 0;
DECLARE l_done INT DEFAULT 0;
-- 定义部门游标
DECLARE dept_csr cursor FOR
SELECT department_id FROM departments;
-- 定义员工游标
DECLARE emp_csr cursor FOR
SELECT employee_id FROM employees
WHERE department_id=l_department_id;
-- 定义错误处理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
-- 迭代部门
OPEN dept_csr;
dept_loop: LOOP
FETCH dept_csr into l_department_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
-- 当内循环结束后，l_done 设置为 1，导致外循环意外终止
LEAVE dept_loop;
END IF;
-- 迭代员工
OPEN emp_csr;
SET l_emp_count=0;
emp_loop: LOOP
FETCH emp_csr INTO l_employee_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
LEAVE emp_loop;
END IF;
SET l_emp_count=l_emp_count+1;
END LOOP;
CLOSE emp_csr;
SELECT CONCAT('Department ',l_department_id,' has ', l_emp_count,' employees') as message;
END LOOP dept_loop;
CLOSE dept_csr;
END调用存储过程，输出如下：
mysql> call demo(); +------------------------------+ | message | +------------------------------+ | Department 1 has 2 employees | +------------------------------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.00 sec)
上面存储过程包含一个微妙的错误。当通过游标 emp_csr 的第一个 “内” 循环完成后，l_done 的值被设置为 1。因此，在通过 dept_csr 的 “外” 循环的下一次迭代中，l_done 的值仍然被设置为 1，外循环无意中被终止。因此，我们只能处理一个部门。对于这个问题，有两种可能的解决方案：其中比较简单的是在每个循环结束时重置 “l_done” 变量，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`()
BEGIN
-- 部门ID
DECLARE l_department_id INT;
-- 员工ID
DECLARE l_employee_id INT;
-- 部门员工数
DECLARE l_emp_count INT DEFAULT 0;
DECLARE l_done INT DEFAULT 0;
-- 定义部门游标
DECLARE dept_csr cursor FOR
SELECT department_id FROM departments;
-- 定义员工游标
DECLARE emp_csr cursor FOR
SELECT employee_id FROM employees
WHERE department_id=l_department_id;
-- 定义错误处理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
-- 迭代部门
OPEN dept_csr;
dept_loop: LOOP
FETCH dept_csr into l_department_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
LEAVE dept_loop;
END IF;
-- 迭代员工
OPEN emp_csr;
SET l_emp_count=0;
emp_loop: LOOP
FETCH emp_csr INTO l_employee_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
LEAVE emp_loop;
END IF;
SET l_emp_count=l_emp_count+1;
END LOOP;
CLOSE emp_csr;
-- 关键位置，重置 l_done 的值
SET l_done=0;
SELECT CONCAT('Department ',l_department_id,' has ', l_emp_count,' employees') as message;
END LOOP dept_loop;
CLOSE dept_csr;
END我们一旦使用了 “l_done” 变量，重置该变量的值始终是一种好的做法，这样就不会影响后续的游标迭代。
在终止游标循环后，一定要重置由 NOT FOUND 处理程序设置的 “l_done” 变量，否则可能会导致后续或嵌套的游标循环提前终止。
一个稍微复杂但可以说更稳健的解决方案是为每个游标提供自己的处理程序。由于在任何特定代码块中只能有一个 NOT FOUND 处理程序处于活动状态，因此只能通过将每个游标封装在自己的代码块中来实现。例如，我们可以将游标放在自己的代码块中，并使用自己的 NOT FOUND 处理程序，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`()
BEGIN
-- 部门ID
DECLARE l_department_id INT;
-- 员工ID
DECLARE l_employee_id INT;
-- 部门员工数
DECLARE l_emp_count INT DEFAULT 0;
DECLARE l_done INT DEFAULT 0;
-- 定义部门游标
DECLARE dept_csr cursor FOR
SELECT department_id FROM departments;
-- 定义员工游标
DECLARE emp_csr cursor FOR
SELECT employee_id FROM employees
WHERE department_id=l_department_id;
-- 定义错误处理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
-- 迭代部门（外循环）
OPEN dept_csr;
dept_loop: LOOP
FETCH dept_csr into l_department_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
LEAVE dept_loop;
END IF;
-- 迭代员工（内循环），重新定义一个代码块
sales_block: BEGIN
-- 为内循环定义错误处理器
DECLARE l_done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
OPEN emp_csr;
SET l_emp_count=0;
emp_loop: LOOP
FETCH emp_csr INTO l_employee_id;
IF l_done=1 THEN
-- 如果游标没有数据，则终止循环
LEAVE emp_loop;
END IF;
SET l_emp_count=l_emp_count+1;
END LOOP;
CLOSE emp_csr;
SET l_done=0; -- 重置 l_done 变量
SELECT CONCAT('Department ',l_department_id,' has ', l_emp_count,' employees') as message;
END sales_block;
END LOOP dept_loop;
CLOSE dept_csr;
SET l_done=0; -- 重置 l_done 变量
END调用存储过程，输出如下：
mysql> call demo(); +------------------------------+ | message | +------------------------------+ | Department 1 has 2 employees | +------------------------------+ 1 row in set (0.02 sec) +------------------------------+ | message | +------------------------------+ | Department 2 has 2 employees | +------------------------------+ 1 row in set (0.06 sec) Query OK, 0 rows affected (0.00 sec)
请注意，我们现在为每个游标都设置了一个单独的 “l_done” 变量，这样就消除了关闭一个游标影响另一个游标状态的可能性。不过也请注意，在完成每个游标循环后，我们仍然会重置 “l_done” 变量，这仍然是我们强烈建议的，因为你可能仍然希望在同一程序块中重新打开一个游标。
游标语句必须以 OPEN-FETCH-CLOSE 顺序出现，该序列的任何变化都将导致运行时错误。如果尝试从一个未打开的游标中进行 CLOSE 或 FETCH 操作，就会出现游标未打开的错误，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE x INT DEFAULT 0; -- 声明游标 DECLARE c cursor for select 1 from departments; -- 关闭游标，游标未打卡 CLOSE c; END
调用存储过程，输出如下：
mysql> call demo(); 1326 - Cursor is not open
如果试图打开一个已经打开的游标时，会出现 “游标已打开” 错误，例如：
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`() BEGIN -- 声明变量 DECLARE x INT DEFAULT 0; -- 声明游标 DECLARE c cursor for select 1 from departments; -- 打开游标 OPEN c; -- 重新打开游标 OPEN c; END
调用存储过程，输出如下：
mysql> call demo(); 1325 - Cursor is already open
川公网安备51010802032098
