MySQL游标与流程控制全解析:定义、使用及技巧‌

以下内容将从概念与语法入手,系统地讲解 MySQL 中游标(Cursor)与流程控制(Flow Control)的使用方法与技巧,并配以丰富的代码示例、ASCII 图解与详细说明,帮助你快速掌握在存储过程(Stored Procedure)或存储函数(Stored Function)中使用游标与流程控制的能力。建议边看边在 MySQL 沙箱环境中实践,加深理解。


1. 概述

在 MySQL 中,当我们需要对查询结果中的多行数据逐行遍历并进行复杂处理时,单纯的 SQL 语句往往无法满足需求。此时,就需要借助 游标(Cursor) 将结果集以“指针”的方式封装到存储程序中,配合 流程控制(Flow Control) 语句(如 LOOPWHILEIFCASELEAVEITERATE 等)来实现逐行处理、条件分支、循环跳出等逻辑。

  • 游标(Cursor):类似于编程语言中的迭代器,用来保存一条或多条查询结果,通过 OPENFETCHCLOSE 等操作让我们在存储过程里逐行取出数据。
  • 流程控制(Flow Control):MySQL 存储程序内置的一组语法,包括条件判断(IF…THEN…ELSECASE)、循环结构(LOOPWHILEREPEAT)与跳转控制(LEAVEITERATE 等),用来实现存储过程/函数中的分支与循环逻辑。

通过对二者的结合,我们可以在 MySQL 层面实现下面这些场景:

  1. 逐行读取查询结果并插入/更新/删除其他表(如统计、数据同步等)。
  2. 当查询到特定条件时跳出循环或跳到下一条,实现复杂的业务规则。
  3. 根据游标字段判断分支逻辑,如根据某列值进行分类处理。
  4. 处理分页数据,例如批量归档、拆分大表时逐页操作。

下面将循序渐进地介绍游标与流程控制的核心概念、语法、使用示例与最佳实践。


2. 游标基础

2.1 游标概念与生命周期

  • 游标(Cursor) 本质上是一个指向查询结果集(Result Set)的指针。通过在存储程序中声明游标后,可以按以下步骤使用:

    1. DECLARE CURSOR:声明游标,指定要执行的 SELECT 语句。
    2. OPEN:打开游标,将查询结果集装载到内存中(或按需读取)。
    3. FETCH:从游标返回一行(或一列)数据到变量。
    4. REPEAT FETCH:重复 FETCH 直到游标到末尾。
    5. CLOSE:关闭游标,释放资源。
  • 生命周期示意图(ASCII)

    +--------------------+
    | 存储过程开始       |
    |                    |
    | 1. DECLARE 游标    |
    | 2. OPEN 游标       |
    |                    |
    | ┌───────────┐      |
    | │ 游标结果集 │      |
    | └───────────┘      |
    |    ↓ FETCH 1 行     |
    |    ↓ FETCH 2 行     |
    |       …             |
    |    ↓ FETCH N 行     |
    | 3. CLOSE 游标      |
    |                    |
    | 存储过程结束       |
    +--------------------+
    • FETCH 直到条件变量 NOT FOUND,即没有更多行可取时跳出循环。

2.2 声明游标的基本语法

在 MySQL 存储程序(PROCEDUREFUNCTION)中,游标的声明必须在所有变量(DECLARE var_name …)、条件处理器(DECLARE CONTINUE HANDLER …)之后,且在第一个可执行语句(如 SETSELECTINSERT 等)之前。

语法格式:

DECLARE cursor_name CURSOR FOR select_statement;
  • cursor_name:游标名称,自定义标识。
  • select_statement:任意合法的 SELECT 语句,用来生成游标结果集。

