知识库

Appearance

Return to top
On this page

MySQL存储过程

存储过程是一组预编译的SQL语句，存储在数据库中，可以通过名称调用执行。存储过程提供了模块化、可重用的数据库编程功能，能够提高性能、增强安全性并简化复杂操作。

存储过程基础

创建存储过程

sql
-- 基本语法
DELIMITER //
CREATE PROCEDURE procedure_name([parameter_list])
BEGIN
    -- SQL语句
END //
DELIMITER ;

-- 简单示例：获取用户总数
DELIMITER //
CREATE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //
DELIMITER ;

-- 调用存储过程
CALL GetTotalUsers();

存储过程参数

存储过程支持三种类型的参数：IN（输入）、OUT（输出）、INOUT（输入输出）。

sql
-- IN参数：输入参数
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- OUT参数：输出参数
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END //
DELIMITER ;

-- 调用并获取输出参数
CALL GetUserCount(@count);
SELECT @count;

-- INOUT参数：输入输出参数
DELIMITER //
CREATE PROCEDURE DoubleNumber(INOUT num INT)
BEGIN
    SET num = num * 2;
END //
DELIMITER ;

-- 调用INOUT参数
SET @value = 5;
CALL DoubleNumber(@value);
SELECT @value; -- 结果为10

存储过程变量

局部变量

sql
DELIMITER //
CREATE PROCEDURE ProcessUser(IN user_id INT)
BEGIN
    -- 声明局部变量
    DECLARE user_name VARCHAR(100);
    DECLARE user_email VARCHAR(100);
    DECLARE created_date DATE;
    
    -- 为变量赋值
    SELECT username, email, DATE(created_at) 
    INTO user_name, user_email, created_date
    FROM users 
    WHERE id = user_id;
    
    -- 使用变量
    SELECT user_name, user_email, created_date;
END //
DELIMITER ;

用户变量

sql
-- 用户变量在会话级别可用
SET @global_count = 0;

DELIMITER //
CREATE PROCEDURE UpdateGlobalCount()
BEGIN
    SET @global_count = @global_count + 1;
END //
DELIMITER ;

控制结构

条件语句

sql
-- IF-ELSE语句
DELIMITER //
CREATE PROCEDURE CheckUserStatus(IN user_id INT)
BEGIN
    DECLARE user_status VARCHAR(20);
    DECLARE message VARCHAR(200);
    
    SELECT status INTO user_status FROM users WHERE id = user_id;
    
    IF user_status = 'active' THEN
        SET message = '用户状态正常';
    ELSEIF user_status = 'inactive' THEN
        SET message = '用户已停用';
    ELSE
        SET message = '用户状态未知';
    END IF;
    
    SELECT message;
END //
DELIMITER ;

-- CASE语句
DELIMITER //
CREATE PROCEDURE GetUserCategory(IN user_id INT)
BEGIN
    DECLARE user_age INT;
    DECLARE category VARCHAR(20);
    
    SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) INTO user_age 
    FROM users WHERE id = user_id;
    
    CASE 
        WHEN user_age < 18 THEN SET category = '未成年';
        WHEN user_age BETWEEN 18 AND 65 THEN SET category = '成年人';
        ELSE SET category = '老年人';
    END CASE;
    
    SELECT category;
END //
DELIMITER ;

循环语句

sql
-- WHILE循环
DELIMITER //
CREATE PROCEDURE ProcessNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    WHILE counter <= 10 DO
        SET result = CONCAT(result, counter, ',');
        SET counter = counter + 1;
    END WHILE;
    
    SELECT result;
END //
DELIMITER ;

-- REPEAT循环
DELIMITER //
CREATE PROCEDURE ProcessWithRepeat()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    REPEAT
        SET result = CONCAT(result, counter, ',');
        SET counter = counter + 1;
    UNTIL counter > 10 END REPEAT;
    
    SELECT result;
END //
DELIMITER ;

-- LOOP循环
DELIMITER //
CREATE PROCEDURE ProcessWithLoop()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    loop_label: LOOP
        IF counter > 10 THEN
            LEAVE loop_label;
        END IF;
        
        SET result = CONCAT(result, counter, ',');
        SET counter = counter + 1;
    END LOOP;
    
    SELECT result;
END //
DELIMITER ;

游标使用

游标用于逐行处理结果集。

