彻底搞懂MySQL存储过程和函数

结果或目标

通过本教程，您将能够熟练使用 CREATE PROCEDURE 和 CREATE FUNCTION 语法，理解存储过程与函数的区别，并能在复杂业务逻辑中运用变量、条件分支和循环结构进行数据处理。

准备条件

1. 环境：已安装并运行 MySQL 数据库服务。
2. 基础：具备基本的 SQL 查询能力。
3. 权限：拥有在指定数据库中创建子程序（Routine）的权限。

真实操作顺序

第1步：配置分隔符（DELIMITER）

由于 MySQL 默认以分号 ; 作为语句结束符，而存储过程的内部包含多条 SQL 语句，直接使用会导致解析冲突。因此，需要先临时将分隔符修改为其他字符。

DELIMITER $$
-- 在此之后编写存储过程代码...
$$
DELIMITER ;

第2步：创建存储过程（Procedure）

存储过程适合执行一系列动作（如增删改），不支持直接返回值给调用者（但可通过 OUT 参数输出）。

• 语法结构：CREATE PROCEDURE name ([param]) BEGIN ... END
• 参数类型：
  • IN：输入参数（默认）。
  • OUT：输出参数，用于从过程向外部传值。
  • INOUT：既可作为输入，也可作为输出。

DELIMITER $$
CREATE PROCEDURE GetEmployeeSalary()
COMMENT '查询员工薪水'
BEGIN
	SELECT salary FROM t_employee;
END$$
DELIMITER ;

第3步：创建存储函数（Function）

函数适合计算并返回单个值，可以在 SQL 语句中被调用。

• 必须指定返回类型：RETURNS type
• 参数限制：函数通常只支持 IN 参数（具体取决于 MySQL 版本和设置）。

DELIMITER $$
CREATE FUNCTION FuncEmployee(id INT(4))
RETURNS INT(6)
COMMENT '根据ID查询薪水'
BEGIN
	DECLARE sal INT;
	SELECT salary INTO sal FROM t_employee WHERE id = id;
	RETURN sal;
END$$
DELIMITER ;

第4步：定义与管理变量

变量仅在 BEGIN ... END 块内有效。

1. 声明：使用 DECLARE var_name type DEFAULT value。必须在块的开头，且在普通 SQL 语句之前。
2. 赋值：
   • SET var = value;
   • SELECT col INTO var FROM table...

-- 声明并赋值
DECLARE test_sql INT DEFAULT 10;
SET test_sql = 30;

第5步：定义异常处理程序

为了增强健壮性，可以捕获错误或特定状态码进行处理。处理类型包括：CONTINUE（继续执行）、EXIT（立即退出）、UNDO（回滚，仅在某些事务环境下有效）。

-- 捕获 SQLSTATE '42S02' (表不存在) 并设置变量
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='Table not found';

第6步：使用 Cursor（光标）遍历结果集

当查询返回多行数据时，需借助光标逐条读取。注意声明顺序：变量 → 条件 → 光标 → 处理器。

1. 声明光标：DECLARE cur_name CURSOR FOR SELECT ...
2. 打开光标：OPEN cur_name;
3. 提取数据：FETCH cur_name INTO var_list;
4. 关闭光标：CLOSE cur_name;（用完必须关闭以释放资源）

第7步：应用流程控制语句

存储过程中支持标准的结构化编程控制流：

• IF-THEN-ELSE：条件分支。
• WHILE / REPEAT / LOOP：循环结构。
  • LEAVE label：跳出循环。
  • ITERATE label：跳过本次循环剩余部分，进入下一次循环（类似 continue）。

完成判断

创建完成后，您可以通过以下命令验证和调用：

• 调用过程：CALL proc_name();
• 调用函数：SELECT func_name(1);
• 查看定义：SHOW CREATE PROCEDURE proc_name;
• 删除对象：DROP PROCEDURE IF EXISTS proc_name;

简要总结

MySQL 存储过程和函数是将业务逻辑下沉到数据库层的重要手段。合理运用参数传递、变量管理、异常捕获及游标循环，可以显著提升数据处理的原子性和性能。在实际开发中，建议将简单的聚合计算放在 SQL 层面，复杂的业务逻辑或需要多次交互数据的场景考虑使用存储过程。

以上就是MySQL存储过程和函数的详细内容，更多关于MySQL存储过程和函数优化的资料请关注本站其它相关文章！