SQL基础⑭ | 变量、流程控制与游标篇

0 序言

本文整理MySQL中变量、流程控制与游标的核心知识，包括变量的分类与使用、条件处理机制、流程控制语句（分支、循环、跳转）及游标的操作。

通过学习，你将掌握这些工具在存储过程和函数中的应用，理解其作用逻辑，提升数据库编程的灵活性与效率。

1 变量

变量用于存储中间结果或输出结果，在存储过程和函数中不可或缺。

MySQL变量分为系统变量和用户自定义变量。

1.1 系统变量

系统变量由MySQL定义，反映服务器属性，分为全局和会话级别，影响数据库运行特性。

1.1.1 分类

• 全局系统变量：对所有会话有效，需用GLOBAL关键字，修改后不跨重启（除非改配置文件）。例如max_connections限制最大连接数。
• 会话系统变量：仅对当前会话有效，默认使用，需用SESSION关键字。初始值复制于全局变量，例如character_set_client设置客户端字符集。
• 静态变量：特殊全局变量，运行时不可用SET修改。

1.1.2 查看

• 查看所有全局变量：SHOW GLOBAL VARIABLES;
• 查看所有会话变量：SHOW SESSION VARIABLES; 或 SHOW VARIABLES;
• 查看部分变量（模糊匹配）：SHOW GLOBAL VARIABLES LIKE '%标识符%';（全局）或 SHOW SESSION VARIABLES LIKE '%标识符%';（会话）
• 查看指定变量：SELECT @@global.变量名;（全局）、SELECT @@session.变量名;（会话）或 SELECT @@变量名;（默认会话）

1.1.3 修改

方式1：修改配置文件（需重启服务）。
方式2：运行时用SET命令：

• 全局变量：SET @@global.变量名=值; 或 SET GLOBAL 变量名=值;
• 会话变量：SET @@session.变量名=值; 或 SET SESSION 变量名=值;

示例：

-- 查看全局自动提交设置
SELECT @@global.autocommit;
-- 关闭全局自动提交
SET GLOBAL autocommit=0;

1.2 用户变量

用户变量由用户定义，分会话用户变量和局部变量，用于存储自定义数据。

1.2.1 会话用户变量

作用域：当前会话有效。
定义与赋值：

• 方式1：SET @变量名=值; 或 SET @变量名:=值;
• 方式2：SELECT @变量名:=表达式 [FROM 子句]; 或 SELECT 表达式 INTO @变量名 [FROM 子句];
  查看：SELECT @变量名;

示例：

-- 赋值并查看
SET @num := COUNT(*) FROM employees;
SELECT @num;
-- 用查询结果赋值
SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;

1.2.2 局部变量

作用域：仅在定义它的BEGIN...END块中有效。
定义位置：BEGIN后第一句，用DECLARE声明。
语法：

• 定义：DECLARE 变量名 类型 [DEFAULT 默认值];（可同时声明多个变量）
• 赋值：SET 变量名=值; 或 SELECT 字段 INTO 变量名 FROM 表;
• 查看：SELECT 变量名;

示例：

DELIMITER //
CREATE PROCEDURE set_value()
BEGIN-- 声明局部变量DECLARE emp_name VARCHAR(25);DECLARE sal DOUBLE(10,2);-- 赋值SELECT last_name, salary INTO emp_name, sal FROM employees WHERE employee_id=102;-- 查看SELECT emp_name, sal;
END //
DELIMITER ;

1.2.3 对比

类型	作用域	定义位置	语法特点
会话用户变量	当前会话	会话任意位置	带@，无需指定类型
局部变量	定义它的BEGIN...END	BEGIN后第一句	不带@，需指定类型

2 定义条件与处理程序

为避免存储过程/函数因错误终止，需预先定义可能的错误条件及处理方式。

2.1 案例分析

若存储过程中执行错误SQL（如违反非空约束），未处理时会直接终止。例如：

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGINSET @x=1;-- 这里email非空，此语句报错UPDATE employees SET email=NULL WHERE last_name='Abel';SET @x=2; -- 不会执行
END //
DELIMITER ;

调用后@x为1，程序因错误终止。

2.2 定义条件

给错误码命名，便于后续引用。

语法：DECLARE 错误名称 CONDITION FOR 错误码;
错误码可为数值型（MySQL_error_code）或字符串型（sqlstate_value）。

示例：

-- 定义违反非空约束的错误（1048对应23000）
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
-- 或用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

2.3 定义处理程序

指定错误发生时的处理方式。

语法：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;

• 处理方式：CONTINUE（继续执行）、EXIT（退出）、UNDO（未支持）。

