MySQL存储过程执行无报错但未插入数据求助

这是教授布置的作业，尽管存在更优的数据表填充方式，但要求按指定方式实现。现有一段名为fillSaleLogs的MySQL存储过程，执行后显示0行受影响，未向目标表插入数据，以下是问题分析及修正点：

存储过程原代码 #

DROP PROCEDURE IF EXISTS fillSaleLogs;
DELIMITER ^^
CREATE PROCEDURE fillSaleLogs(IN amountOfDays INT, IN operationtype INT, IN maxSalesPerDay INT)
BEGIN
    DECLARE nextSunday INT;
    DECLARE recordTime TIMESTAMP;
    DECLARE maxSalesPerDay INT;
    DECLARE salesPerDay INT;
    DECLARE chosenProduct INT;
    DECLARE chosenCart INT;
    DECLARE chosenCashier INT;
    DECLARE chosenPaymentMethod INT;
    DECLARE chosenBeach INT;
    DECLARE chosenComission INT;
    DECLARE price INT;
    DECLARE cashUsed INT;
    DECLARE quantity INT;
    DECLARE soldQuantity int;
    DECLARE temp int;

    WHILE amountOfDays > 0 DO
        SELECT DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 6) MONTH) + INTERVAL FLOOR(RAND() * 86400) SECOND AS random_timestamp INTO recordTime;
        SET salesPerDay = RAND() * maxSalesPerDay;
        SELECT idCopero FROM coperos ORDER BY RAND() LIMIT 1 INTO chosenCashier;
        SELECT idCarrito FROM carritos ORDER BY RAND() LIMIT 1 INTO chosenCart;
        SELECT TipoPago FROM Ventas ORDER BY RAND() LIMIT 1 INTO chosenPaymentMethod;
        SELECT idPlaya FROM playas ORDER BY RAND() LIMIT 1 INTO chosenBeach;
        SELECT idComision FROM comisiones ORDER BY RAND() LIMIT 1 INTO chosenComission;
        SET cashUsed = price + RAND()*5000;
        
        
        WHILE salesPerDay > 0 DO
            SELECT idProducto FROM productos ORDER BY RAND() LIMIT 1 INTO chosenProduct;
            SELECT idPrecio FROM precios WHERE idProducto = chosenProduct.idProducto INTO price;
            INSERT INTO ventas (tipoPago, monto, vuelto, fecha, idCopero, idCarrito, idPlaya, idProducto, idComision) VALUES
            (chosenPaymentMethod, cashUsed, cashUsed - price, recordTime, chosenCashier, chosenCart, chosenBeach, chosenProduct, chosenComission);
            -- TODO: Actualizar cantidades de ingredientes
            SELECT COUNT(idIngrediente) as quantity from ingredientesxproducto WHERE idProducto = chosenProduct INTO quantity;
            WHILE quantity > 0 DO
                INSERT INTO inventoryCheck (posttime, idFirstCheck, idCheckTypes) VALUES
                (recordTime, chosenCashier, 3);
                
                SELECT cantidadIngrediente FROM ingredientesxproducto WHERE idProducto = chosenProduct INTO temp;
                SET soldQuantity = selledQuantity - temp;
                
                INSERT INTO inventoryLog (posttime, idIngrediente, idProducto, idInventoryCheck, cantidadIngrediente) VALUES
                (recordTime, quantity, chosenProduct, IDENT_CURRENT('inventoryCheck'), cantidadIngrediente);
            END WHILE;
            -- Actualizar dinero en la caja
            SET salesPerDay = salesPerDay - 1;
        END WHILE;
        SET amountOfDays = amountOfDays - 1;
    END WHILE;
END^^
DELIMITER ;

CALL fillSaleLogs(3, 2, 5);

核心问题及修正方案 #

• 变量重定义覆盖参数：存储过程参数已定义maxSalesPerDay，但DECLARE部分又重复定义同名变量，导致传入的参数值被覆盖为NULL，salesPerDay计算后也为NULL，内层WHILE循环无法执行。修正：删除DECLARE中的DECLARE maxSalesPerDay INT;。
• price未初始化就使用：SET cashUsed = price + RAND()*5000;执行时price还未赋值（赋值在第二个WHILE循环内），cashUsed变为NULL，违反ventas表非空约束导致插入失败。修正：将price的赋值逻辑移到cashUsed赋值之前，或者确保在使用price前完成赋值。
• 变量引用错误：chosenProduct是INT类型变量，SELECT idPrecio FROM precios WHERE idProducto = chosenProduct.idProducto INTO price;中错误使用chosenProduct.idProducto，应改为chosenProduct。
• 内层循环未终止：WHILE quantity > 0 DO循环内没有递减quantity的逻辑，会导致无限循环，存储过程无法正常结束。修正：在循环内添加SET quantity = quantity - 1;。
• 未定义变量：SET soldQuantity = selledQuantity - temp;中的selledQuantity未DECLARE，会触发错误终止存储过程。修正：提前DECLARE该变量，或根据业务逻辑初始化它。
• inventoryLog插入字段错误：插入时用quantity作为idIngrediente的值错误，且cantidadIngrediente未定义，应改为获取对应idIngrediente并使用temp变量。修正：改为查询idIngrediente和cantidadIngrediente，比如SELECT idIngrediente, cantidadIngrediente FROM ingredientesxproducto WHERE idProducto = chosenProduct LIMIT 1 INTO chosenIngrediente, temp;，再插入对应值。
• 主键获取方式错误：IDENT_CURRENT('inventoryCheck')可能获取其他会话的插入ID，应使用990900获取当前会话刚插入的inventoryCheck主键。
• 空结果集未处理：如果依赖的基础表（如coperos、productos等）无数据，SELECT ... INTO会抛出错误，导致存储过程终止。修正：添加错误处理逻辑，或确保基础表有数据。

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