Java优学网MySQL存储过程详解：从基础语法到实战应用，轻松掌握数据库操作技巧

DELIMITER // CREATE PROCEDURE 存储过程名称(参数列表) BEGIN

-- 这里写具体的SQL逻辑

END // DELIMITER ;

CREATE PROCEDURE sp_UpdateUserScore(

IN p_user_id INT,
IN p_score DECIMAL(5,2)

) BEGIN

UPDATE users SET score = p_score WHERE id = p_user_id;

END

CREATE PROCEDURE sp_AnalyzeUserQuery(IN p_days INT) BEGIN

-- 先分析查询计划
EXPLAIN SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.create_time >= DATE_SUB(NOW(), INTERVAL p_days DAY)
GROUP BY u.id, u.name;

-- 然后执行实际查询
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.create_time >= DATE_SUB(NOW(), INTERVAL p_days DAY)
GROUP BY u.id, u.name;

END

CREATE PROCEDURE sp_CreateOrder(

IN p_user_id INT,
IN p_product_id INT,
IN p_quantity INT,
IN p_coupon_code VARCHAR(50),
OUT p_order_id INT,
OUT p_result_code INT,
OUT p_message VARCHAR(200)

) BEGIN

DECLARE v_available_stock INT DEFAULT 0;
DECLARE v_product_price DECIMAL(10,2) DEFAULT 0;
DECLARE v_coupon_discount DECIMAL(10,2) DEFAULT 0;
DECLARE v_final_amount DECIMAL(10,2) DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET p_result_code = -1;
    SET p_message = '系统错误，订单创建失败';
END;

START TRANSACTION;

-- 检查库存
SELECT stock, price INTO v_available_stock, v_product_price
FROM products WHERE id = p_product_id FOR UPDATE;

IF v_available_stock < p_quantity THEN
    SET p_result_code = 1001;
    SET p_message = '库存不足';
    ROLLBACK;
ELSE
    -- 计算优惠券折扣
    IF p_coupon_code IS NOT NULL THEN
        SELECT discount INTO v_coupon_discount 
        FROM coupons WHERE code = p_coupon_code AND status = 'active';
    END IF;
    
    -- 计算最终金额
    SET v_final_amount = (v_product_price * p_quantity) - v_coupon_discount;
    
    -- 创建订单
    INSERT INTO orders (user_id, product_id, quantity, unit_price, discount, total_amount, status)
    VALUES (p_user_id, p_product_id, p_quantity, v_product_price, v_coupon_discount, v_final_amount, 'pending');
    
    SET p_order_id = LAST_INSERT_ID();
    
    -- 扣减库存
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;
    
    -- 记录订单日志
    INSERT INTO order_logs (order_id, action, operator, note)
    VALUES (p_order_id, 'create', 'system', '订单创建成功');
    
    COMMIT;
    SET p_result_code = 0;
    SET p_message = '订单创建成功';
END IF;

END