• 错误类型：SQLSTATE '字符串'、MySQL_error_code、错误名称、SQLWARNING（01开头错误）、NOT FOUND（02开头错误）、SQLEXCEPTION（其他错误）。

  示例：

  -- 捕获表不存在错误（1146），继续执行并记录信息
  DECLARE CONTINUE HANDLER FOR 1146 SET @info='表不存在';
  -- 先定义条件再使用
  DECLARE no_such_table CONDITION FOR 1146;
  DECLARE CONTINUE HANDLER FOR no_such_table SET @info='表不存在';
  

这里就给出了报错信息了。

2.4 案例解决

为上述UpdateDataNoCondition添加处理程序，使程序继续执行：

DELIMITER //
CREATE PROCEDURE UpdateDataWithCondition()
BEGIN-- 处理非空约束错误（1048），继续执行并设@proc_value=-1DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value=-1;SET @x=1;UPDATE employees SET email=NULL WHERE last_name='Abel'; -- 报错但继续SET @x=3; -- 会执行
END //
DELIMITER ;

运行这个程序，会得到@x=3，@proc_value=-1

这就说明问题解决了，程序可以继续执行。

3 流程控制

控制SQL语句执行顺序，包括分支、循环、跳转，实现复杂逻辑。

3.1 分支结构之IF

根据条件执行不同操作，语法：

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

示例：根据员工薪资和入职年限涨薪

DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGINDECLARE emp_salary DOUBLE;DECLARE hire_year DOUBLE;SELECT salary INTO emp_salary FROM employees WHERE employee_id=emp_id;SELECT DATEDIFF(CURDATE(), hire_date)/365 INTO hire_year FROM employees WHERE employee_id=emp_id;-- 薪资<8000且入职>5年，涨500IF emp_salary < 8000 AND hire_year > 5 THENUPDATE employees SET salary=salary+500 WHERE employee_id=emp_id;END IF;
END //
DELIMITER ;

然后你运行程序后，

106这个员工它是满足条件的，

那么你允许后它的薪资就会增加500.

我们看一下结果。

3.2 分支结构之CASE

类似多条件判断，分两种格式：

3.2.1 格式1（类似switch）

CASE 表达式
WHEN 值1 THEN 操作1
WHEN 值2 THEN 操作2
...
ELSE 操作N
END CASE

3.2.2 格式2（类似多重IF）

CASE
WHEN 条件1 THEN 操作1
WHEN 条件2 THEN 操作2
...
ELSE 操作N
END CASE

示例：按入职年限涨薪

DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGINDECLARE hire_year DOUBLE;SELECT ROUND(DATEDIFF(CURDATE(), hire_date)/365) INTO hire_year FROM employees WHERE employee_id=emp_id;CASE hire_yearWHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id=emp_id;WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id=emp_id;ELSE UPDATE employees SET salary=salary+500 WHERE employee_id=emp_id;END CASE;
END //
DELIMITER ;

同样的道理，

这个程序也是如此，

调用时，输入以下程序：

USE demo_hr;
CALL update_salary_by_eid1(101);

再把它选择出来，

就可以看到结果啦。

3.3 循环结构之LOOP

无条件循环，需用LEAVE退出。语法：

[loop_label:] LOOP循环体IF 退出条件 THEN LEAVE loop_label; END IF;
END LOOP [loop_label]

示例：循环涨薪至平均薪资≥12000

DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGINDECLARE avg_salary DOUBLE;DECLARE loop_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_salary FROM employees;label_loop: LOOPIF avg_salary >= 12000 THEN LEAVE label_loop; END IF;UPDATE employees SET salary=salary*1.1; -- 涨10%SET loop_count=loop_count+1;SELECT AVG(salary) INTO avg_salary FROM employees;END LOOP label_loop;SET num=loop_count;
END //
DELIMITER ;

这个程序详细讲解一下，

后续调用这个函数，然后看效果的话，

使用以下程序：

SET @num = 0;
CALL update_salary_loop(@num);
SELECT @num; -- 查看循环次数

当然，你也可以直接选择打开employees这张表，

可以清楚看到每个人的工资都提升了，

每个人的工资都得到了相对映的提升。

3.4 循环结构之WHILE

先判断条件，为真则执行循环体。语法：

[while_label:] WHILE 循环条件 DO循环体
END WHILE [while_label]

示例：循环降薪至平均薪资≤5000

DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGINDECLARE avg_sal DOUBLE;DECLARE while_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_sal FROM employees;WHILE avg_sal > 5000 DOUPDATE employees SET salary=salary*0.9; -- 降10%SET while_count=while_count+1;SELECT AVG(salary) INTO avg_sal FROM employees;END WHILE;SET num=while_count;
END //
DELIMITER ;

这个同理，可以自行到程序中自己验证一下

