MySQL存储过程创建、参数调用与流程控制完整指南

概述与核心机制

自 MySQL 5.0 版本起全面支持存储过程。存储过程本质上是存储在数据库中、经过预编译的一段 SQL 代码集合，类似于编程语言中的“函数”。调用存储过程不仅能简化应用程序的开发工作，还能有效降低数据在应用服务器与数据库之间的传输量，从而提升整体处理性能。

存储过程通过以下三种参数类型实现与应用系统的数据交互：

• IN 类型：输入参数，由调用者传入值至存储过程内部使用。
• OUT 类型：输出参数，存储过程计算后将结果返回给调用者。
• INOUT 类型：输入输出双向参数，既接受调用者的初始值，也可将处理后的结果传回调用者。

优缺点分析

在决定是否使用存储过程前，需权衡其带来的利弊：

主要优点：

1. 简化复杂操作：将繁琐的 SQL 逻辑封装在数据库内部，应用端只需发送简单调用指令。
2. 易于维护：当表结构或业务逻辑变更时，仅需修改存储过程代码，无需改动前端应用代码。
3. 性能优化：MySQL 对存储过程采用按需编译并将其缓存于内存中，高频调用时可利用编译后的版本，减少解析开销。
4. 流量节省：避免了在应用中拼装长串 SQL 发送给数据库，大幅减少网络带宽消耗。
5. 安全性强：DBA 可仅授予存储过程执行权限，而无需直接开放底层表的增删改查权限。

主要缺点：

1. 资源占用：大量存储过程会显著增加连接进程的内存占用；若包含复杂逻辑运算，也会增加 CPU 负担，因为 MySQL 设计初衷更侧重于 IO 密集型查询而非逻辑计算。
2. 调试困难：MySQL 原生缺乏完善的存储过程调试功能，排查逻辑错误较为繁琐。
3. 移植性差：不同数据库厂商（如 Oracle 与 MySQL）的语法存在差异，迁移成本高。

存储过程基本定义与格式

在 MySQL 中创建存储过程必须使用 DELIMITER 修改定界符，以便区分过程体内的分号与语句结束符。基本结构如下：

DELIMITER $$
CREATE PROCEDURE `库名`.`过程名`(IN param_in VARCHAR(10), OUT result_count INT)
BEGIN
    -- 变量声明
    DECLARE temp_var INT DEFAULT 0;
    
    -- 逻辑处理块
    SET temp_var = 1;
END$$
DELIMITER ;

关键要素说明：

• IN：默认参数类型，用于向过程内传递数据。
• OUT：用于向外返回计算结果。
• DECLARE：用于声明局部变量，可通过 DEFAULT 指定默认值。
• BEGIN ... END：包裹所有过程体执行的 SQL 语句。

第1步：创建无参存储过程

目的是验证环境配置及基础语法。创建一个简单的 Demo 过程并在其中定义变量。

DELIMITER $$
CREATE PROCEDURE `demo`.`demo1`()
BEGIN
    DECLARE de_name VARCHAR(10) DEFAULT '';
    SET de_name = 'jim';
    SELECT de_name AS output_result;
END$$
DELIMITER ;

调用时使用 CALL 关键字：

CALL demo1();

第2步：创建带参数的存储过程

假设需要统计 student 表中特定性别的学生数量。此过程涉及 IN 参数接收筛选条件，以及 INTO 将查询结果赋值给 OUT 参数。

DELIMITER $$
CREATE PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1), OUT s_count INT)
BEGIN
    -- 使用 INTO 将聚合结果赋给输出参数
    SELECT COUNT(*) INTO s_count FROM student WHERE sex = s_sex;
    SELECT s_count; -- 便于控制台观察
END$$
DELIMITER ;

调用时需要传入参数，并使用会话级变量（以 @ 开头）来接收输出参数：

-- 调用并获取输出结果存入 @s_count
CALL demo2('男', @s_count);
SELECT @s_count;

流程控制语句应用

存储过程中支持标准的逻辑分支与循环结构，极大增强了数据处理能力。

条件判断：IF ELSE

类似于 Java/C# 中的 if-else 结构，适用于多路分支场景。

DELIMITER $$
CREATE PROCEDURE `demo`.`demo3`(IN `day` INT)
BEGIN
    IF `day` = 0 THEN
        SELECT '星期天';
    ELSEIF `day` = 1 THEN
        SELECT '星期一';
    ELSEIF `day` = 2 THEN
        SELECT '星期二';
    ELSE
        SELECT '无效日期';
    END IF;
END$$
DELIMITER ;

调用示例：

CALL demo3(2);

条件判断：CASE WHEN

CASE 语句分为两种写法：搜索型 CASE 和简写型 CASE。

1. 搜索型 CASE（类似 switch-case）：

