存储过程sp_buy_products调用异常：库存充足却提示缺货

调用语句：CALL sp_buy_products ('Longan', 2);
问题：Longan实际库存为67，但持续收到Sorry! Out of stock!提示

原存储过程代码 #

DELIMITER $$
CREATE PROCEDURE sp_buy_products (IN p_product_name VARCHAR(50), IN p_quantity INT)

BEGIN
    DECLARE p_product_name VARCHAR(50);
    DECLARE  p_quantity INT;
    DECLARE  v_count INT;

    SELECT count(1)
    INTO v_count
    FROM products
    WHERE p_quantity <= quantity_in_stock AND name = p_product_name;
    
    IF v_count > 0 THEN
    UPDATE products SET quantity_in_stock = (quantity_in_stock - p_quantity)
    WHERE name = p_product_name;
    SELECT 'Product sold!';
    
    ELSE 
    SELECT 'Sorry! Out of stock!';
    END IF;

END $$
DELIMITER ;

问题根源 #

存储过程内部重复声明了和入参同名的局部变量p_product_name和p_quantity。这两个局部变量未初始化，默认值为NULL，会直接覆盖传入的参数值。

执行查询时：

• name = p_product_name 等价于 name = NULL，SQL中NULL与任何值比较结果都是UNKNOWN，无法匹配到任何行
• p_quantity <= quantity_in_stock 等价于 NULL <= 67，结果同样是UNKNOWN

最终v_count始终为0，触发ELSE分支返回库存不足提示。

修复后的代码 #

删除内部重复声明的变量，直接使用传入的参数：

DELIMITER $$
CREATE PROCEDURE sp_buy_products (IN p_product_name VARCHAR(50), IN p_quantity INT)

BEGIN
    DECLARE v_count INT;

    SELECT count(1)
    INTO v_count
    FROM products
    WHERE p_quantity <= quantity_in_stock AND name = p_product_name;
    
    IF v_count > 0 THEN
        UPDATE products SET quantity_in_stock = quantity_in_stock - p_quantity
        WHERE name = p_product_name;
        SELECT 'Product sold!';
    ELSE 
        SELECT 'Sorry! Out of stock!';
    END IF;

END $$
DELIMITER ;

可选优化 #

1. 直接查询库存值判断，比count(1)更直观：

DELIMITER $$
CREATE PROCEDURE sp_buy_products (IN p_product_name VARCHAR(50), IN p_quantity INT)

BEGIN
    DECLARE v_stock INT;

    SELECT quantity_in_stock INTO v_stock
    FROM products
    WHERE name = p_product_name;
    
    IF v_stock >= p_quantity THEN
        UPDATE products SET quantity_in_stock = quantity_in_stock - p_quantity
        WHERE name = p_product_name;
        SELECT 'Product sold!';
    ELSE 
        SELECT 'Sorry! Out of stock!';
    END IF;

END $$
DELIMITER ;

2. 添加事务控制，防止并发场景下的超卖：

DELIMITER $$
CREATE PROCEDURE sp_buy_products (IN p_product_name VARCHAR(50), IN p_quantity INT)

BEGIN
    DECLARE v_stock INT;
    START TRANSACTION;

    SELECT quantity_in_stock INTO v_stock
    FROM products
    WHERE name = p_product_name FOR UPDATE; -- 加行锁
    
    IF v_stock >= p_quantity THEN
        UPDATE products SET quantity_in_stock = quantity_in_stock - p_quantity
        WHERE name = p_product_name;
        SELECT 'Product sold!';
        COMMIT;
    ELSE 
        SELECT 'Sorry! Out of stock!';
        ROLLBACK;
    END IF;

END $$
DELIMITER ;

内容的提问来源于stack exchange，提问作者Kimbo