数据库开发|SQL语法 MariaDB/MySQL存储过程和函数用法详解

🔍 数据库开发 | SQL语法 MariaDB/MySQL存储过程和函数用法详解

📖 引言：为什么需要存储过程和函数？

想象一下,你正在开发一个电商平台🛒，每天要处理成千上万的订单数据，每次查询订单状态、计算用户积分或者更新库存时，都要写一大段重复的SQL代码，不仅容易出错，还影响效率，这时候，存储过程（Stored Procedure）和函数（Function）就派上用场了！

它们就像是数据库里的"小程序"，可以封装复杂的SQL逻辑，提高代码复用性，减少网络传输开销，还能增强安全性🔒，我们就来深入探讨MariaDB/MySQL中存储过程和函数的用法！

🛠️ 存储过程（Stored Procedure）

1️⃣ 什么是存储过程？

存储过程是一组预编译的SQL语句,存储在数据库中，可以被多次调用，它支持参数传递、流程控制（如IF、LOOP、CASE等），甚至能返回多个结果集。

2️⃣ 基本语法

DELIMITER //  -- 修改分隔符，避免与SQL语句冲突  
CREATE PROCEDURE 过程名([IN|OUT|INOUT] 参数名 数据类型, ...)  
BEGIN  
    -- SQL语句  
END //  
DELIMITER ;  -- 恢复默认分隔符  

📌 参数类型：

• IN（默认）：输入参数，存储过程内部可读取但不能修改
• OUT：输出参数，存储过程可修改并返回
• INOUT：既能输入也能输出

3️⃣ 示例：计算订单总价💰

DELIMITER //  
CREATE PROCEDURE CalculateOrderTotal(  
    IN order_id INT,  
    OUT total DECIMAL(10,2)  
)  
BEGIN  
    SELECT SUM(price * quantity) INTO total  
    FROM order_items  
    WHERE order_id = order_id;  
END //  
DELIMITER ;  
-- 调用存储过程  
CALL CalculateOrderTotal(1001, @order_total);  
SELECT @order_total;  -- 查看结果  

4️⃣ 存储过程的优势

✔ 减少网络流量（只需传递参数，不用发送完整SQL）
✔ 提高性能（预编译+缓存执行计划）
✔ 增强安全性（限制直接表访问，通过存储过程操作）

🎯 函数（Function）

1️⃣ 函数 vs. 存储过程

2️⃣ 基本语法

DELIMITER //  
CREATE FUNCTION 函数名(参数 数据类型, ...)  
RETURNS 返回值类型  
[DETERMINISTIC|NOT DETERMINISTIC]  -- 是否依赖外部数据  
BEGIN  
    -- SQL逻辑  
    RETURN 值;  
END //  
DELIMITER ;  

3️⃣ 示例：生成用户昵称🎭

DELIMITER //  
CREATE FUNCTION GenerateNickname(first_name VARCHAR(50))  
RETURNS VARCHAR(100)  
DETERMINISTIC  
BEGIN  
    RETURN CONCAT(first_name, FLOOR(RAND() * 1000));  
END //  
DELIMITER ;  
-- 调用函数  
SELECT GenerateNickname('Alice');  -- 可能输出 "Alice742"  

4️⃣ 注意事项

⚠️ 函数必须是确定性的（相同输入→相同输出），否则可能影响查询优化
⚠️ MySQL 8.0+默认启用binary_log时，需声明DETERMINISTIC或NO SQL等特性

🔥 高级技巧

1️⃣ 流程控制

-- IF语句示例  
CREATE PROCEDURE CheckInventory(IN product_id INT)  
BEGIN  
    DECLARE stock INT;  
    SELECT quantity INTO stock FROM products WHERE id = product_id;  
    IF stock > 0 THEN  
        SELECT 'In stock' AS status;  
    ELSE  
        SELECT 'Out of stock' AS status;  
    END IF;  
END;  

2️⃣ 错误处理

CREATE PROCEDURE SafeDelete(IN user_id INT)  
BEGIN  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION  
    BEGIN  
        ROLLBACK;  
        SELECT 'Error occurred!' AS message;  
    END;  
    START TRANSACTION;  
    DELETE FROM users WHERE id = user_id;  
    COMMIT;  
END;  

3️⃣ 动态SQL

CREATE PROCEDURE DynamicQuery(IN table_name VARCHAR(100))  
BEGIN  
    SET @sql = CONCAT('SELECT * FROM ', table_name);  
    PREPARE stmt FROM @sql;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
END;  

🚀 最佳实践

1. 命名规范：使用sp_前缀表示存储过程，fn_表示函数（如sp_CalculateTax）
2. 注释代码：用或说明复杂逻辑
3. 避免过度使用：简单查询直接写SQL更高效
4. 版本控制：将存储过程脚本纳入Git管理

存储过程和函数是数据库开发的超级武器💪：

• 存储过程适合封装复杂业务逻辑，特别是需要多次调用的操作
• 函数适合计算和返回单一值，能直接在SQL语句中使用

掌握它们,你的数据库代码将变得更模块化、更高效！现在就去试试写你的第一个存储过程吧～ 🎉

（本文基于MariaDB 10.6和MySQL 8.0验证，2025-08更新）