DELIMITER $$
CREATE PROCEDURE demo4(IN num INT)
BEGIN
    CASE 
        WHEN num < 0 THEN 
            SELECT '负数';
        WHEN num > 0 THEN 
            SELECT '正数';
        ELSE 
            SELECT '不是正数也不是负数';
    END CASE;
END$$
DELIMITER ;

调用：

CALL demo4(1);

2. 简写型 CASE：

DELIMITER $$
CREATE PROCEDURE demo5(IN num INT)
BEGIN
    CASE num
        WHEN 1 THEN 
            SELECT '输入为1';
        WHEN 0 THEN 
            SELECT '输入为0';
        ELSE 
            SELECT '不是1也不是0';
    END CASE;
END$$
DELIMITER ;

调用：

CALL demo5(0);

循环结构：WHILE

先判断条件，满足条件后执行循环体。适合已知迭代次数的场景。

DELIMITER $$
CREATE PROCEDURE demo6(IN num INT, OUT SUM INT)
BEGIN
    SET SUM = 0;
    WHILE num < 10 DO
        SET num = num + 1;
        SET SUM = SUM + num;
    END WHILE;
END$$
DELIMITER ;

调用：

CALL demo6(0, @sum);
SELECT @sum;

循环结构：REPEAT UNTIL

类似于 do-while，先执行循环体，再检查条件。当条件为 TRUE 时停止循环。

DELIMITER $$
CREATE PROCEDURE demo7(IN num INT, OUT SUM INT)
BEGIN
    SET SUM = 0;
    REPEAT
        SET num = num + 1;
        SET SUM = SUM + num;
    UNTIL num >= 10
    END REPEAT;
END$$
DELIMITER ;

调用：

CALL demo7(9, @sum);
SELECT @sum;

循环结构：LOOP

无条件循环，通常需要配合 LEAVE（类似 break）和 ITERATE（类似 continue）手动控制退出或跳过。

DELIMITER $$
CREATE PROCEDURE demo8(IN num INT, OUT SUM INT)
BEGIN
    SET SUM = 0;
    demo_sum: LOOP
        SET num = num + 1;
        IF num > 10 THEN
            LEAVE demo_sum;      -- 终止整个循环
        ELSEIF num < 9 THEN
            ITERATE demo_sum;    -- 跳过本次后续代码，进入下一次循环
        END IF;
        SET SUM = SUM + num;
    END LOOP demo_sum;
END$$
DELIMITER ;

调用：

CALL demo8(0, @sum);
SELECT @sum;

实战：基于存储过程的安全插入

在实际开发中，直接在应用层做唯一性校验可能存在并发竞争问题。利用存储过程可以在数据库原子层面完成“查-判-插”操作。

DELIMITER $$
CREATE PROCEDURE demo9(IN s_student VARCHAR(10), IN s_sex CHAR(1), OUT s_result VARCHAR(20))
BEGIN
    DECLARE s_count INT DEFAULT 0;
    -- 检查姓名是否已存在
    SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;
    
    IF s_count = 0 THEN
        INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
        SET s_result = '数据添加成功';
    ELSE
        SET s_result = '名字已存在，不能添加';
    END IF;
    
    SELECT s_result;
END$$
DELIMITER ;

首次调用：

CALL demo9('Jim', '女', @s_result);

再次调用同名用户：

CALL demo9('Jim', '女', @s_result);

存储过程的日常管理与运维

熟悉相关管理命令有助于后续的维护与排错。

• 查看所有存储过程列表：
  SHOW PROCEDURE STATUS;

• 查看特定库和名的过程：
  SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = '过程名';
• 模糊匹配过程名称：
  SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

• 查看源代码（DDL）：
  SHOW CREATE PROCEDURE 过程名;

• 删除不需要的过程：
  DROP PROCEDURE 过程名;

后端集成：MyBatis 调用实现

在 Java Web 项目中，通常使用 MyBatis 作为 ORM 框架。以下是 MyBatis XML 映射文件中调用存储过程的标准写法：

<!-- 参数映射配置 -->
<parameterMap type="map" id="userCallMap">
    <parameter property="name" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="sex" jdbcType="CHAR" mode="IN"/>
    <parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

<!-- 调用语句定义，注意 statementType 设置为 CALLABLE -->
<insert id="saveUserDemo" parameterMap="userCallMap" statementType="CALLABLE">
    {call demo9(?, ?, ?)} 
</insert>

Java 调用方式：

HashMap<String, Object> map = new HashMap<>(); 
map.put("name", "Jim"); 
map.put("sex", "男"); 
// 执行调用
userDao.saveUserDemo(map); 
// 从 Map 中取出 OUT 参数结果
String outResult = (String) map.get("result"); 

通过上述配置，MyBatis 会正确处理 JDBC CallableStatement，自动将传入的参数绑定，并将数据库输出的结果回填至 Map 对象中供业务逻辑使用。

以上就是 MySQL 中存储过程的详细创建、流程控制与后端调用的全部内容，更多关于数据库高级特性的资料请关注本站其它相关文章！