在MySQL过程中似乎忽略了SET命令

Question

我最终让这个函数正常工作，直到我尝试在生产状态下实现为止。当我运行这个过程和调用时，我只得到LowerLimit输出的值和UpperLimit输出的NULL。我在过程中添加了一个SELECT，它显示正在计算的内部变量@lowlim和@upplim的结果，但似乎只有LowerLimit变量的集合有效。我已经忙了好几个小时了，到目前为止还不知道。有人能看到明显的我在这里错过了什么吗？以下是问题发生的地方：

SET LowerLimit = @lowlim;
SET UpperLimit = @upplim;   
SELECT @lowlim, @upplim;

这是完整的程序。

DELIMITER $$
DROP PROCEDURE if exists p_GetOutlierLimits;
CREATE DEFINER=`user`@`%` PROCEDURE p_GetOutlierLimits(
    IN KPI VARCHAR(255), TableName VARCHAR(100),
    OUT LowerLimit decimal(16,6), UpperLimit decimal(16,6)
    )
BEGIN
    SET @lowlim = 0;
    SET @upplim = 0;
    SET @SQLExec = CONCAT("
    with orderedList AS (
    SELECT
        ",KPI,",
        ROW_NUMBER() OVER (ORDER BY ",KPI,") AS row_n
    FROM ",TableName,"
    ),
    
    quartile_breaks AS (
    SELECT
        ",KPI,",
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.75)
        ) AS q_three_lower,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.75) + 1
        ) AS q_three_upper,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.25)
        ) AS q_one_lower,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.25) + 1
        ) AS q_one_upper
        FROM orderedList
        ),
    
    iqr AS (
    SELECT
        ",KPI,",
        (
        (SELECT MAX(q_three_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_three_upper)
            FROM quartile_breaks)
        )/2 AS q_three,
        (
        (SELECT MAX(q_one_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_one_upper)
            FROM quartile_breaks)
        )/2 AS q_one,
        1.5 * ((
        (SELECT MAX(q_three_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_three_upper)
            FROM quartile_breaks)
        )/2 - (
        (SELECT MAX(q_one_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_one_upper)
            FROM quartile_breaks)
        )/2) AS outlier_range
    FROM quartile_breaks
    )
    
    SELECT MAX(q_one) OVER () - MAX(outlier_range) OVER () AS lower_limit,
        MAX(q_three) OVER () + MAX(outlier_range) OVER () AS upper_limit
    INTO @lowlim, @upplim
    FROM iqr
    LIMIT 1;");
    PREPARE stmt FROM @SQLExec;
    EXECUTE stmt;

    SET LowerLimit = @lowlim;
    SET UpperLimit = @upplim;   
    SELECT @lowlim, @upplim;
END$$
DELIMITER ;

CALL p_GetOutlierLimits('576_VMC_Sol_Savings_Pct','vmctco',@LowerLimit, @UpperLimit);
SELECT @LowerLimit, @UpperLimit;

Answer 1

您需要为每个参数声明OUT：

CREATE DEFINER=`user`@`%` PROCEDURE p_GetOutlierLimits(
IN KPI VARCHAR(255), TableName VARCHAR(100),
OUT LowerLimit decimal(16,6), OUT UpperLimit decimal(16,6)
)

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html说：

默认情况下，每个参数都是IN参数。若要为参数指定其他内容，请在参数名称.之前使用关键字OUT或INOUT。