注意事项

  1. 声明位置:所有 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 …; 等。
  2. 条件处理器(Handler)

    • 最常见的是 NOT FOUND 处理器,用于捕获 FETCH 到末尾时的错误标志。常用写法:

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;

      当游标超出结果集时,MySQL 会触发 NOT FOUND 条件。如果我们不声明处理器,就会导致存储过程报错中断。

  3. 游标只能在存储过程/函数内使用,不能在普通 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 关键点详解

  1. 变量声明(DECLARE v_id INT; 等):用来接收 FETCH 出来的各列值。
  2. done_flag 标志:常用来判断游标是否到末尾,当没有更多行时,MySQL 会触发 NOT FOUND 条件,执行对应的 CONTINUE HANDLER 设置 done_flag = 1
  3. 游标声明

    DECLARE emp_cursor CURSOR FOR
        SELECT id, name, department, salary
        FROM employees
        ORDER BY id;
    • 这里指定了要遍历的查询结果集,结果会按 id 升序返回。
  4. 条件处理器

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_flag = 1;
    • FOR NOT FOUND:表示若之后的 FETCH 没有可读取的行,则跳转到此处理器,将 done_flag 置为 1,并让程序继续执行(CONTINUE)。
  5. 打开游标

    OPEN emp_cursor;

    这一步会执行 SELECT id, name, … 并将结果集保存到内部数据结构,等待调用 FETCH

  6. 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; 将信息“打印”到客户端(真实场景可改成 INSERTUPDATE 等操作)。
    • 提前跳出:演示了当 v_sal > 11000 时,再次 LEAVE read_loop,直接退出遍历。
  7. 关闭游标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 说明与要点

  1. DECLARE eng_cursor CURSOR FOR SELECT … WHERE department = 'Engineering'

    • 只遍历 Engineering 部门的员工。
    • ORDER BY id 保证处理顺序一致。
  2. 更新与日志

    • 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;
  3. 事务与并发

    • 如果同时有其他会话在操作 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 关键点与注意事项

  1. 嵌套声明位置

    • 因为 MySQL 要求 DECLARE … HANDLERDECLARE … CURSOR 必须在存储程序局部“最前面”,在一个存储过程体内,如果想为第二个游标声明处理器和游标,就需要用一个新的块(BEGIN … END)隔离。
    • 外层的 dept_cursor 处于最外层块,内层的 emp_cursor 则放在一个新的匿名块(BEGIN … END)中。
  2. 处理器隔离

    • 外层使用 dept_done,内层使用 emp_done,互不干扰。
    • 如果不使用匿名子块,内层的 DECLARE CONTINUE HANDLER FOR NOT FOUND 会与外层冲突,导致逻辑混乱。
  3. CURSOR 作用域

    • emp_cursor 只在内层匿名块中有效,出了该块就会失效。每次循环进入一个新部门时,都会重新进入该匿名块,重新声明处理器和游标。
  4. 流程示意(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();

假设 departmentsemployees 表如前所示,执行结果类似:

+----------------------------------------+
| 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 LOOPIF … THEN … END IFLEAVE 等流程控制语句。下面集中介绍 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 中常见的循环结构有三种:LOOPWHILEREPEAT。它们的差异与用法如下。

6.2.1 LOOP…END LOOP

  • 语法

    [label:] LOOP
        statements;
        [LEAVE label;]
        [ITERATE label;]
        ...
    END LOOP [label];
  • 需要配合标签 labelLEAVEITERATE 跳出或继续循环。
  • 示例:下面例子在循环里 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();
    • 该存储过程会依次打印 12(跳过 3)、4567,然后在 i=8LEAVE,循环结束。

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 解析与要点

  1. 两个游标的块级隔离

    • 部门游标在最外层声明并打开。
    • 针对每个部门,使用一个匿名块 BEGIN … END; 来声明与使用员工游标,确保 DECLARE 顺序与作用域正确。
  2. dept_total 累加

    • 在进入员工游标前,将 dept_total 置为 0。
    • 每次 FETCH 得到 v_emp_sal 后,用 dept_total = dept_total + v_emp_sal 进行累加。
  3. 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();

      这样写更简洁。

  4. 阈值告警

    • dept_total 超过 salary_threshold 时,插入 dept_alerts
    • 如果想避免重复插入同一部门多条告警,可在插入前先判断或使用唯一索引。
  5. 控制流程示意(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 思路概要

  1. 假设 employees 表主键为 id
  2. 每次从 last_id+1 开始,取出 batch_size 条记录(如 1000 条)。
  3. 对当前批次执行统计(或其它处理)。
  4. 更新 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 说明

  1. batch_size 参数:由调用者指定每页大小。
  2. v_last_idv_max_id

    • v_last_id 用于记录上一批次的最大 id,初始为 0。
    • v_max_id = 表中最大 id,用于确定循环终止条件。
  3. 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) 用来避免最后一页超过最大值。
  4. 子查询 SUM(salary):一次性统计当前批次薪资和,无需显式游标遍历。
  5. 分页操作:若需要针对每条记录做更复杂操作,可以在子查询改为 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,共分三页:

    1. IDs 1–2,总和 = 8000 + 7800 = 15800
    2. IDs 3–4,总和 = 12000 + 11500 = 23500
    3. 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 避免死循环

  • LOOPWHILEREPEAT 中,一定要保证循环的终止条件能够被正确触发,否则会导致死循环。
  • 对于游标循环,务必在 FETCH 后检查 done_flag,并在适当位置调用 LEAVE

9.4 变量作用域

  • MySQL 存储过程中的 DECLARE 只能在最开始位置声明,且不能在任意行位置。因此,如果要在同一存储过程或函数里使用多套游标与处理器,务必使用嵌套的匿名块(BEGIN … END)来隔离,避免变量/处理器/游标命名冲突或顺序错误。

9.5 事务与并发问题

  • 如果存储程序中涉及多次 UPDATEINSERT,建议显式开启事务(START TRANSACTION)并在结束时手动 COMMITROLLBACK
  • 在循环体中进行大量 DML 操作时,要关注锁的粒度与隔离级别;防止长事务导致死锁或阻塞。

10. 总结与技巧汇总

通过本文,你已经系统地学习了 MySQL 存储程序中游标与流程控制的使用方法与技巧,包括:

  1. 游标基础

    • DECLARE CURSOR FOR SELECT …
    • OPENFETCH INTOCLOSE
    • CONTINUE HANDLER FOR NOT FOUND 捕获游标末尾
  2. 流程控制

    • 条件:IF … THEN … ELSEIF … ELSE … END IFCASE … WHEN … END CASE
    • 循环:LOOP … END LOOP(配合 LEAVEITERATE),WHILE … END WHILEREPEAT … UNTIL … END REPEAT
    • 跳转:LEAVE labelITERATE label,可实现“跳出循环”、“进入下一次迭代”等
  3. 多游标 / 嵌套游标

    • 使用匿名块(BEGIN…END)隔离不同层级的游标与处理器声明,避免命名与作用域冲突。
    • 先外部声明一层游标,内部再嵌套声明第二层游标,实现“先遍历部门,再遍历员工”等需求。
  4. 综合业务示例

    • 逐行打印:读取 employees 表行并打印。
    • 批量更新:遍历并更新 Engineering 部门员工薪水,同时写日志。
    • 部门统计:遍历部门游标,再嵌套遍历员工游标,累计薪水并写入统计表和告警表。
    • 分页处理:结合主键范围做批量统计,避免一次性加载全表。
  5. 常见注意事项

    • 游标会占用资源,谨慎使用大结果集。
    • 始终使用 CONTINUE HANDLER FOR NOT FOUND 处理 FETCH 到末尾的情况,避免报错中断。
    • 确保循环逻辑有可触发的终止条件,避免死循环。
    • 在一个存储程序中使用多套游标时,务必用块级匿名 BEGIN…END 隔离作用域。
    • 对于涉及多次 DML 的复杂逻辑,可显式开启事务(START TRANSACTION/COMMIT)保证数据一致性。

掌握了上述内容后,你就能在 MySQL 存储程序层面灵活地对多行结果集进行逐行处理,并结合多种流程控制语法实现复杂业务逻辑。接下来,建议动手将本文举例在你自己的数据库环境中运行、调试,并根据实际需求进行改造与优化,逐步积累经验。

评论已关闭

推荐阅读

DDPG 模型解析,附Pytorch完整代码
2024年11月24日
DQN 模型解析,附Pytorch完整代码
2024年11月24日
AIGC实战——Transformer模型
2024年12月01日
Socket TCP 和 UDP 编程基础(Python)
2024年11月30日
python , tcp , udp
如何使用 ChatGPT 进行学术润色?你需要这些指令
2024年12月01日
AI
最新 Python 调用 OpenAi 详细教程实现问答、图像合成、图像理解、语音合成、语音识别(详细教程)
2024年11月24日
ChatGPT 和 DALL·E 2 配合生成故事绘本
2024年12月01日
omegaconf,一个超强的 Python 库!
2024年11月24日
【视觉AIGC识别】误差特征、人脸伪造检测、其他类型假图检测
2024年12月01日
[超级详细]如何在深度学习训练模型过程中使用 GPU 加速
2024年11月29日
Python 物理引擎pymunk最完整教程
2024年11月27日
MediaPipe 人体姿态与手指关键点检测教程
2024年11月27日
深入了解 Taipy:Python 打造 Web 应用的全面教程
2024年11月26日
基于Transformer的时间序列预测模型
2024年11月25日
Python在金融大数据分析中的AI应用(股价分析、量化交易)实战
2024年11月25日
AIGC Gradio系列学习教程之Components
2024年12月01日
Python3 `asyncio` — 异步 I/O,事件循环和并发工具
2024年11月30日
llama-factory SFT系列教程:大模型在自定义数据集 LoRA 训练与部署
2024年12月01日
Python 多线程和多进程用法
2024年11月24日
Python socket详解,全网最全教程
2024年11月27日