MySQL存储过程中trip_days为NULL却能正确计算DailyCostAverage？

我编写了名为usp_update_activity_cost_get_total_trip_cost的MySQL存储过程，用于更新活动成本并计算旅行总成本与日均成本（DailyCostAverage）。通过DATEDIFF函数计算trip_days，但调用后发现trip_days返回NULL，然而用它计算出的DailyCostAverage结果却正确。尝试修改trip_days的数据类型、更换测试值均无效，在朋友的电脑上测试也出现相同问题。

原存储过程代码 #

DELIMITER //

CREATE PROCEDURE usp_update_activity_cost_get_total_trip_cost( IN activity_id_in INT,IN new_cost_in DECIMAL(10,2),OUT trip_name VARCHAR(255),OUT activity_name VARCHAR(255),OUT current_total_cost DECIMAL(10,2), OUT daily_cost_average DECIMAL(10,2), OUT trip_days INT)

BEGIN DECLARE this_trip_id INT;DECLARE trip_start_date DATE;DECLARE trip_end_date DATE;DECLARE trip_days INT ;


-- retrieve the associated trip_id, trip name, and activity name for the input activity_id
SELECT a.trip_id, t.name, a.name 
INTO this_trip_id, trip_name, activity_name
FROM activities a
JOIN trips t ON a.trip_id = t.trip_id
WHERE a.activity_id = activity_id_in
LIMIT 1;


-- if the activity id exists, update the cost of activity in the activities table
IF activity_id_in IS NOT NULL THEN
    -- update the cost of the activity
    UPDATE activities
    SET cost_in_cad = new_cost_in
    WHERE activity_id = activity_id_in;

    -- calculate the updated total cost for the trip the activity id is associated with
    SELECT SUM(cost_in_cad) 
    INTO current_total_cost
    FROM activities
    WHERE trip_id = this_trip_id 
    LIMIT 1;

    -- retrieve the trip's start and end dates to use it for calculating the number of days of the trip
    SELECT start_date, end_date 
    INTO trip_start_date, trip_end_date
    FROM trips
    WHERE trip_id = this_trip_id
    LIMIT 1;

    -- calculate the total number of days of the trip
    -- adding 1 so that if both start and end dates are on the same day, the days won't be zero
    
    SET trip_days = DATEDIFF(trip_end_date, trip_start_date) + 1;

    -- calculate the daily cost average using current total cost and number of days of the trip
    SET daily_cost_average = current_total_cost / trip_days;
 
ELSE
    -- if the activity id does not exist, set all outputs to NULL
    SET trip_name = NULL;
    SET activity_name = NULL;
    SET current_total_cost = NULL;
    SET daily_cost_average = NULL;
    SET trip_days = NULL;
END IF;

END //

DELIMITER ; 

原调用代码 #

SET @activity_id_in = 1;
SET @new_cost_in = 250.00;
SET @trip_name = NULL;
SET @activity_name = NULL;
SET @current_total_cost = NULL;
SET @daily_cost_average = NULL;
SET @trip_days = NULL;

CALL usp_update_activity_cost_get_total_trip_cost(
    @activity_id_in, @new_cost_in, 
    @trip_name, @activity_name, @current_total_cost, @daily_cost_average, @trip_days
);

SELECT 
    @trip_name AS TripName,
    @activity_name AS ActivityName,
    @current_total_cost AS TotalTripCost,
    @daily_cost_average AS DailyCostAverage,
    @trip_days AS TripDays;

问题原因 #

核心问题是存储过程内部声明了一个与OUT参数同名的局部变量trip_days：

• 内部计算时，SET trip_days = ...操作的是局部变量，这个局部变量的作用域仅在BEGIN/END块内，不会同步到外部的OUT参数。
• 日均成本计算正确是因为用的是局部变量的有效值，但存储过程执行完毕后，OUT参数trip_days从未被赋值，因此返回NULL。

解决方案 #

删除内部的DECLARE trip_days INT ;声明，直接使用OUT参数trip_days进行赋值操作。

修改后的存储过程代码 #

DELIMITER //

CREATE PROCEDURE usp_update_activity_cost_get_total_trip_cost( IN activity_id_in INT,IN new_cost_in DECIMAL(10,2),OUT trip_name VARCHAR(255),OUT activity_name VARCHAR(255),OUT current_total_cost DECIMAL(10,2), OUT daily_cost_average DECIMAL(10,2), OUT trip_days INT)

BEGIN DECLARE this_trip_id INT;DECLARE trip_start_date DATE;DECLARE trip_end_date DATE; -- 移除了DECLARE trip_days INT;


-- retrieve the associated trip_id, trip name, and activity name for the input activity_id
SELECT a.trip_id, t.name, a.name 
INTO this_trip_id, trip_name, activity_name
FROM activities a
JOIN trips t ON a.trip_id = t.trip_id
WHERE a.activity_id = activity_id_in
LIMIT 1;


-- if the activity id exists, update the cost of activity in the activities table
IF activity_id_in IS NOT NULL THEN
    -- update the cost of the activity
    UPDATE activities
    SET cost_in_cad = new_cost_in
    WHERE activity_id = activity_id_in;

    -- calculate the updated total cost for the trip the activity id is associated with
    SELECT SUM(cost_in_cad) 
    INTO current_total_cost
    FROM activities
    WHERE trip_id = this_trip_id 
    LIMIT 1;

    -- retrieve the trip's start and end dates to use it for calculating the number of days of the trip
    SELECT start_date, end_date 
    INTO trip_start_date, trip_end_date
    FROM trips
    WHERE trip_id = this_trip_id
    LIMIT 1;

    -- calculate the total number of days of the trip
    -- adding 1 so that if both start and end dates are on the same day, the days won't be zero
    
    SET trip_days = DATEDIFF(trip_end_date, trip_start_date) + 1;

    -- calculate the daily cost average using current total cost and number of days of the trip
    SET daily_cost_average = current_total_cost / trip_days;
 
ELSE
    -- if the activity id does not exist, set all outputs to NULL
    SET trip_name = NULL;
    SET activity_name = NULL;
    SET current_total_cost = NULL;
    SET daily_cost_average = NULL;
    SET trip_days = NULL;
END IF;

END //

DELIMITER ; 

验证结果 #

修改后重新调用存储过程，trip_days会正确返回计算出的旅行天数，同时日均成本DailyCostAverage依然保持正确计算结果。

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