MySQL存储过程无输出，单独执行对应SQL语句有输出求助

核心原因 #

你的存储过程出现OUT参数无输出的问题，核心是以下错误导致：

• 变量赋值方式错误：MySQL中给变量赋值不能用SELECT 变量=值的写法，这种写法实际是做比较运算，不会把查询结果赋值给变量，正确写法是SELECT 查询语句 INTO 变量。
• OUT参数未被正确赋值：比如referalcount参数，你只执行了统计查询，但没有把结果赋值给这个OUT参数；starscount同样用了错误的赋值写法。
• 局部变量与OUT参数重名：你声明了局部变量WALLETBALANCE，和OUT参数WalletBalance同名，会导致赋值混乱。
• 空值未处理：当sum()没有匹配数据时会返回NULL，直接参与运算会导致最终结果为NULL，需要用IFNULL处理空值。

修正后的存储过程代码 #

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ShopBusinessFunctions`(
    IN `shopid` VARCHAR(15), 
    OUT `referalcount` INT, 
    OUT `starscount` INT, 
    OUT `WalletBalance` DECIMAL(10,2), 
    OUT `TodaysBusiness` DECIMAL(10,2), 
    OUT `CurMonthBusiness` DECIMAL(10,2), 
    OUT `CurYearBusiness` DECIMAL(10,2)
)
BEGIN
    DECLARE walletamount DECIMAL(10,2) DEFAULT 0.00;
    DECLARE toozocommission DECIMAL(10,2) DEFAULT 0.00;

    -- 赋值钱包金额，用IFNULL处理空值
    SELECT IFNULL(sum(Amount), 0.00) INTO walletamount 
    FROM shopswallet 
    WHERE shopid = shopid AND isapproved = 1;

    -- 赋值平台佣金，处理空值
    SELECT IFNULL(sum(Amount), 0.00) INTO toozocommission 
    FROM shopspurchasewallet 
    WHERE shopid = shopid;

    -- 计算钱包余额
    SET WalletBalance = walletamount - toozocommission;

    -- 今日营业额
    SELECT IFNULL(sum(Amount), 0.00) INTO TodaysBusiness 
    FROM shopspurchasewallet   
    WHERE DATE(date_entered) = CURDATE() AND shopid = shopid;

    -- 当月营业额，用日期函数替代DATE_FORMAT，避免索引失效
    SELECT IFNULL(sum(Amount), 0.00) INTO CurMonthBusiness 
    FROM shopspurchasewallet   
    WHERE YEAR(date_entered) = YEAR(CURDATE()) AND MONTH(date_entered) = MONTH(CURDATE()) AND shopid = shopid;

    -- 当年营业额
    SELECT IFNULL(sum(Amount), 0.00) INTO CurYearBusiness 
    FROM shopspurchasewallet   
    WHERE YEAR(date_entered) = YEAR(CURDATE()) AND shopid = shopid;

    -- 推荐人数赋值给OUT参数
    SELECT IFNULL(count(ReferalID), 0) INTO referalcount 
    FROM shops 
    WHERE ReferalID = shopid;

    -- 评分数量赋值给OUT参数
    SELECT IFNULL(count(starratings), 0) INTO starscount 
    FROM customerpurchaseentry 
    WHERE shopid = shopid;

END$$
DELIMITER ;

执行说明 #

调用存储过程时，需要声明变量接收OUT参数的值，示例：

-- 声明变量
SET @shop_id = '你的店铺ID';
SET @ref_count = 0;
SET @star_count = 0;
SET @wallet_bal = 0.00;
SET @today_biz = 0.00;
SET @month_biz = 0.00;
SET @year_biz = 0.00;

-- 调用存储过程
CALL ShopBusinessFunctions(@shop_id, @ref_count, @star_count, @wallet_bal, @today_biz, @month_biz, @year_biz);

-- 查看结果
SELECT @ref_count AS referalcount, @star_count AS starscount, @wallet_bal AS WalletBalance, 
       @today_biz AS TodaysBusiness, @month_biz AS CurMonthBusiness, @year_biz AS CurYearBusiness;

额外优化点 #

• 移除了原代码中未使用的冗余局部变量（v_done、v_idRoom、v_maxAvailable）。
• 用DATE(date_entered) = CURDATE()替代DATE_FORMAT比较日期，避免索引失效，提升查询性能。
• 给局部变量设置默认值0.00，进一步避免空值传递问题。

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