MySQL 存储过程与函数详解：创建、变量定义及异常处理

结果或目标

在数据库应用中，将复杂的业务逻辑封装为存储过程或函数，可以显著减少网络传输量并提高执行效率。通过掌握其创建语法和异常处理机制，开发者能够构建具备容错能力的数据库应用模块。

准备条件

• 已安装 MySQL 数据库服务并拥有相应权限账户。
• 确保客户端连接工具支持多语句执行或正确的结束符设置。

真实操作顺序

第1步：修改语句结束符

目的：解决存储过程体内包含分号导致的语句提前终止冲突。

MySQL 默认以分号 ; 作为命令结束符。但在定义包含多条 SQL 语句的过程体时，需要在体内外使用不同的结束符。

1. 将结束符临时修改为非分号字符（如 &&）。
2. 输入存储过程定义的完整代码，其中内部的分号仍正常书写。
3. 使用原结束符 ; 完成定义。
4. 将结束符恢复为默认的 ;。

DELIMITER &&
-- 在此处编写 CREATE PROCEDURE/FUNCTION 语句，内部可使用 ;
DELIMITER ;

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

入口：MySQL 命令行或管理工具。

动作：使用 CREATE PROCEDURE 关键字，指定名称、参数及过程体。

重要参数说明：

• sp_name：过程名称，全局唯一。
• proc_parameter：由 [ IN | OUT | INOUT ] param_name type 组成。IN为传入，OUT为返回，INOUT为双向交互。
• characteristic：可选属性。常用 READS SQL DATA 表示读取数据；DETERMINISTIC/NOT DETERMINISTIC 指明执行结果的确定性；COMMENT 用于添加注释。
• routine_body：用 BEGIN ... END 包裹的实际 SQL 逻辑。

示例：创建统计员工数量的过程。

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
    READS SQL DATA 
BEGIN 
    SELECT COUNT(*) INTO count_num 
    FROM employee 
    WHERE d_id=emp_id ; 
END

完成判断：执行无报错并显示 Query OK, 0 rows affected 即为成功。

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

区别：存储函数必须有返回值，且只能有 IN 参数，通常用于标量计算而非复杂控制流。

动作：使用 CREATE FUNCTION，必须包含 RETURNS type 子句，并在 BEGIN...END 中使用 RETURN 关键字。

CREATE FUNCTION name_from_employee (emp_id INT) 
    RETURNS VARCHAR(20) 
BEGIN 
    RETURN (SELECT name FROM employee WHERE num=emp_id); 
END

第4步：在过程体中定义与使用变量

目的：在局部作用域内暂存中间计算结果。

入口：过程体的 BEGIN 之后。

步骤：

1. 声明变量：使用 DECLARE var_name type [DEFAULT value]。变量仅在当前 BEGIN-END 块有效。
2. 赋值：
   • 直接赋值：SET var_name = expr;
   • 查询赋值：SELECT col_name INTO var_name FROM table_name WHERE condition;

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

目的：增强程序的鲁棒性，避免运行中因错误导致进程意外终止或留下脏数据。

动作：在过程体中按以下顺序定义：

1. 定义条件：关联特定的错误码或 SQLSTATE 值。
   DECLARE condition_name CONDITION FOR SQLSTATE '...' | mysql_error_code;
2. 定义处理器：指定错误发生时的行为（CONTINUE 继续执行 / EXIT 退出块）。
   DECLARE handler_type HANDLER FOR condition_value sp_statement;

常见取值：

• SQLWARNING：捕获所有以 01 开头的状态。
• NOT FOUND：捕获所有以 02 开头的状态（常用于游标结束判定）。
• SQLEXCEPTION：捕获其他所有 SQL 异常。

示例：捕获“表不存在”错误。

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='Error: Table not found';

简要总结

熟练掌握 MySQL 存储过程和函数的编写，关键在于规范使用 DELIMITER、清晰划分参数流向，并通过 DECLARE 机制妥善管理局部变量与潜在异常。合理的异常处理能显著提升数据库应用的可靠性。

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