首页 / MySQL 入门教程 / 存储过程与自定义函数

← 上一篇 下一篇 →
MySQL 入门教程

存储过程与自定义函数

本教程共 46 篇 · 第 35 篇 · 更新于 2026-07-30 · 约 12 分钟阅读

MySQLMySQL 入门教程存储过程PROCEDURE函数FUNCTIONDELIMITER流程控制

35. 存储过程与自定义函数

本节目标：学会创建和调用存储过程（PROCEDURE）与自定义函数（FUNCTION），掌握 IN/OUT/INOUT 参数和流程控制语法，理解两者的区别和各自适用场景。

35.1 什么是存储过程

存储过程（Stored Procedure） 是一组预先编译好并存储在数据库中的 SQL 语句集合。你给它起个名字，调用时传入参数，它就执行里面的一串逻辑。

说白了，就是把多条 SQL 打包成一个”方法”，需要时调用。

Note

存储过程在数据库服务器端执行，减少了客户端和服务器之间的网络往返。适合批量操作、复杂业务逻辑等场景。
来源：MySQL 官方文档 - CREATE PROCEDURE

35.2 DELIMITER：改分隔符

创建存储过程时，过程体内有多条以分号结尾的 SQL。但 MySQL 命令行遇到分号就会执行，导致过程体写到一半就被截断。

解决办法是临时改一下语句分隔符：

-- 把分隔符改成 //
DELIMITER //

CREATE PROCEDURE test_proc()
BEGIN
    SELECT 'Hello';
    SELECT 'World';
END //  -- 这里用 // 结束整个 CREATE PROCEDURE

-- 改回分号
DELIMITER ;
Tip

DELIMITER 只影响 mysql 客户端的解析，不是 SQL 语法。用 // 还是 $$ 都行，习惯上用 //。

35.3 创建和调用存储过程

先准备数据：

DELIMITER //

-- 创建存储过程：查询某用户的订单总数
CREATE PROCEDURE get_user_order_count(
    IN p_user_id INT
)
BEGIN
    SELECT COUNT(*) AS order_count
    FROM orders
    WHERE user_id = p_user_id;
END //

DELIMITER ;

调用存储过程用 CALL：

-- 调用
CALL get_user_order_count(1);

35.4 参数：IN / OUT / INOUT

存储过程参数有三种模式：

模式说明
IN传入参数，过程内只读（默认）
OUT传出参数，过程内赋值，调用方接收结果
INOUT既传入又传出
DELIMITER //

-- IN 参数：传入用户ID
-- OUT 参数：传出订单总数和总金额
CREATE PROCEDURE get_user_stats(
    IN p_user_id INT,
    OUT p_order_count INT,
    OUT p_total_amount DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*) INTO p_order_count
    FROM orders WHERE user_id = p_user_id;

    SELECT COALESCE(SUM(amount), 0) INTO p_total_amount
    FROM orders WHERE user_id = p_user_id;
END //

DELIMITER ;

调用带 OUT 参数的过程，要先定义变量接收：

-- 用 @变量接收输出
CALL get_user_stats(1, @count, @total);

-- 查看结果
SELECT @count AS order_count, @total AS total_amount;
Note

SELECT ... INTO @变量 是 MySQL 中把查询结果存入变量的语法。OUT 参数的值在过程执行完后赋给调用方传入的变量。

35.5 流程控制语句

存储过程体内可以使用流程控制语句，让它具备编程能力。

IF…THEN…ELSE

DELIMITER //
CREATE PROCEDURE check_age(
    IN p_age INT,
    OUT p_result VARCHAR(20)
)
BEGIN
    IF p_age < 18 THEN
        SET p_result = '未成年';
    ELSEIF p_age >= 18 AND p_age < 60 THEN
        SET p_result = '成年人';
    ELSE
        SET p_result = '老年人';
    END IF;
END //
DELIMITER ;

CALL check_age(25, @result);
SELECT @result;  -- 成年人

CASE

DELIMITER //
CREATE PROCEDURE get_order_label(
    IN p_status VARCHAR(20),
    OUT p_label VARCHAR(50)
)
BEGIN
    CASE p_status
        WHEN 'pending' THEN SET p_label = '待处理';
        WHEN 'active' THEN SET p_label = '进行中';
        WHEN 'completed' THEN SET p_label = '已完成';
        WHEN 'cancelled' THEN SET p_label = '已取消';
        ELSE SET p_label = '未知状态';
    END CASE;
END //
DELIMITER ;

WHILE 循环