sql
DELIMITER //
CREATE PROCEDURE ProcessUserOrders(IN user_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_amount DECIMAL(10,2);
    DECLARE order_date DATE;
    
    -- 声明游标
    DECLARE order_cursor CURSOR FOR 
        SELECT id, amount, DATE(created_at) 
        FROM orders 
        WHERE user_id = user_id;
    
    -- 声明继续处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE temp_user_orders (
        order_id INT,
        amount DECIMAL(10,2),
        order_date DATE
    );
    
    OPEN order_cursor;
    
    read_loop: LOOP
        FETCH order_cursor INTO order_id, order_amount, order_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO temp_user_orders VALUES (order_id, order_amount, order_date);
    END LOOP;
    
    CLOSE order_cursor;
    
    -- 返回结果
    SELECT * FROM temp_user_orders;
    DROP TEMPORARY TABLE temp_user_orders;
END //
DELIMITER ;

错误处理

异常处理

sql
DELIMITER //
CREATE PROCEDURE SafeTransfer(
    IN from_user_id INT, 
    IN to_user_id INT, 
    IN transfer_amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出异常
    END;
    
    START TRANSACTION;
    
    -- 检查转出账户余额
    SET @balance = (SELECT balance FROM accounts WHERE user_id = from_user_id FOR UPDATE);
    IF @balance < transfer_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 执行转账
    UPDATE accounts SET balance = balance - transfer_amount WHERE user_id = from_user_id;
    UPDATE accounts SET balance = balance + transfer_amount WHERE user_id = to_user_id;
    
    -- 记录转账日志
    INSERT INTO transfer_logs (from_user, to_user, amount, transfer_time) 
    VALUES (from_user_id, to_user_id, transfer_amount, NOW());
    
    COMMIT;
END //
DELIMITER ;

自定义错误

sql
DELIMITER //
CREATE PROCEDURE ValidateUser(IN user_email VARCHAR(100))
BEGIN
    DECLARE user_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO user_count FROM users WHERE email = user_email;
    
    IF user_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
    ELSEIF user_count > 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据异常：存在重复邮箱';
    END IF;
    
    SELECT '用户验证通过' AS result;
END //
DELIMITER ;

复杂存储过程示例

用户注册存储过程

sql
DELIMITER //
CREATE PROCEDURE RegisterUser(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_password VARCHAR(255),
    OUT p_user_id INT,
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '注册失败：系统错误';
        SET p_user_id = 0;
    END;
    
    START TRANSACTION;
    
    -- 检查用户名是否已存在
    IF EXISTS(SELECT 1 FROM users WHERE username = p_username) THEN
        SET p_result = '用户名已存在';
        SET p_user_id = 0;
        ROLLBACK;
    ELSEIF EXISTS(SELECT 1 FROM users WHERE email = p_email) THEN
        -- 检查邮箱是否已存在
        SET p_result = '邮箱已被注册';
        SET p_user_id = 0;
        ROLLBACK;
    ELSE
        -- 插入用户信息
        INSERT INTO users (username, email, password, created_at) 
        VALUES (p_username, p_email, p_password, NOW());
        
        SET p_user_id = LAST_INSERT_ID();
        
        -- 创建用户资料
        INSERT INTO user_profiles (user_id, created_at) 
        VALUES (p_user_id, NOW());
        
        -- 分配默认角色
        INSERT INTO user_roles (user_id, role_id) 
        VALUES (p_user_id, 1); -- 假设1是普通用户角色
        
        SET p_result = '注册成功';
        COMMIT;
    END IF;
END //
DELIMITER ;

-- 调用示例
CALL RegisterUser('newuser', 'newuser@example.com', 'password123', @user_id, @result);
SELECT @user_id, @result;

订单处理存储过程

sql
DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_result VARCHAR(200)
)
BEGIN
    DECLARE v_product_price DECIMAL(10,2);
    DECLARE v_product_stock INT;
    DECLARE v_total_amount DECIMAL(10,2);
    DECLARE v_user_balance DECIMAL(10,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '订单处理失败：系统错误';
        SET p_order_id = 0;
    END;
    
    START TRANSACTION;
    
    -- 获取商品信息
    SELECT price, stock INTO v_product_price, v_product_stock 
    FROM products 
    WHERE id = p_product_id AND status = 'active' FOR UPDATE;
    
    -- 检查商品是否存在
    IF v_product_price IS NULL THEN
        SET p_result = '商品不存在或已下架';
        SET p_order_id = 0;
        ROLLBACK;
    ELSEIF v_product_stock < p_quantity THEN
        -- 检查库存是否充足
        SET p_result = CONCAT('库存不足，当前库存：', v_product_stock);
        SET p_order_id = 0;
        ROLLBACK;
    ELSE
        -- 计算总金额
        SET v_total_amount = v_product_price * p_quantity;
        
        -- 获取用户余额
        SELECT balance INTO v_user_balance 
        FROM accounts 
        WHERE user_id = p_user_id FOR UPDATE;
        
        -- 检查余额是否充足
        IF v_user_balance < v_total_amount THEN
            SET p_result = CONCAT('余额不足，当前余额：', v_user_balance, '，需要：', v_total_amount);
            SET p_order_id = 0;
            ROLLBACK;
        ELSE
            -- 创建订单
            INSERT INTO orders (user_id, total_amount, status, created_at) 
            VALUES (p_user_id, v_total_amount, 'pending', NOW());
            
            SET p_order_id = LAST_INSERT_ID();
            
            -- 创建订单详情
            INSERT INTO order_items (order_id, product_id, quantity, price) 
            VALUES (p_order_id, p_product_id, p_quantity, v_product_price);
            
            -- 更新库存
            UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;
            
            -- 扣减用户余额
            UPDATE accounts SET balance = balance - v_total_amount WHERE user_id = p_user_id;
            
            -- 记录余额变动
            INSERT INTO balance_logs (user_id, amount, type, related_id, description) 
            VALUES (p_user_id, -v_total_amount, 'order', p_order_id, '订单支付');
            
            SET p_result = '订单创建成功';
            UPDATE orders SET status = 'confirmed' WHERE id = p_order_id;
            COMMIT;
        END IF;
    END IF;
END //
DELIMITER ;

存储过程管理

查看存储过程

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

-- 查看特定数据库的存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

-- 查看存储过程定义
SHOW CREATE PROCEDURE procedure_name;

-- 从information_schema查看
SELECT routine_name, routine_type, created, last_altered
FROM information_schema.routines 
WHERE routine_schema = 'your_database_name' 
AND routine_type = 'PROCEDURE';

修改和删除存储过程

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

-- 如果需要修改存储过程，需要先删除再重新创建
DROP PROCEDURE IF EXISTS GetUserById;
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT id, username, email, created_at 
    FROM users 
    WHERE id = user_id AND status = 'active';
END //
DELIMITER ;

存储函数

存储函数与存储过程类似，但必须返回一个值。

sql
DELIMITER //
CREATE FUNCTION GetUserOrderCount(user_id INT) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE order_count INT;
    
    SELECT COUNT(*) INTO order_count 
    FROM orders 
    WHERE user_id = user_id;
    
    RETURN IFNULL(order_count, 0);
END //
DELIMITER ;

-- 使用存储函数
SELECT username, GetUserOrderCount(id) as order_count 
FROM users 
WHERE id IN (1, 2, 3);

性能优化

预编译优势

存储过程在创建时被预编译，执行时无需再次解析，提高性能。

减少网络传输

sql
-- 不使用存储过程：多次网络往返
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO transactions VALUES (...);

-- 使用存储过程：一次网络往返
CALL TransferMoney(1, 2, 100);

执行计划缓存

MySQL会缓存存储过程的执行计划，提高重复执行的效率。

安全考虑

权限管理

sql
-- 授予执行存储过程的权限
GRANT EXECUTE ON PROCEDURE database.procedure_name TO 'username'@'host';

-- 授予所有存储过程的执行权限
GRANT EXECUTE ON database.* TO 'username'@'host';

SQL注入防护

存储过程的参数化查询天然防止SQL注入：

sql
-- 安全的参数化查询
DELIMITER //
CREATE PROCEDURE SearchUsers(IN search_term VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE username LIKE CONCAT('%', search_term, '%');
END //
DELIMITER ;

最佳实践

1. 命名规范

sql
-- 推荐的命名约定
DELIMITER //
CREATE PROCEDURE sp_GetUserOrders(IN p_user_id INT)  -- 前缀sp_表示存储过程
BEGIN
    -- 过程体
END //
DELIMITER ;

2. 参数验证

sql
DELIMITER //
CREATE PROCEDURE ValidateAndProcess(IN p_input INT)
BEGIN
    -- 参数验证
    IF p_input IS NULL OR p_input <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '参数无效';
    END IF;
    
    -- 业务逻辑
    -- ...
END //
DELIMITER ;

3. 事务管理

sql
DELIMITER //
CREATE PROCEDURE ComplexOperation()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 多个相关操作
    -- ...
    
    COMMIT;
END //
DELIMITER ;

与应用程序集成

从应用程序调用

sql
-- 示例：从应用程序调用存储过程
-- 在Java中使用JDBC：
-- CallableStatement stmt = connection.prepareCall("{call ProcessOrder(?, ?, ?, ?, ?)}");
-- stmt.setInt(1, userId);
-- stmt.setInt(2, productId);
-- stmt.setInt(3, quantity);
-- stmt.registerOutParameter(4, Types.INTEGER);
-- stmt.registerOutParameter(5, Types.VARCHAR);
-- stmt.execute();

总结

存储过程是MySQL中强大的编程功能，提供了以下优势：

  1. 性能提升：预编译和执行计划缓存
  2. 安全性：参数化查询防止SQL注入，权限控制
  3. 模块化：可重用的代码单元
  4. 数据完整性：在数据库层面强制业务规则
  5. 减少网络流量：批量操作只需一次网络调用

在使用存储过程时，需要注意：

  1. 适当的错误处理和事务管理
  2. 合理的参数验证
  3. 性能优化和监控
  4. 安全性考虑
  5. 维护和文档

通过合理使用存储过程，可以构建高效、安全、可维护的数据库应用程序。