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 ;

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

1.2.3 对比

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

2 定义条件与处理程序

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

2.1 案例分析

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

DELIMITER //CREATE PROCEDURE UpdateDataNoCondition()BEGIN SET @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_valueDECLARE 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=\'表不存在\';

SQL基础⑭ | 变量、流程控制与游标篇
这里就给出了报错信息了。

2.4 案例解决

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

DELIMITER //CREATE PROCEDURE UpdateDataWithCondition()BEGIN -- 处理非空约束错误（1048），继续执行并设@proc_value=-1 DECLARE 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)BEGIN DECLARE 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; -- 薪资5年，涨500 IF emp_salary < 8000 AND hire_year > 5 THEN UPDATE employees SET salary=salary+500 WHERE employee_id=emp_id; END IF;END //DELIMITER ;

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

然后你运行程序后，

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

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

我们看一下结果。

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

3.2 分支结构之CASE

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

3.2.1 格式1（类似switch）

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

3.2.2 格式2（类似多重IF）

CASEWHEN 条件1 THEN 操作1WHEN 条件2 THEN 操作2...ELSE 操作NEND CASE

示例：按入职年限涨薪

DELIMITER //CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)BEGIN DECLARE hire_year DOUBLE; SELECT ROUND(DATEDIFF(CURDATE(), hire_date)/365) INTO hire_year FROM employees WHERE employee_id=emp_id; CASE hire_year WHEN 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)BEGIN DECLARE avg_salary DOUBLE; DECLARE loop_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_salary FROM employees; label_loop: LOOP IF 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 ;

这个程序详细讲解一下，

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

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

使用以下程序：

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

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

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

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

SQL基础⑭ | 变量、流程控制与游标篇
每个人的工资都得到了相对映的提升。

3.4 循环结构之WHILE

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

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

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

DELIMITER //CREATE PROCEDURE update_salary_while(OUT num INT)BEGIN DECLARE avg_sal DOUBLE; DECLARE while_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_sal FROM employees; WHILE avg_sal > 5000 DO UPDATE 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)BEGIN DECLARE avg_sal DOUBLE; DECLARE repeat_count INT DEFAULT 0; SELECT AVG(salary) INTO avg_sal FROM employees; REPEAT UPDATE employees SET salary=salary*1.15; -- 涨15% SET repeat_count=repeat_count+1; SELECT AVG(salary) INTO avg_sal FROM employees; UNTIL avg_sal >= 13000 END REPEAT; SET num=repeat_count;END //DELIMITER ;

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

当然了，

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

去看看循环的次数。

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

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

3.6 跳转语句之LEAVE

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

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

DELIMITER //CREATE PROCEDURE leave_begin(IN num INT)begin_label: BEGIN IF 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()BEGIN DECLARE num INT DEFAULT 0; my_loop: LOOP SET 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 使用步骤

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

4.3 示例

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

DELIMITER //CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT)BEGIN DECLARE 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_salary END REPEAT; SET total_count=emp_count; -- 4.关闭游标 CLOSE emp_cursor;END //DELIMITER ;

SQL基础⑭ | 变量、流程控制与游标篇
可以如此理解，

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

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

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

4.4 注意事项

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

5 小结

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

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

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

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

0 序言

1 变量

1.1 系统变量

1.1.1 分类

1.1.2 查看

1.1.3 修改

1.2 用户变量

1.2.1 会话用户变量

1.2.2 局部变量

1.2.3 对比

2 定义条件与处理程序

2.1 案例分析

2.2 定义条件

2.3 定义处理程序

2.4 案例解决

3 流程控制

3.1 分支结构之IF

3.2 分支结构之CASE

3.2.1 格式1（类似switch）

3.2.2 格式2（类似多重IF）

3.3 循环结构之LOOP

3.4 循环结构之WHILE

3.5 循环结构之REPEAT

3.6 跳转语句之LEAVE

3.7 跳转语句之ITERATE

4 游标

4.1 作用

4.2 使用步骤

4.3 示例

4.4 注意事项

5 小结

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

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

0 序言

1 变量

1.1 系统变量

1.1.1 分类

1.1.2 查看

1.1.3 修改

1.2 用户变量

1.2.1 会话用户变量

1.2.2 局部变量

1.2.3 对比

2 定义条件与处理程序

2.1 案例分析

2.2 定义条件

2.3 定义处理程序

2.4 案例解决

3 流程控制

3.1 分支结构之IF

3.2 分支结构之CASE

3.2.1 格式1（类似switch）

3.2.2 格式2（类似多重IF）

3.3 循环结构之LOOP

3.4 循环结构之WHILE

3.5 循环结构之REPEAT

3.6 跳转语句之LEAVE

3.7 跳转语句之ITERATE

4 游标

4.1 作用

4.2 使用步骤

4.3 示例

4.4 注意事项

5 小结

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签