MySQL游标与流程控制全解析:定义、使用及技巧
以下内容将从概念与语法入手,系统地讲解 MySQL 中游标(Cursor)与流程控制(Flow Control)的使用方法与技巧,并配以丰富的代码示例、ASCII 图解与详细说明,帮助你快速掌握在存储过程(Stored Procedure)或存储函数(Stored Function)中使用游标与流程控制的能力。建议边看边在 MySQL 沙箱环境中实践,加深理解。
1. 概述
在 MySQL 中,当我们需要对查询结果中的多行数据逐行遍历并进行复杂处理时,单纯的 SQL 语句往往无法满足需求。此时,就需要借助 游标(Cursor) 将结果集以“指针”的方式封装到存储程序中,配合 流程控制(Flow Control) 语句(如 LOOP
、WHILE
、IF
、CASE
、LEAVE
、ITERATE
等)来实现逐行处理、条件分支、循环跳出等逻辑。
- 游标(Cursor):类似于编程语言中的迭代器,用来保存一条或多条查询结果,通过
OPEN
、FETCH
、CLOSE
等操作让我们在存储过程里逐行取出数据。 - 流程控制(Flow Control):MySQL 存储程序内置的一组语法,包括条件判断(
IF…THEN…ELSE
、CASE
)、循环结构(LOOP
、WHILE
、REPEAT
)与跳转控制(LEAVE
、ITERATE
等),用来实现存储过程/函数中的分支与循环逻辑。
通过对二者的结合,我们可以在 MySQL 层面实现下面这些场景:
- 逐行读取查询结果并插入/更新/删除其他表(如统计、数据同步等)。
- 当查询到特定条件时跳出循环或跳到下一条,实现复杂的业务规则。
- 根据游标字段判断分支逻辑,如根据某列值进行分类处理。
- 处理分页数据,例如批量归档、拆分大表时逐页操作。
下面将循序渐进地介绍游标与流程控制的核心概念、语法、使用示例与最佳实践。
2. 游标基础
2.1 游标概念与生命周期
游标(Cursor) 本质上是一个指向查询结果集(Result Set)的指针。通过在存储程序中声明游标后,可以按以下步骤使用:
- DECLARE CURSOR:声明游标,指定要执行的
SELECT
语句。 - OPEN:打开游标,将查询结果集装载到内存中(或按需读取)。
- FETCH:从游标返回一行(或一列)数据到变量。
- REPEAT FETCH:重复
FETCH
直到游标到末尾。 - CLOSE:关闭游标,释放资源。
- DECLARE CURSOR:声明游标,指定要执行的
生命周期示意图(ASCII)
+--------------------+ | 存储过程开始 | | | | 1. DECLARE 游标 | | 2. OPEN 游标 | | | | ┌───────────┐ | | │ 游标结果集 │ | | └───────────┘ | | ↓ FETCH 1 行 | | ↓ FETCH 2 行 | | … | | ↓ FETCH N 行 | | 3. CLOSE 游标 | | | | 存储过程结束 | +--------------------+
FETCH
直到条件变量NOT FOUND
,即没有更多行可取时跳出循环。
2.2 声明游标的基本语法
在 MySQL 存储程序(PROCEDURE
或 FUNCTION
)中,游标的声明必须在所有变量(DECLARE var_name …
)、条件处理器(DECLARE CONTINUE HANDLER …
)之后,且在第一个可执行语句(如 SET
、SELECT
、INSERT
等)之前。
语法格式:
DECLARE cursor_name CURSOR FOR select_statement;
cursor_name
:游标名称,自定义标识。select_statement
:任意合法的SELECT
语句,用来生成游标结果集。
注意事项
声明位置:所有
DECLARE
(包括变量、游标、条件处理器)必须出现在存储程序的最开始部分,且顺序为:
DECLARATION 部分:
DECLARE var1, var2, … ; DECLARE done_flag INT DEFAULT 0; -- 用作游标结束标志 DECLARE cur_name CURSOR FOR SELECT …; -- 游标声明 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1; -- “无更多行”时处理
- 可执行语句部分:即在所有
DECLARE
后面才能写OPEN cursor_name;
、FETCH cursor_name INTO …;
等。条件处理器(Handler):
最常见的是
NOT FOUND
处理器,用于捕获FETCH
到末尾时的错误标志。常用写法:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;
当游标超出结果集时,MySQL 会触发
NOT FOUND
条件。如果我们不声明处理器,就会导致存储过程报错中断。- 游标只能在存储过程/函数内使用,不能在普通 SQL 会话里直接使用
DECLARE CURSOR
。
下面先演示一个简单存储过程,说明游标声明与基本用法。
3. 单游标示例:逐行读取并打印
假设有一张名为 employees
的表,结构如下:
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'HR', 8000.00),
('Bob', 'Engineering', 12000.00),
('Cathy', 'Sales', 9500.00),
('David', 'Engineering', 11500.00),
('Eve', 'HR', 7800.00);
3.1 存储过程模板
我们要写一个存储过程,以游标方式逐行读取 employees
表的每行数据,打印到客户端(通过 SELECT
模拟“打印”),并在读取到特定条件时跳出循环。
DELIMITER //
CREATE PROCEDURE print_all_employees()
BEGIN
-- 1. 变量声明
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_dept VARCHAR(50);
DECLARE v_sal DECIMAL(10,2);
DECLARE done_flag INT DEFAULT 0; -- 标志是否到末尾
-- 2. 游标声明:根据 employees 表查询需要读取的字段
DECLARE emp_cursor CURSOR FOR
SELECT id, name, department, salary
FROM employees
ORDER BY id;
-- 3. 条件处理器:当游标读取到末尾时,将 done_flag 设为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;
-- 4. 打开游标
OPEN emp_cursor;
-- 5. 循环读取
read_loop: LOOP
-- 5.1 取一行
FETCH emp_cursor
INTO v_id, v_name, v_dept, v_sal;
-- 5.2 检查是否到末尾
IF done_flag = 1 THEN
LEAVE read_loop; -- 跳出循环
END IF;
-- 5.3 在客户端打印读取到的值(用 SELECT 语句演示)
SELECT
CONCAT('ID=', v_id, ', Name=', v_name,
', Dept=', v_dept, ', Salary=', v_sal) AS info;
-- 5.4 如遇到特定条件可提前退出(例如 v_sal > 11000)
IF v_sal > 11000 THEN
SELECT CONCAT('High salary detected (', v_name, '), break.') AS alert_msg;
LEAVE read_loop;
END IF;
END LOOP read_loop;
-- 6. 关闭游标
CLOSE emp_cursor;
END;
//
DELIMITER ;
3.1.1 关键点详解
- 变量声明(
DECLARE v_id INT;
等):用来接收FETCH
出来的各列值。 done_flag
标志:常用来判断游标是否到末尾,当没有更多行时,MySQL 会触发NOT FOUND
条件,执行对应的CONTINUE HANDLER
设置done_flag = 1
。游标声明:
DECLARE emp_cursor CURSOR FOR SELECT id, name, department, salary FROM employees ORDER BY id;
- 这里指定了要遍历的查询结果集,结果会按
id
升序返回。
- 这里指定了要遍历的查询结果集,结果会按
条件处理器:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;
FOR NOT FOUND
:表示若之后的FETCH
没有可读取的行,则跳转到此处理器,将done_flag
置为 1,并让程序继续执行(CONTINUE
)。
打开游标:
OPEN emp_cursor;
这一步会执行
SELECT id, name, …
并将结果集保存到内部数据结构,等待调用FETCH
。LOOP … END LOOP
循环:read_loop: LOOP
:给循环一个标签read_loop
,以便后续使用LEAVE read_loop
跳出循环。FETCH emp_cursor INTO v_id, v_name, v_dept, v_sal;
:从游标取出一行数据,填充到四个变量中。- 检查结束条件:
IF done_flag = 1 THEN LEAVE read_loop; END IF;
,如果已经到末尾则跳出循环。 - 业务逻辑处理:这里通过
SELECT CONCAT(...) AS info;
将信息“打印”到客户端(真实场景可改成INSERT
或UPDATE
等操作)。 - 提前跳出:演示了当
v_sal > 11000
时,再次LEAVE read_loop
,直接退出遍历。
- 关闭游标:
CLOSE emp_cursor;
,释放相应资源。
3.2 测试与执行
CALL print_all_employees();
3.2.1 执行结果示例
假设 employees
表如下:
+----+-------+-------------+---------+
| id | name | department | salary |
+----+-------+-------------+---------+
| 1 | Alice | HR | 8000.00|
| 2 | Bob | Engineering | 12000.00|
| 3 | Cathy | Sales | 9500.00|
| 4 | David | Engineering | 11500.00|
| 5 | Eve | HR | 7800.00|
+----+-------+-------------+---------+
执行 CALL print_all_employees();
之后,会依次输出:
+----------------------------------------------+
| info |
+----------------------------------------------+
| ID=1, Name=Alice, Dept=HR, Salary=8000.00 |
+----------------------------------------------+
+----------------------------------------------+
| info |
+----------------------------------------------+
| ID=2, Name=Bob, Dept=Engineering, Salary=12000.00|
+----------------------------------------------+
+----------------------------------------------+
| High salary detected (Bob), break. |
+----------------------------------------------+
- 当读取到第二行(
Bob, salary=12000
)时,符合v_sal > 11000
条件,触发提前跳出的逻辑,因此后续记录(Cathy
等)不再处理。
4. 进一步演示:在游标中执行 DML 操作
上节示例只演示了“读取并打印”。实际业务场景往往需要在读取一行后进行修改/插入/删除等操作。例如:对 employees
表中所有 Engineering
部门员工的薪水进行一次调整,并将操作记录到日志表 salary_changes
。
4.1 表结构准备
-- 原employees表(与上节相同,假定已存在)
-- 额外创建日志表
CREATE TABLE salary_changes (
change_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
4.2 存储过程:遍历并更新
DELIMITER //
CREATE PROCEDURE increase_engineering_salaries()
BEGIN
-- 1. 变量声明
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_dept VARCHAR(50);
DECLARE v_sal DECIMAL(10,2);
DECLARE done_flag INT DEFAULT 0; -- 游标结束标志
-- 2. 声明游标:选出 Engineering 部门所有员工
DECLARE eng_cursor CURSOR FOR
SELECT id, name, department, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY id;
-- 3. NOT FOUND 处理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;
-- 4. 打开游标
OPEN eng_cursor;
-- 5. 循环读取
fetch_loop: LOOP
FETCH eng_cursor INTO v_id, v_name, v_dept, v_sal;
IF done_flag = 1 THEN
LEAVE fetch_loop;
END IF;
-- 5.1 计算新薪水:涨 10%
SET @new_salary = v_sal * 1.10;
-- 5.2 更新 employees 表
UPDATE employees
SET salary = @new_salary
WHERE id = v_id;
-- 5.3 插入日志表
INSERT INTO salary_changes (emp_id, old_salary, new_salary)
VALUES (v_id, v_sal, @new_salary);
END LOOP fetch_loop;
-- 6. 关闭游标
CLOSE eng_cursor;
END;
//
DELIMITER ;
4.2.1 说明与要点
DECLARE eng_cursor CURSOR FOR SELECT … WHERE department = 'Engineering'
:- 只遍历
Engineering
部门的员工。 - 有
ORDER BY id
保证处理顺序一致。
- 只遍历
更新与日志:
- 在
FETCH
拿到v_id、v_sal
后,用UPDATE employees …
修改薪水,再用INSERT INTO salary_changes …
写入操作日志。 - 注意这里使用了用户变量
@new_salary
,也可以直接用局部变量DECLARE v_new_sal DECIMAL(10,2); SET v_new_sal = v_sal * 1.10;
。
- 在
事务与并发:
- 如果同时有其他会话在操作
employees
表,需根据业务需要显式开启事务(START TRANSACTION; … COMMIT;
)并考虑隔离级别。 - 本示例未显示使用事务,但实际场景下,最好将更新与日志插入放在同一个事务中,确保一致性。
- 如果同时有其他会话在操作
4.3 执行示例
-- 假设初始employees:
+----+-------+-------------+---------+
| id | name | department | salary |
+----+-------+-------------+---------+
| 2 | Bob | Engineering | 12000.00|
| 4 | David | Engineering | 11500.00|
+----+-------+-------------+---------+
CALL increase_engineering_salaries();
-- 执行后,employees表:
+----+-------+-------------+---------+
| id | name | department | salary |
+----+-------+-------------+---------+
| 2 | Bob | Engineering | 13200.00| -- 12000 * 1.1
| 4 | David | Engineering | 12650.00| -- 11500 * 1.1
+----+-------+-------------+---------+
-- salary_changes 日志:
+-----------+--------+------------+------------+---------------------+
| change_id | emp_id | old_salary | new_salary | changed_at |
+-----------+--------+------------+------------+---------------------+
| 1 | 2 | 12000.00 | 13200.00 | 2025-06-07 17:10:05 |
| 2 | 4 | 11500.00 | 12650.00 | 2025-06-07 17:10:05 |
+-----------+--------+------------+------------+---------------------+
5. 多游标与嵌套游标
在某些场景,需要对多个结果集分别遍历,并且游标之间可能有关联;这时就要用到 多游标 或 嵌套游标。以下示例演示:先遍历部门表,再针对每个部门遍历该部门下的员工。
5.1 表结构示例
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
dept_id INT,
salary DECIMAL(10,2),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
INSERT INTO departments (dept_name) VALUES
('HR'), ('Engineering'), ('Sales');
INSERT INTO employees (name, dept_id, salary) VALUES
('Alice', 1, 8000.00),
('Eve', 1, 7800.00),
('Bob', 2, 12000.00),
('David', 2, 11500.00),
('Cathy', 3, 9500.00);
5.2 需求
- 遍历每个部门(
departments
表),打印部门名称。 - 对当前部门,再遍历该部门下的员工(
employees
表),打印员工信息。 - 结束后继续下一个部门。
5.3 存储过程示例:嵌套游标
DELIMITER //
CREATE PROCEDURE print_dept_emp()
BEGIN
-- 1. 声明部门游标相关变量
DECLARE v_dept_id INT;
DECLARE v_dept_name VARCHAR(50);
DECLARE dept_done INT DEFAULT 0;
-- 2. 声明员工游标相关变量
DECLARE v_emp_id INT;
DECLARE v_emp_name VARCHAR(50);
DECLARE v_emp_sal DECIMAL(10,2);
DECLARE emp_done INT DEFAULT 0;
-- 3. 声明部门游标
DECLARE dept_cursor CURSOR FOR
SELECT dept_id, dept_name
FROM departments
ORDER BY dept_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dept_done = 1;
-- 4. 打开部门游标
OPEN dept_cursor;
-- 5. 遍历部门
dept_loop: LOOP
FETCH dept_cursor INTO v_dept_id, v_dept_name;
IF dept_done = 1 THEN
LEAVE dept_loop;
END IF;
-- 打印部门信息
SELECT CONCAT('Department: [', v_dept_id, '] ', v_dept_name) AS dept_info;
-- 在当前部门下声明并打开员工游标
-- 5.1 每次进入新部门前先重置 emp_done 标志
SET emp_done = 0;
-- 5.2 命名空间中要先 DECLARE 处理器,再 DECLARE 游标
-- 所以要用一个子块(BEGIN…END)来隔离 emp_cursor
BEGIN
-- 声明员工游标的处理器(针对员工游标读取结束)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET emp_done = 1;
-- 声明员工游标:只遍历当前部门的员工
DECLARE emp_cursor CURSOR FOR
SELECT id, name, salary
FROM employees
WHERE dept_id = v_dept_id
ORDER BY id;
-- 打开员工游标
OPEN emp_cursor;
-- 遍历员工
emp_loop: LOOP
FETCH emp_cursor INTO v_emp_id, v_emp_name, v_emp_sal;
IF emp_done = 1 THEN
LEAVE emp_loop;
END IF;
-- 打印员工信息
SELECT CONCAT(' -> EmpID=', v_emp_id,
', Name=', v_emp_name,
', Salary=', v_emp_sal) AS emp_info;
END LOOP emp_loop;
-- 关闭员工游标
CLOSE emp_cursor;
END;
-- 继续下一个部门
END LOOP dept_loop;
-- 6. 关闭部门游标
CLOSE dept_cursor;
END;
//
DELIMITER ;
5.3.1 关键点与注意事项
嵌套声明位置
- 因为 MySQL 要求
DECLARE … HANDLER
与DECLARE … CURSOR
必须在存储程序局部“最前面”,在一个存储过程体内,如果想为第二个游标声明处理器和游标,就需要用一个新的块(BEGIN … END
)隔离。 - 外层的
dept_cursor
处于最外层块,内层的emp_cursor
则放在一个新的匿名块(BEGIN … END
)中。
- 因为 MySQL 要求
处理器隔离
- 外层使用
dept_done
,内层使用emp_done
,互不干扰。 - 如果不使用匿名子块,内层的
DECLARE CONTINUE HANDLER FOR NOT FOUND
会与外层冲突,导致逻辑混乱。
- 外层使用
CURSOR 作用域
emp_cursor
只在内层匿名块中有效,出了该块就会失效。每次循环进入一个新部门时,都会重新进入该匿名块,重新声明处理器和游标。
流程示意(ASCII)
+---------------------------------------+ | OPEN dept_cursor | | LOOP dept_loop: | | FETCH dept_cursor INTO v_dept_* | | IF dept_done=1 THEN LEAVE dept_loop | | PRINT 部门信息 | | | | BEGIN (匿名块,为 emp_cursor 做声明) | | SET emp_done = 0 | | DECLARE emp_cursor CURSOR FOR ... | | DECLARE handler FOR NOT FOUND ... | | OPEN emp_cursor | | LOOP emp_loop: | | FETCH emp_cursor INTO v_emp_* | | IF emp_done=1 THEN LEAVE emp_loop| | PRINT 员工信息 | | END LOOP emp_loop | | CLOSE emp_cursor | | END (匿名块结束) | | | | END LOOP dept_loop | | CLOSE dept_cursor | +---------------------------------------+
5.4 执行与结果示例
CALL print_dept_emp();
假设 departments
和 employees
表如前所示,执行结果类似:
+----------------------------------------+
| dept_info |
+----------------------------------------+
| Department: [1] HR |
+----------------------------------------+
+------------------------------+
| emp_info |
+------------------------------+
| -> EmpID=1, Name=Alice, Salary=8000.00 |
+------------------------------+
| -> EmpID=5, Name=Eve, Salary=7800.00 |
+------------------------------+
+----------------------------------------+
| dept_info |
+----------------------------------------+
| Department: [2] Engineering |
+----------------------------------------+
+------------------------------+
| emp_info |
+------------------------------+
| -> EmpID=2, Name=Bob, Salary=12000.00 |
+------------------------------+
| -> EmpID=4, Name=David, Salary=11500.00 |
+------------------------------+
+----------------------------------------+
| dept_info |
+----------------------------------------+
| Department: [3] Sales |
+----------------------------------------+
+------------------------------+
| emp_info |
+------------------------------+
| -> EmpID=3, Name=Cathy, Salary=9500.00 |
+------------------------------+
6. 流程控制详解
在前面的示例中,我们已经用到了 LOOP … END LOOP
、IF … THEN … END IF
、LEAVE
等流程控制语句。下面集中介绍 MySQL 存储程序中所有常见的流程控制要素,并以示例加以说明。
6.1 条件判断
6.1.1 IF…THEN…ELSEIF…ELSE…END IF
语法:
IF condition1 THEN statements1; [ELSEIF condition2 THEN statements2;] [ELSE statements3;] END IF;
示例:根据员工薪资等级打印不同信息。
DELIMITER // CREATE PROCEDURE salary_grade_check() BEGIN DECLARE v_id INT; DECLARE v_name VARCHAR(50); DECLARE v_sal DECIMAL(10,2); DECLARE done_flag INT DEFAULT 0; DECLARE emp_cur CURSOR FOR SELECT id, name, salary FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1; OPEN emp_cur; read_loop: LOOP FETCH emp_cur INTO v_id, v_name, v_sal; IF done_flag = 1 THEN LEAVE read_loop; END IF; IF v_sal >= 11000 THEN SELECT CONCAT(v_name, ' is high earner.') AS msg; ELSEIF v_sal BETWEEN 9000 AND 10999.99 THEN SELECT CONCAT(v_name, ' is mid-level earner.') AS msg; ELSE SELECT CONCAT(v_name, ' is low earner.') AS msg; END IF; END LOOP read_loop; CLOSE emp_cur; END; // DELIMITER ;
- 根据
v_sal
的范围,分别用不同分支打印提示。
- 根据
6.1.2 CASE…WHEN…THEN…ELSE…END CASE
语法:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END CASE;
示例:使用
CASE
将部门 ID 转为部门名称(假设在某些场合不想联表)。SELECT id, name, CASE department WHEN 'HR' THEN 'Human Resources' WHEN 'Engineering' THEN 'Engineering Dept' WHEN 'Sales' THEN 'Sales Dept' ELSE 'Unknown' END AS dept_full_name FROM employees;
在存储过程里赋值示例:
DELIMITER // CREATE PROCEDURE set_dept_code() BEGIN DECLARE v_id INT; DECLARE v_dept VARCHAR(50); DECLARE v_code INT; DECLARE done_flag INT DEFAULT 0; DECLARE emp_cur CURSOR FOR SELECT id, department FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1; OPEN emp_cur; label_loop: LOOP FETCH emp_cur INTO v_id, v_dept; IF done_flag = 1 THEN LEAVE label_loop; END IF; SET v_code = CASE WHEN v_dept = 'HR' THEN 10 WHEN v_dept = 'Engineering' THEN 20 WHEN v_dept = 'Sales' THEN 30 ELSE 0 END; -- 更新到表里,假设新增了一列 dept_code UPDATE employees SET department = CONCAT(v_dept, '(', v_code, ')') WHERE id = v_id; END LOOP label_loop; CLOSE emp_cur; END; // DELIMITER ;
CASE
结构等价于多个IF...ELSEIF
,在对单个字段赋值时更简洁。
6.2 循环结构
MySQL 中常见的循环结构有三种:LOOP
、WHILE
、REPEAT
。它们的差异与用法如下。
6.2.1 LOOP…END LOOP
语法:
[label:] LOOP statements; [LEAVE label;] [ITERATE label;] ... END LOOP [label];
- 需要配合标签
label
和LEAVE
、ITERATE
跳出或继续循环。 示例:下面例子在循环里
ITERATE
用于跳到下一次循环,LEAVE
用于跳出整个循环。DELIMITER // CREATE PROCEDURE loop_example() BEGIN DECLARE i INT DEFAULT 0; DECLARE max_i INT DEFAULT 10; loop_label: LOOP SET i = i + 1; IF i = 3 THEN -- 跳过当前循环(即不执行后续打印),直接进入下次循环 ITERATE loop_label; END IF; IF i = 8 THEN -- 提前跳出循环 LEAVE loop_label; END IF; SELECT CONCAT('Loop iteration: ', i) AS iter_msg; END LOOP loop_label; END; // DELIMITER ; -- 调用 CALL loop_example();
- 该存储过程会依次打印
1
、2
(跳过 3)、4
、5
、6
、7
,然后在i=8
时LEAVE
,循环结束。
- 该存储过程会依次打印
6.2.2 WHILE…DO…END WHILE
语法:
[label:] WHILE search_condition DO statements; [ITERATE label;] [LEAVE label;] ... END WHILE [label];
- 在进入循环体前会先判断
search_condition
,满足条件才执行循环体;不满足时直接跳出。 示例:计算 1 到 5 的累加和。
DELIMITER // CREATE PROCEDURE while_sum() BEGIN DECLARE i INT DEFAULT 1; DECLARE total INT DEFAULT 0; WHILE i <= 5 DO SET total = total + i; SET i = i + 1; END WHILE; SELECT CONCAT('Sum 1 to 5 = ', total) AS result; END; // DELIMITER ;
6.2.3 REPEAT…UNTIL…END REPEAT
语法:
[label:] REPEAT statements; [ITERATE label;] [LEAVE label;] ... UNTIL search_condition END REPEAT [label];
- 会先执行一次循环体,然后再判断
search_condition
,如果满足条件则退出,否则继续执行。 示例:与上一示例等价,但使用
REPEAT
。DELIMITER // CREATE PROCEDURE repeat_sum() BEGIN DECLARE i INT DEFAULT 1; DECLARE total INT DEFAULT 0; repeat_label: REPEAT SET total = total + i; SET i = i + 1; UNTIL i > 5 END REPEAT; SELECT CONCAT('Sum 1 to 5 = ', total) AS result; END; // DELIMITER ;
6.3 跳转控制:LEAVE 与 ITERATE
- LEAVE label:立即跳出标记为
label
的循环体,继续执行循环体外的第一个语句。 - ITERATE label:立即跳到标记为
label
的循环的下一次迭代,相当于continue
。
label1: LOOP
…
IF cond1 THEN
ITERATE label1; -- 跳过当前循环,进入下一次迭代
END IF;
IF cond2 THEN
LEAVE label1; -- 跳出循环体,执行 label1 之后的语句
END IF;
END LOOP label1;
7. 游标与流程控制综合示例
下面通过一个综合实例,将游标、IF、LOOP、WHILE、LEAVE、ITERATE 等多种流程控制技术结合,完成一个稍微复杂的任务:统计每个部门的全体员工薪水,并将结果写入一张统计表 dept_salary_totals
中。对于薪资总额超过一定阈值(如 > 20000)的部门,需要额外插入告警记录到表 dept_alerts
。
7.1 表结构准备
-- 原 employees 表(同上),字段: id, name, dept_id, salary
-- 部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
);
-- 部门薪资合计表
CREATE TABLE dept_salary_totals (
dept_id INT PRIMARY KEY,
total_salary DECIMAL(15,2),
calculated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 警告表:当总薪资超过阈值时,记录一条告警
CREATE TABLE dept_alerts (
alert_id INT PRIMARY KEY AUTO_INCREMENT,
dept_id INT,
total_salary DECIMAL(15,2),
alert_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
7.2 存储过程:逐部门统计并写入
DELIMITER //
CREATE PROCEDURE calculate_dept_salaries()
BEGIN
-- 1. 变量声明
DECLARE v_dept_id INT;
DECLARE v_dept_name VARCHAR(50);
DECLARE v_emp_id INT;
DECLARE v_emp_sal DECIMAL(10,2);
DECLARE dept_total DECIMAL(15,2);
DECLARE dept_done INT DEFAULT 0;
DECLARE emp_done INT DEFAULT 0;
-- 薪资阈值
DECLARE salary_threshold DECIMAL(15,2) DEFAULT 20000.00;
-- 2. 部门游标:遍历所有部门
DECLARE dept_cursor CURSOR FOR
SELECT dept_id, dept_name FROM departments;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dept_done = 1;
-- 3. 打开部门游标
OPEN dept_cursor;
dept_loop: LOOP
-- 3.1 取下一部门
FETCH dept_cursor INTO v_dept_id, v_dept_name;
IF dept_done = 1 THEN
LEAVE dept_loop;
END IF;
-- 3.2 初始化部门薪资汇总
SET dept_total = 0;
SET emp_done = 0;
-- 3.3 员工游标:遍历当前部门所有员工
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET emp_done = 1;
DECLARE emp_cursor CURSOR FOR
SELECT id, salary
FROM employees
WHERE dept_id = v_dept_id;
OPEN emp_cursor;
emp_loop: LOOP
FETCH emp_cursor INTO v_emp_id, v_emp_sal;
IF emp_done = 1 THEN
LEAVE emp_loop;
END IF;
-- 累加薪资
SET dept_total = dept_total + v_emp_sal;
END LOOP emp_loop;
CLOSE emp_cursor;
END;
-- 3.4 插入或更新 dept_salary_totals 表
-- 如果已有记录,则更新;否则插入。
IF EXISTS (SELECT 1 FROM dept_salary_totals WHERE dept_id = v_dept_id) THEN
UPDATE dept_salary_totals
SET total_salary = dept_total,
calculated_at = NOW()
WHERE dept_id = v_dept_id;
ELSE
INSERT INTO dept_salary_totals (dept_id, total_salary)
VALUES (v_dept_id, dept_total);
END IF;
-- 3.5 如果薪资总额超过阈值,插入告警表
IF dept_total > salary_threshold THEN
INSERT INTO dept_alerts (dept_id, total_salary)
VALUES (v_dept_id, dept_total);
END IF;
END LOOP dept_loop;
-- 4. 关闭部门游标
CLOSE dept_cursor;
END;
//
DELIMITER ;
7.2.1 解析与要点
两个游标的块级隔离
- 部门游标在最外层声明并打开。
- 针对每个部门,使用一个匿名块
BEGIN … END;
来声明与使用员工游标,确保DECLARE
顺序与作用域正确。
dept_total
累加- 在进入员工游标前,将
dept_total
置为 0。 - 每次
FETCH
得到v_emp_sal
后,用dept_total = dept_total + v_emp_sal
进行累加。
- 在进入员工游标前,将
INSERT … ON DUPLICATE KEY UPDATE
(可选优化)- 上例中用
IF EXISTS … UPDATE … ELSE INSERT
判断表中是否已有记录。 也可以直接用:
INSERT INTO dept_salary_totals (dept_id, total_salary) VALUES (v_dept_id, dept_total) ON DUPLICATE KEY UPDATE total_salary = dept_total, calculated_at = NOW();
这样写更简洁。
- 上例中用
阈值告警
- 当
dept_total
超过salary_threshold
时,插入dept_alerts
。 - 如果想避免重复插入同一部门多条告警,可在插入前先判断或使用唯一索引。
- 当
控制流程示意(ASCII)
+-------------------------------------------+ | OPEN dept_cursor | | dept_loop: LOOP | | FETCH dept_cursor INTO v_dept_* | | IF dept_done=1 THEN LEAVE dept_loop | | | | SET dept_total = 0 | | emp_done = 0 | | | | BEGIN (匿名块,用于员工游标) | | DECLARE emp_cursor FOR SELECT id,sal… | | DECLARE handler FOR NOT FOUND | | OPEN emp_cursor | | emp_loop: LOOP | | FETCH emp_cursor INTO v_emp_* | | IF emp_done=1 THEN LEAVE emp_loop | | SET dept_total = dept_total + v_emp_sal | | END LOOP emp_loop | | CLOSE emp_cursor | | END | | | | 插入/更新 dept_salary_totals | | IF dept_total > threshold THEN | | INSERT INTO dept_alerts | | END IF | | | | END LOOP dept_loop | | CLOSE dept_cursor | +-------------------------------------------+
8. 完整示例演练:分页处理大表
当表数据量非常大时,直接用游标一次性遍历会导致长时间锁表、占用资源。此时可以结合分页和游标的思路:先按 主键范围 或 LIMIT/OFFSET 分页,每页使用游标或直接 SELECT … INTO
批量处理,然后循环下一页,直到处理完所有数据。下面示例演示如何分批统计 employees
表的薪资总和,避免一次性加载整个表。
8.1 思路概要
- 假设
employees
表主键为id
。 - 每次从
last_id+1
开始,取出batch_size
条记录(如 1000 条)。 - 对当前批次执行统计(或其它处理)。
- 更新
last_id
为本批次的最大id
,重复步骤 2,直到没有更多记录。
8.2 存储过程示例
DELIMITER //
CREATE PROCEDURE batch_process_employees(batch_size INT)
BEGIN
DECLARE v_last_id INT DEFAULT 0;
DECLARE v_max_id INT;
DECLARE v_batch_total DECIMAL(15,2);
DECLARE rows_affected INT DEFAULT 1;
-- 1. 获取 employees 表最大 id
SELECT MAX(id) INTO v_max_id FROM employees;
-- 2. 如果表为空,直接返回
IF v_max_id IS NULL THEN
SELECT 'Table is empty.' AS msg;
LEAVE proc_end;
END IF;
-- 3. 分页循环:当 v_last_id < v_max_id 时继续
WHILE v_last_id < v_max_id DO
-- 使用子查询统计 id 在 (v_last_id, v_last_id+batch_size] 范围内的薪资总和
SELECT SUM(salary) INTO v_batch_total
FROM employees
WHERE id > v_last_id
AND id <= v_last_id + batch_size;
-- 输出本批次统计结果
SELECT CONCAT('Processed IDs (', v_last_id+1, ' to ', LEAST(v_last_id+batch_size, v_max_id),
'), Batch Salary Sum=', IFNULL(v_batch_total,0)) AS batch_info;
-- 更新 last_id
SET v_last_id = v_last_id + batch_size;
END WHILE;
proc_end: BEGIN END;
END;
//
DELIMITER ;
8.2.1 说明
batch_size
参数:由调用者指定每页大小。v_last_id
和v_max_id
:v_last_id
用于记录上一批次的最大id
,初始为 0。v_max_id
= 表中最大id
,用于确定循环终止条件。
WHILE v_last_id < v_max_id DO … END WHILE
:- 当
v_last_id
小于v_max_id
时继续。 - 每次统计
id
在(v_last_id, v_last_id + batch_size]
范围中的数据。 LEAST(v_last_id+batch_size, v_max_id)
用来避免最后一页超过最大值。
- 当
- 子查询
SUM(salary)
:一次性统计当前批次薪资和,无需显式游标遍历。 - 分页操作:若需要针对每条记录做更复杂操作,可以在子查询改为
DECLARE cursor FOR SELECT id, name, salary … LIMIT … OFFSET …
,再用游标逐条处理。
8.3 调用示例
CALL batch_process_employees(2);
假设 employees
表如下:
+----+-------+-------------+---------+
| id | name | department | salary |
+----+-------+-------------+---------+
| 1 | Alice | HR | 8000.00|
| 2 | Eve | HR | 7800.00|
| 3 | Bob | Engineering | 12000.00|
| 4 | David | Engineering | 11500.00|
| 5 | Cathy | Sales | 9500.00|
+----+-------+-------------+---------+
执行结果:
+--------------------------------------------------+
| batch_info |
+--------------------------------------------------+
| Processed IDs (1 to 2), Batch Salary Sum=15800.00|
+--------------------------------------------------+
+--------------------------------------------------+
| batch_info |
+--------------------------------------------------+
| Processed IDs (3 to 4), Batch Salary Sum=23500.00|
+--------------------------------------------------+
+--------------------------------------------------+
| batch_info |
+--------------------------------------------------+
| Processed IDs (5 to 5), Batch Salary Sum=9500.00 |
+--------------------------------------------------+
由于
batch_size=2
,共分三页:- IDs 1–2,总和 = 8000 + 7800 = 15800
- IDs 3–4,总和 = 12000 + 11500 = 23500
- IDs 5–5,总和 = 9500
9. 错误处理与注意事项
在编写带游标与流程控制的存储程序时,需要注意以下要点以保证正确性和性能。
9.1 条件处理器(Handler)与异常捕获
CONTINUE HANDLER FOR NOT FOUND
:- 必须与相应游标配合使用,检测
FETCH
到末尾时触发,将标志变量置为 1,让程序通过判断跳出循环。 - 如果不声明该处理器,
FETCH
到末尾会导致存储过程报错并中止。
- 必须与相应游标配合使用,检测
其他常见处理器:
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- 遇到任何 SQL 错误(如除 0、类型转换错误等)都会执行这里 ROLLBACK; SELECT 'An SQL error occurred' AS err_msg; END;
EXIT HANDLER
:触发后退出整个存储程序块,不会继续。CONTINUE HANDLER
:触发后仅执行处理体,然后继续后续代码。
9.2 游标性能与资源
- 游标会占用服务器资源,尤其是针对大结果集时,可能会一次性将整个结果载入内存。
- 对于超大表,最好结合分页或
LIMIT OFFSET
,每次处理一小批数据,避免一次性打开一个巨大的游标。 - 在一个存储程序中同时打开过多游标会导致资源紧张,应合理控制并且及时
CLOSE
。
9.3 避免死循环
- 在
LOOP
、WHILE
、REPEAT
中,一定要保证循环的终止条件能够被正确触发,否则会导致死循环。 - 对于游标循环,务必在
FETCH
后检查done_flag
,并在适当位置调用LEAVE
。
9.4 变量作用域
- MySQL 存储过程中的
DECLARE
只能在最开始位置声明,且不能在任意行位置。因此,如果要在同一存储过程或函数里使用多套游标与处理器,务必使用嵌套的匿名块(BEGIN … END
)来隔离,避免变量/处理器/游标命名冲突或顺序错误。
9.5 事务与并发问题
- 如果存储程序中涉及多次
UPDATE
、INSERT
,建议显式开启事务(START TRANSACTION
)并在结束时手动COMMIT
或ROLLBACK
。 - 在循环体中进行大量 DML 操作时,要关注锁的粒度与隔离级别;防止长事务导致死锁或阻塞。
10. 总结与技巧汇总
通过本文,你已经系统地学习了 MySQL 存储程序中游标与流程控制的使用方法与技巧,包括:
游标基础
DECLARE CURSOR FOR SELECT …
OPEN
、FETCH INTO
、CLOSE
CONTINUE HANDLER FOR NOT FOUND
捕获游标末尾
流程控制
- 条件:
IF … THEN … ELSEIF … ELSE … END IF
,CASE … WHEN … END CASE
- 循环:
LOOP … END LOOP
(配合LEAVE
、ITERATE
),WHILE … END WHILE
,REPEAT … UNTIL … END REPEAT
- 跳转:
LEAVE label
、ITERATE label
,可实现“跳出循环”、“进入下一次迭代”等
- 条件:
多游标 / 嵌套游标
- 使用匿名块(
BEGIN…END
)隔离不同层级的游标与处理器声明,避免命名与作用域冲突。 - 先外部声明一层游标,内部再嵌套声明第二层游标,实现“先遍历部门,再遍历员工”等需求。
- 使用匿名块(
综合业务示例
- 逐行打印:读取
employees
表行并打印。 - 批量更新:遍历并更新
Engineering
部门员工薪水,同时写日志。 - 部门统计:遍历部门游标,再嵌套遍历员工游标,累计薪水并写入统计表和告警表。
- 分页处理:结合主键范围做批量统计,避免一次性加载全表。
- 逐行打印:读取
常见注意事项
- 游标会占用资源,谨慎使用大结果集。
- 始终使用
CONTINUE HANDLER FOR NOT FOUND
处理FETCH
到末尾的情况,避免报错中断。 - 确保循环逻辑有可触发的终止条件,避免死循环。
- 在一个存储程序中使用多套游标时,务必用块级匿名
BEGIN…END
隔离作用域。 - 对于涉及多次 DML 的复杂逻辑,可显式开启事务(
START TRANSACTION/COMMIT
)保证数据一致性。
掌握了上述内容后,你就能在 MySQL 存储程序层面灵活地对多行结果集进行逐行处理,并结合多种流程控制语法实现复杂业务逻辑。接下来,建议动手将本文举例在你自己的数据库环境中运行、调试,并根据实际需求进行改造与优化,逐步积累经验。
评论已关闭