MySQL存储过程示例教程

概述

• MySQL存储过程的示例教程，包含基础语法和实际案例演示

一、存储过程是什么？

存储过程（Stored Procedure）是一组预编译的 SQL 语句，存储在数据库中，可通过名称调用。优势包括：
• 提高性能：预编译减少解析时间

• 复用性：一次编写多次调用

• 安全性：限制直接访问表

• 减少网络流量：批量操作在服务端完成

二、基础语法

1. 创建存储过程

DELIMITER //  -- 修改分隔符，避免与过程中的分号冲突

CREATE PROCEDURE procedure_name([参数列表])
BEGIN
    -- SQL 逻辑
END //

DELIMITER ;  -- 恢复默认分隔符

2. 调用存储过程

CALL procedure_name([参数]);

3. 删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

三、参数类型

• IN（默认）：输入参数

• OUT：输出参数

• INOUT：输入输出参数

四、示例演示

示例 1：无参数的存储过程

DELIMITER //
CREATE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //
DELIMITER ;

-- 调用
CALL GetTotalUsers();

示例 2：带 IN 参数的存储过程

DELIMITER //
CREATE PROCEDURE GetUserByEmail(IN user_email VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE email = user_email;
END //
DELIMITER ;

-- 调用
CALL GetUserByEmail('user@example.com');

示例 3：带 OUT 参数的存储过程

DELIMITER //
CREATE PROCEDURE GetOrderCount(IN customer_id INT, OUT order_count INT)
BEGIN
    SELECT COUNT(*) INTO order_count FROM orders 
    WHERE customer_id = customer_id;
END //
DELIMITER ;

-- 调用
CALL GetOrderCount(123, @count);
SELECT @count AS total_orders;

示例 4：带 INOUT 参数的存储过程

DELIMITER //
CREATE PROCEDURE CapitalizeName(INOUT name VARCHAR(100))
BEGIN
    SET name = CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2)));
END //
DELIMITER ;

-- 调用
SET @name = 'john';
CALL CapitalizeName(@name);
SELECT @name;  -- 输出 'John'

五、流程控制

1. IF 语句

DELIMITER //
CREATE PROCEDURE CheckUserStatus(IN user_id INT)
BEGIN
    DECLARE status VARCHAR(20);
    SELECT active INTO status FROM users WHERE id = user_id;
    
    IF status = 1 THEN
        SELECT 'Active User' AS result;
    ELSE
        SELECT 'Inactive User' AS result;
    END IF;
END //
DELIMITER ;

2. WHILE 循环

DELIMITER //
CREATE PROCEDURE GenerateNumbers(IN max_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    CREATE TEMPORARY TABLE temp_numbers (num INT);
    
    WHILE counter <= max_num DO
        INSERT INTO temp_numbers VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT * FROM temp_numbers;
    DROP TABLE temp_numbers;
END //
DELIMITER ;

-- 调用
CALL GenerateNumbers(5);

六、综合实例：订单处理

DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN product_id INT,
    IN quantity INT,
    OUT total_price DECIMAL(10,2)
)
BEGIN
    DECLARE price DECIMAL(10,2);
    DECLARE stock INT;

    -- 获取商品价格和库存
    SELECT unit_price, units_in_stock INTO price, stock 
    FROM products WHERE id = product_id;

    -- 检查库存
    IF stock < quantity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;

    -- 计算总价
    SET total_price = price * quantity;

    -- 更新库存
    UPDATE products 
    SET units_in_stock = units_in_stock - quantity 
    WHERE id = product_id;

    -- 记录订单
    INSERT INTO orders (product_id, quantity, total_price)
    VALUES (product_id, quantity, total_price);
END //
DELIMITER ;

-- 调用
CALL ProcessOrder(101, 2, @total);
SELECT @total AS order_total;

七、查看存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS;

-- 查看创建语句
SHOW CREATE PROCEDURE procedure_name;

八、注意事项

1. 使用 DECLARE 声明局部变量
2. 通过 SELECT ... INTO 赋值变量
3. 临时表需手动清理或使用 TEMPORARY
4. 使用 SIGNAL 抛出错误信息
5. 避免复杂业务逻辑，保持存储过程简洁

通过以上示例，您可以快速掌握 MySQL 存储过程的基本用法。实际开发中可根据需求组合使用参数、流程控制和 SQL 语句实现复杂业务逻辑。