MySQL 二轮学习笔记·进阶篇·(五) 存储过程与存储函数
存储过程
存储过程(Stored Procedure)是预编译并存储在数据库中的一组 SQL 语句集合,可通过 “调用指令” 重复执行,无需每次重新编写 SQL。
1.基本语法
创建存储过程
delimiter //
create procedure 存储过程名(参数...)
begin
select * from 表名;
insert into 表名 values(...);
end //
delimiter ;
注:delimitee
能临时修改语句结束符,防止mysql将存储过程内的;
识别为数据结束,导致语法错误,因此在存储过程前添加delimiter //
修改分隔符,在存储过程后添加delimiter ;
恢复分隔符。
调用存储过程
call 存储过程名(参数...);
无参数时写:cal 存储过程名();
查看存储过程
查看所有存储过程:show procedure status;
查看某个存储过程的创建语句:show create procedure 存储过程名;
删除存储过程
drop procedure if exists 存储过程名;
2.变量
系统变量
分类
全局变量:影响整个mysql服务,前缀是global
会话变量:仅影响当前数据库连接,前缀是session
查看系统变量
show global variables;
show session variables;
select @@global.变量名;
select @@session.变量名;(session可省略,默认查当前会话)
修改系统变量
set global 变量名=值;(修改后需重启连接生效,重启 MySQL 服务后失效,需改配置文件永久生效)
set session 变量名=值;(仅当前连接生效,断开后恢复默认)
用户定义变量
作用范围为当前会话,无需显式声明类型(MySQL 自动推断,如整数、字符串),直接赋值即可
set @变量名 = 值;(推荐)
示例:
SET @user_age = 25;
select 值 into @变量名;(从查询结果中赋值,需确保查询结果只有一行一列)
示例:
SELECT age INTO @user_age FROM users WHERE id = 1;
局部变量
作用范围仅在存储过程的BEGIN...END
块内部(或IF
/循环
等子块内),必须先声明、后使用,且需指定数据类型(如INT
、VARCHAR(20)
)
delimiter //
create procedure test_local_var()
begin
declare var_name varchar(20);
declare var_age int default 0;
set var_name = '张三';
select age into var_age from users where id = 1;
select var_name, var_age;
end //
delimiter ;
局部变量不能加@
。
3.if 判断
IF 条件1 THEN
-- 条件1为TRUE执行
ELSE IF 条件2 THEN
-- 条件2为TRUE执行
ELSE
-- 所有条件都为FALSE执行
END IF;
示例:根据年龄判断用户等级
DELIMITER //
CREATE PROCEDURE get_user_level(IN user_id INT) -- IN参数:接收外部传入的用户ID
BEGIN
DECLARE user_age INT;
DECLARE level VARCHAR(10);
SELECT age INTO user_age FROM users WHERE id = user_id;
-- IF判断逻辑
IF user_age < 18 THEN
SET level = '未成年';
ELSE IF user_age BETWEEN 18 AND 60 THEN
SET level = '成年';
ELSE
SET level = '老年';
END IF;
SELECT level AS user_level; -- 返回结果
END //
DELIMITER ;
-- 调用:查看ID=1的用户等级
CALL get_user_level(1);
4.参数(in,out,inout)
类型 | 数据流向 | 特点 | 示例场景 |
---|---|---|---|
IN | 外部→存储过程(仅传入数据) | 存储过程内不能修改外部传入的参数值 | 传入用户 ID,查询用户信息 |
OUT | 存储过程→外部(仅返回数据) | 参数在外部需先声明(用用户变量),存储过程内赋值后传出 | 计算结果返回给外部(如统计总数) |
INOUT | 外部→存储过程→外部(双向传递) | 既传入初始值,存储过程修改后再传出 | 传入数值,存储过程累加后返回 |
DELIMITER //
-- 1. IN参数:传入用户名,查询年龄
CREATE PROCEDURE get_age_by_name(IN in_name VARCHAR(20))
BEGIN
SELECT age FROM users WHERE name = in_name;
END //
-- 2. OUT参数:统计用户总数,返回给外部
CREATE PROCEDURE count_total_users(OUT out_total INT)
BEGIN
SELECT COUNT(*) INTO out_total FROM users; -- 存储过程内赋值
END //
-- 3. INOUT参数:传入初始值,累加5后返回
CREATE PROCEDURE add_five(INOUT inout_num INT)
BEGIN
SET inout_num = inout_num + 5; -- 修改传入的参数值
END //
DELIMITER ;
-- 调用示例
CALL get_age_by_name('张三'); -- IN参数:直接传值
SET @total = 0; -- 先声明用户变量接收OUT参数
CALL count_total_users(@total);
SELECT @total; -- 查看返回的总数(OUT参数的结果)
SET @num = 10; -- 声明并赋值INOUT参数的初始值
CALL add_five(@num);
SELECT @num; -- 结果为15(INOUT参数的修改后值)
5.循环
(1)while
WHILE 条件表达式 DO
-- 循环体(需执行的SQL语句)
-- (必须包含“让条件最终为FALSE”的逻辑,否则无限循环)
END WHILE;
(2)repeat
REPEAT
-- 循环体(至少执行1次)
-- (需包含“让条件最终为TRUE”的逻辑,否则无限循环)
UNTIL 条件表达式 -- 注意:UNTIL后无“;”
END REPEAT;
eg. 用 REPEAT 循环计算 1~10 的累加和
DELIMITER //
CREATE PROCEDURE calculate_sum()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
REPEAT
SET total = total + i; -- 累加(先执行1次)
SET i = i + 1; -- 计数器自增
UNTIL i > 10 -- 条件:i>10时退出(此时i从1到11,循环执行10次)
END REPEAT;
SELECT total AS sum_1_to_10; -- 返回结果:55
END //
DELIMITER ;
-- 调用:计算1~10的和
CALL calculate_sum();
(3)loop
LOOP
本身是 “无限循环”,必须通过LEAVE
主动退出,灵活性最高。
-- 1. 定义循环标签(自定义名称,如loop_label)
loop_label: LOOP
-- 循环体(默认无限循环)
-- 2. 退出循环(必须用LEAVE,配合条件判断)
IF 退出条件 THEN
LEAVE loop_label; -- 满足条件时,退出loop_label标签对应的循环
END IF;
-- 3. 跳过当前循环剩余部分,进入下一次循环(可选,用ITERATE)
IF 跳过条件 THEN
ITERATE loop_label; -- 满足条件时,跳过后续代码,直接开始下一次循环
END IF;
END LOOP loop_label;
eg. 用 LOOP 循环筛选 1~20 中的偶数
DELIMITER $$
CREATE PROCEDURE find_even_numbers()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE even_numbers TEXT DEFAULT '';
loop_label: LOOP
IF i > 20 THEN
LEAVE loop_label;
END IF;
IF i % 2 = 0 THEN
SET even_numbers = CONCAT(even_numbers, i, ' ');
END IF;
SET i = i + 1;
END LOOP;
SELECT even_numbers AS '1~20中的偶数';
END $$
DELIMITER ;
-- 执行存储过程
CALL find_even_numbers();
6.游标-cursor
数据库中的“结果集指针”,可定位到查询结果的某一行,读取该行数据,实现逐行读取查询结果集,解决存储过程中无法直接遍历多行数据的问题。
- 只能单向移动(只能从第一行往后读,不能回退);
- 只能关联一个查询结果集(一个游标对应一个
SELECT
语句); - 需配合“条件处理程序(Handler)”处理“游标遍历到末尾”的情况(否则会报错)。
DELIMITER //
CREATE PROCEDURE use_cursor_demo()
BEGIN
-- 步骤1:声明局部变量(用于存储游标读取的每行数据)
DECLARE var_id INT;
DECLARE var_name VARCHAR(20);
DECLARE var_age INT;
DECLARE is_end BOOLEAN DEFAULT FALSE; -- 标记游标是否遍历结束(关键)
-- 步骤2:声明游标(关联查询结果集)
DECLARE user_cursor CURSOR FOR
SELECT id, name, age FROM users WHERE age > 18; -- 游标对应的查询(只查成年用户)
-- 步骤3:声明“条件处理程序”(处理“游标无数据”的情况)
-- 当触发“SQLSTATE '02000'”(游标到达末尾,无更多数据)时,设置is_end为TRUE
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_end = TRUE;
-- 步骤4:打开游标(激活游标,关联查询结果集)
OPEN user_cursor;
-- 步骤5:循环读取游标数据(用LOOP/REPEAT/WHILE)
cursor_loop: LOOP
FETCH user_cursor INTO var_id, var_name, var_age; -- 读取游标当前行数据,赋值给局部变量(顺序需与游标查询的列一致)
IF is_end THEN -- 判断是否遍历结束,是则退出循环
LEAVE cursor_loop;
END IF;
-- 步骤6:处理数据(示例:打印成年用户信息,或执行其他业务逻辑)
SELECT CONCAT('ID:', var_id, ',姓名:', var_name, ',年龄:', var_age) AS adult_user;
END LOOP cursor_loop;
-- 步骤7:关闭游标(释放资源,必须执行)
CLOSE user_cursor;
END //
DELIMITER ;
-- 调用:遍历并打印所有成年用户信息
CALL use_cursor_demo();
- 声明顺序严格:必须先声明局部变量 → 再声明游标 → 最后声明条件处理程序(否则语法错误)。
FETCH
顺序匹配:FETCH ... INTO
后的变量顺序,必须与游标SELECT
语句的列顺序完全一致。- 资源释放:游标使用后必须用
CLOSE
关闭,避免占用数据库连接资源。
7.条件处理程序-handler
条件处理程序是 “预先定义的规则”:当存储过程中触发特定 SQL 状态码或特定错误码时,自动执行规则中的操作(如设置变量、退出循环、忽略错误)。
DECLARE 处理方式 HANDLER FOR 触发条件
DO 执行的操作;
处理方式
CONTINUE
:执行操作后,继续执行存储过程后续代码(常用,如游标结束后继续关闭游标)。
EXIT
:执行操作后,立即退出当前BEGIN...END
块(不常用,可能导致资源未释放)。
触发条件
特定 SQL 状态码:如SQLSTATE '02000'
(游标无数据)、SQLSTATE '23000'
(主键冲突 / 数据重复);
特定错误码:如1062
(主键冲突,对应SQLSTATE '23000'
);
通用条件:SQLEXCEPTION
(所有异常)、SQLWARNING
(所有警告)、NOT FOUND
(无数据,等价于SQLSTATE '02000'
)。
场景 1:处理 “数据重复插入”(主键冲突)
DELIMITER //
CREATE PROCEDURE insert_user_safe(IN in_id INT, IN in_name VARCHAR(20))
BEGIN
-- 声明条件处理程序:当触发主键冲突(SQLSTATE '23000')时,打印提示
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT CONCAT('用户ID ', in_id, ' 已存在,插入失败') AS result;
END;
-- 尝试插入数据(若ID已存在,会触发上述Handler)
INSERT INTO users (id, name) VALUES (in_id, in_name);
SELECT CONCAT('用户ID ', in_id, ' 插入成功') AS result;
END //
DELIMITER ;
-- 调用测试:先插入ID=1,再插入ID=1(第二次会触发Handler)
CALL insert_user_safe(1, '张三'); -- 第一次:插入成功
CALL insert_user_safe(1, '张三'); -- 第二次:触发Handler,提示“已存在”
场景 2:处理 “游标无数据”(配合游标使用)
-- 当游标遍历到末尾(无数据,SQLSTATE '02000'),设置is_end为TRUE,继续执行后续关闭游标的代码
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_end = TRUE;
存储函数
存储函数是 “预编译并存储在数据库中的 SQL 逻辑块”,必须有且仅有一个返回值(类似 Java 的 “有返回值方法”),可直接在SELECT
等 SQL 语句中调用。
1.基本语法
(1)创建存储函数
DELIMITER //
CREATE FUNCTION 函数名(参数列表)
RETURNS 返回值类型 -- 必须指定返回值类型(如INT、VARCHAR(20))
DETERMINISTIC -- 可选:标记函数“输入相同则输出相同”(优化用)
NO SQL -- 可选:标记函数“不操作SQL”(如纯计算);若操作SQL,用READS SQL DATA
BEGIN
-- 函数体(需包含RETURN语句,返回结果)
DECLARE 局部变量;
-- 业务逻辑
RETURN 返回值; -- 必须有RETURN,且返回值类型与RETURNS一致
END //
DELIMITER ;
(2)调用存储函数
直接在 SQL 语句中使用(如SELECT
、WHERE
条件),无需CALL
(这也是与存储过程的区别)
-- 示例:调用函数获取用户年龄
SELECT get_user_age(1) AS user_age; -- 1是函数的参数(用户ID)
2.应用示例
DELIMITER //
CREATE FUNCTION get_user_age(in_user_id INT)
RETURNS INT -- 返回值类型:整数(年龄)
READS SQL DATA -- 标记函数“仅读取SQL数据,不修改”
BEGIN
DECLARE out_age INT;
-- 查询用户年龄(若用户不存在,out_age为NULL)
SELECT age INTO out_age FROM users WHERE id = in_user_id;
RETURN out_age; -- 返回结果
END //
DELIMITER ;
-- 调用方式1:直接查询
SELECT get_user_age(1) AS age_of_user1; -- 若ID=1存在,返回年龄;否则返回NULL
-- 调用方式2:在WHERE条件中使用
SELECT * FROM users WHERE age > get_user_age(1); -- 查询年龄大于“ID=1用户”的所有用户
3.存储函数 vs 存储过程
对比维度 | 存储函数(Function) | 存储过程(Procedure) |
---|---|---|
返回值 | 必须有且仅有 1 个返回值 | 可选(可通过 OUT/INOUT 参数返回多个值) |
调用方式 | 直接在 SQL 语句中使用(如 SELECT) | 必须用 CALL 指令调用 |
参数类型 | 仅支持 IN 参数(默认,无需显式声明) | 支持 IN、OUT、INOUT 三种参数 |
适用场景 | 简单计算、单值查询(如根据 ID 查字段) | 复杂业务逻辑(多 SQL、流程控制、多值返回) |
事务支持 | 不能包含事务控制(如 COMMIT、ROLLBACK) | 可包含事务控制 |
本文系作者 @xiin 原创发布在To Future$站点。未经许可,禁止转载。
暂无评论数据