感受一下这个执行过程。

3.5 循环结构之REPEAT

先执行循环体，再判断条件，为真则退出。语法：

[repeat_label:] REPEAT循环体
UNTIL 退出条件
END REPEAT [repeat_label]

示例：循环涨薪至平均薪资≥13000

DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGINDECLARE avg_sal DOUBLE;DECLARE repeat_count INT DEFAULT 0;SELECT AVG(salary) INTO avg_sal FROM employees;REPEATUPDATE employees SET salary=salary*1.15; -- 涨15%SET repeat_count=repeat_count+1;SELECT AVG(salary) INTO avg_sal FROM employees;UNTIL avg_sal >= 13000END REPEAT;SET num=repeat_count;
END //
DELIMITER ;

当然了，

运行完后你也可以选择运行以下程序

去看看循环的次数。

USE demo_hr;
SET @num = 0;
CALL update_salary_repeat(@num);
SELECT @num; -- 查看循环次数

3.6 跳转语句之LEAVE

跳出循环或BEGIN...END块，类似break。语法：LEAVE 标记名;

示例：根据输入参数跳出程序体

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGINIF num <= 0 THEN LEAVE begin_label; -- 跳出BEGIN块ELSEIF num=1 THEN SELECT AVG(salary) FROM employees;ELSE SELECT MAX(salary) FROM employees;END IF;SELECT COUNT(*) FROM employees; -- num>0时执行
END //
DELIMITER ;

3.7 跳转语句之ITERATE

重新开始循环，类似continue。语法：ITERATE 标记名;

示例：控制循环执行范围

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGINDECLARE num INT DEFAULT 0;my_loop: LOOPSET num=num+1;IF num < 10 THEN ITERATE my_loop; -- num<10时重新循环ELSEIF num > 15 THEN LEAVE my_loop; -- num>15时退出END IF;END LOOP my_loop;
END //
DELIMITER ;

3.6、3.7的循环条件原理基本上可以说一致，

所以这里没有过多演示，

可以自己跑一下程序感受一下。

4 游标

游标是指向结果集的指针，支持逐条操作记录，为SQL提供面向过程能力。

4.1 作用

解决结果集无法逐条定位的问题，可遍历查询结果，对每条记录单独处理。

4.2 使用步骤

1. 声明游标：DECLARE 游标名 CURSOR FOR SELECT语句;（需在处理程序前声明）
2. 打开游标：OPEN 游标名;（加载结果集到内存）
3. 使用游标：FETCH 游标名 INTO 变量1[,变量2...];（读取当前行到变量，指针下移）
4. 关闭游标：CLOSE 游标名;（释放资源）

4.3 示例

累加薪资最高的员工薪资，直到总和达到指定值，返回累加人数：

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT)
BEGINDECLARE sum_salary DOUBLE DEFAULT 0; -- 总薪资DECLARE cursor_salary DOUBLE DEFAULT 0; -- 当前薪资DECLARE emp_count INT DEFAULT 0; -- 人数-- 1.声明游标（按薪资降序）DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;-- 2.打开游标OPEN emp_cursor;-- 循环读取REPEAT-- 3.使用游标FETCH emp_cursor INTO cursor_salary;SET sum_salary=sum_salary+cursor_salary;SET emp_count=emp_count+1;UNTIL sum_salary >= limit_total_salaryEND REPEAT;SET total_count=emp_count;-- 4.关闭游标CLOSE emp_cursor;
END //
DELIMITER ;

可以如此理解，

接着，你可以运行下面程序来查看结果：

SET @limit = 50000; -- 设定薪资阈值
SET @count = 0;
CALL get_count_by_limit_total_salary(@limit, @count);
SELECT @count; -- 查看需要的员工人数

4.4 注意事项

• 游标需及时关闭，避免占用资源。
• 结果集字段数需与FETCH中变量数一致，否则报错。
• 适用于小结果集，大结果集会消耗内存。

5 小结

本文系统介绍了MySQL中变量、条件处理、流程控制与游标：

• 变量分为系统变量（全局/会话）和用户变量（会话/局部），用于存储数据，需掌握定义、赋值与查看方法。
• 条件与处理程序可增强程序容错性，通过定义错误条件和处理方式，避免程序因错误终止。
• 流程控制（IF/CASE分支、LOOP/WHILE/REPEAT循环、LEAVE/ITERATE跳转）实现复杂逻辑，灵活控制执行顺序。
• 游标支持逐条操作结果集，弥补SQL面向集合的不足，但需注意资源释放。

掌握这些工具，在后续需要使用到的时候，就可以利用这些工具去实现自己应对复杂数据库的操作需求，从而提升存储过程和函数的编程能力。