MySQL 存储过程与函数：创建、语法及核心操作详解

存储过程与函数的核心价值

在日常数据库操作中，许多业务逻辑无法通过单条 SQL 语句完成，往往需要包含判断、循环等流程控制的多步操作。存储过程和函数本质上是事先编译并存储在数据库中的一段 SQL 代码集合，其优势主要体现在以下几个方面：

1. 标准化组件编程：提高 SQL 语句的重用性、共享性和跨平台可移植性。
2. 执行效率提升：作为预编译对象，减少了网络传输流量并加快了执行速度。
3. 安全机制：可以通过权限控制，让应用只调用特定的过程而不直接暴露底层表结构。

需要注意的是，编写和维护这些对象对开发者的技能要求较高，且需要相应的数据库对象创建权限。此外，过度使用可能导致数据库服务器负载增加，特别是在涉及大规模数据遍历时需谨慎设计。

创建存储过程和函数的基础语法

存储过程（Procedure）与函数（Function）的主要区别在于：函数必须有一个返回值，而存储过程则没有；同时存储过程的参数类型（IN/OUT/INOUT）比函数更丰富。

第1步：设置分隔符与创建存储过程

由于 SQL 默认以分号 ; 结束语句，而在编写包含多条语句的过程体时会产生冲突。因此，通常需要先更改命令结束符。

语法结构：

DELIMITER $$
CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] param_name type, ...)
BEGIN
    -- SQL 语句组
END $$
DELIMITER ;

关键特性说明：

• LANGUAGE SQL：指定过程体由 SQL 组成（默认）。
• [NOT] DETERMINISTIC：控制结果是否确定。若相同输入永远产生相同输出则为 DETERMINISTIC。
• SQL SECURITY {DEFINER | INVOKER}：决定执行权限归属。DEFINER 仅限定义者，INVOKER 允许调用者执行（默认通常为 DEFINER）。
• COMMENT：为过程添加注释字符串。

第2步：创建带返回值的函数

函数的创建流程与过程类似，但必须明确 RETURNS 数据类型。

DELIMITER $$
CREATE FUNCTION func_name (param_name type)
RETURNS return_type
BEGIN
    RETURN expression;
END $$
DELIMITER ;

函数参数仅需指定名称和类型（如 int, double），不支持 IN/OUT 修饰符。

第3步：实战示例查询与返回

以下示例展示了创建一个简单查询工资的过程和一个根据员工号返回工资的函数：

示例 A：列出所有雇员工资

DELIMITER $$
CREATE PROCEDURE proce_employee_sal()
COMMENT '查询所有雇员的工资'
BEGIN
    SELECT sal FROM t_employee;
END $$
DELIMITER ;

示例 B：根据工号获取薪资

DELIMITER $$
CREATE FUNCTION func_employee_sal (empno INT(11))
RETURNS DOUBLE(10,2)
COMMENT '查询某个雇员的工资'
BEGIN
    DECLARE emp_salary DOUBLE;
    SELECT sal INTO emp_salary 
    FROM t_employee 
    WHERE empno = empno;
    RETURN emp_salary;
END $$
DELIMITER ;

注意：函数内部可以使用 DECLARE 声明局部变量，并通过 INTO 将查询结果赋值给变量后返回。

存储过程内的核心表达式操作

复杂的业务逻辑需要在过程体内进行变量管理、条件处理和记录遍历。

第4步：声明与赋值变量

声明变量：必须在 BEGIN...END 块的起始位置声明。 DECLARE var_name TYPE [DEFAULT value];

赋值方式：

1. SET 赋值：SET var_name = value;
2. SELECT ... INTO 赋值：用于从表中检索数据存入变量。
   SELECT field INTO var_name FROM table WHERE condition;
   注意：此查询只能返回单行单列结果，否则报错。

第5步：异常处理与条件定义

在处理过程中，需要捕获错误以便控制流程。常用的处理类型包括 CONTINUE（继续执行后续语句）、EXIT（终止当前块执行）。

DECLARE handler_type HANDLER FOR condition_value
    BEGIN
        -- 处理逻辑
    END;

常见的条件速记符：SQLWARNING（以 01 开头的 SQLSTATE）、NOT FOUND（以 02 开头的 SQLSTATE，常用于游标耗尽时）、SQLEXCEPTION（其他所有错误）。

第6步：使用游标遍历结果集

当查询返回多行记录时，需借助游标（Cursor）逐行处理。需注意游标会增加服务器负担，建议避免在万级数据量或连接操作中滥用。

标准操作流程：

1. 声明游标：DECLARE cur_name CURSOR FOR SELECT ...;
2. 打开游标：OPEN cur_name;（此时游标指向第一行之前）。
3. 提取数据：FETCH cur_name INTO var_name;
4. 关闭游标：CLOSE cur_name;

修改与删除维护

随着需求变更，可能需要调整已有对象的属性或直接移除。

第7步：修改属性 (ALTER)

ALTER 语句不能修改过程体的核心逻辑，仅能修改特性（Characteristic），如安全性、注释和数据访问类型。

-- 修改存储过程的安全级别
ALTER PROCEDURE proce_name SQL SECURITY INVOKER;
-- 修改函数特性
ALTER FUNCTION func_name COMMENT 'New Comment';

第8步：彻底删除 (DROP)

一旦对象不再需要，应及时清理以避免资源占用。删除前请务必备份相关 SQL 脚本。

DROP PROCEDURE IF EXISTS proce_name;
DROP FUNCTION IF EXISTS func_name;

使用 IF EXISTS 可以在对象不存在时跳过错误，适合脚本化运维场景。

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