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

Question

我终于使此功能正常工作，直到我尝试以生产状态实施为止。当我运行此过程并致电时，我只会获得下限输出的值，而上限输出的零。我在过程中添加了一个选择，该过程显示了正在计算内部变量@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 说：

每个参数默认情况下是一个参数。要其他参数指定，请在参数名称之前使用关键字或输入。