DELIMITER //
CREATE PROCEDURE insert_test_data(
    IN p_count INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= p_count DO
        INSERT INTO users(username, email, age)
        VALUES(CONCAT('user_', i), CONCAT('user_', i, '@test.com'), 20 + i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 批量插入 100 条测试数据
CALL insert_test_data(100);

LOOP 和 REPEAT

-- LOOP（无条件循环，需要自己 LEAVE 跳出）
DELIMITER //
CREATE PROCEDURE loop_example()
BEGIN
    DECLARE i INT DEFAULT 0;
    my_loop: LOOP
        SET i = i + 1;
        IF i > 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    SELECT i;
END //
DELIMITER ;

-- REPEAT（先执行再判断，类似 do...while）
DELIMITER //
CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE i INT DEFAULT 0;
    REPEAT
        SET i = i + 1;
    UNTIL i >= 10 END REPEAT;
    SELECT i;
END //
DELIMITER ;
循环特点类比
WHILE先判断后执行while
REPEAT先执行后判断do…while
LOOP无条件循环，靠 LEAVE 跳出while(true) + break

35.6 自定义函数（FUNCTION）

自定义函数（Stored Function） 和存储过程类似，但有一个核心区别：函数必须返回一个值，可以直接用在 SQL 表达式中。

DELIMITER //
CREATE FUNCTION calc_discount(
    p_amount DECIMAL(10,2),
    p_rate DECIMAL(3,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_amount * p_rate;
END //
DELIMITER ;
-- 直接在 SELECT 中调用函数
SELECT id, amount, calc_discount(amount, 0.8) AS discounted
FROM orders
WHERE status = 'pending';
Note

DETERMINISTIC 表示相同输入总返回相同输出。MySQL 默认不允许创建非 DETERMINISTIC 的函数（除非开启 log_bin_trust_function_creators），这是为了不影响主从复制的一致性。

35.7 存储过程 vs 函数

特性存储过程（PROCEDURE）函数（FUNCTION）
返回值可有可无（通过 OUT 参数）必须返回一个值
调用方式CALL proc_name()嵌入 SQL 表达式中
能否在 SELECT 中用不能能
参数模式IN / OUT / INOUT只有 IN（默认）
事务控制可以包含 COMMIT/ROLLBACK不可以
返回结果集可以返回多结果集不可以
Tip

简单记忆：需要返回一个值并在 SQL 中直接使用，用函数；需要执行复杂逻辑、可能返回多个结果或操作多张表，用存储过程。

35.8 查看和删除

-- 查看存储过程列表
SHOW PROCEDURE STATUS WHERE Db = '你的库名';

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_user_order_count\G

-- 查看函数列表
SHOW FUNCTION STATUS WHERE Db = '你的库名';

-- 查看函数定义
SHOW CREATE FUNCTION calc_discount\G

-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_order_count;

-- 删除函数
DROP FUNCTION IF EXISTS calc_discount;

35.9 变量声明

存储过程里用变量分两类：

局部变量（过程内部声明）：

DECLARE v_count INT DEFAULT 0;
DECLARE v_name VARCHAR(50) DEFAULT '';

用户变量（会话级别，@ 开头）：

SET @total = 0;
SELECT COUNT(*) INTO @total FROM users;
特性局部变量（DECLARE）用户变量（@var）
作用域BEGIN…END 块内整个会话
类型声明时指定不用声明类型
初始化可用 DEFAULT默认 NULL

35.10 游标（Cursor）

需要逐行处理查询结果时，用游标：

DELIMITER //
CREATE PROCEDURE process_users()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_username VARCHAR(50);
    DECLARE v_age INT;

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT id, username, age FROM users WHERE age > 18;

    -- 声明游标结束时的处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id, v_username, v_age;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 对每行数据做处理
        INSERT INTO logs(user_id, info) VALUES(v_id, CONCAT(v_username, ' age=', v_age));
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;
Warning

游标效率不高，因为它逐行处理，不能利用批量操作的优势。能用一条 SQL 解决的就不要用游标。只有在确实需要逐行处理复杂逻辑时才用。

35.11 小结

  • 存储过程把多条 SQL 打包成可复用的程序，用 CALL 调用；
  • DELIMITER 改语句分隔符，避免过程体被截断；
  • 参数三种模式：IN 传入、OUT 传出、INOUT 传入传出；
  • 流程控制：IF/CASE 做条件判断，WHILE/REPEAT/LOOP 做循环；
  • 自定义函数必须返回值，可嵌入 SQL 表达式中使用；
  • 游标用于逐行处理，但效率低于批量操作，谨慎使用。

下一节讲触发器（TRIGGER），在数据变更时自动执行逻辑。

上一篇
视图 VIEW
下一篇
触发器 TRIGGER