带有可为空参数的 MYSQL 存储过程，如果为空则设置该参数

Question

嗨，我正在尝试在存储过程中添加一个“可选”参数，我知道这在 MYSQL 中是不允许的，但我计划将其传递为 null，然后检查并设置它(如果为 null)。不过我认为我的语法已经过时了，我怀疑我的分隔符有问题，但我不明白为什么它是错误的。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `addSensorReading`(
      _temp DECIMAL(5,2), 
      _mois DECIMAL(5,2), 
      sourceName varchar(15), 
      _ambTemp DECIMAL(5,2), 
      _ambMois DECIMAL(5,2)
      )

IF (_ambTemp IS NULL) THEN
    SET _ambTemp := (   SELECT      r.ambTempValue
                        FROM        Readings r
                        WHERE       r.ambTempValue IS NOT NULL
                        ORDER BY    r.creation_time DESC
                        LIMIT       1);
END IF;

IF (_ambMois IS NULL) THEN
    SET _ambMois := (   SELECT      r.ambMoisValue
                        FROM        Readings r
                        WHERE       r.ambMoisValue IS NOT NULL
                        ORDER BY    r.creation_time DESC
                        LIMIT       1);
END IF;


INSERT INTO reading (moisValue, tempValue, sourceName, ambTempValue, ambMoisValue)
    VALUES (_mois, _temp, sourceName, _ambTemp, _ambMois);;


DELIMITER ;

Barmar给出了答案:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `addSensorReading`(
      _temp DECIMAL(5,2), 
      _mois DECIMAL(5,2), 
      sourceName varchar(15), 
      _ambTemp DECIMAL(5,2), 
      _ambMois DECIMAL(5,2)
      )
BEGIN
/*we only get ambient readings from the api every 2 hours (that's how often it updates), so for ease of UI and readability we assume it has not changed as there is no more recent reading. So we get the latest reading with an ambient value if we don't have one provided. */
INSERT INTO Reading (moisValue, tempValue, sourceName, ambTempValue, ambMoisValue)
SELECT _mois, _temp, sourceName, IFNULL(_ambTemp, r1.ambTempValue), IFNULL(_ambMois, r2.ambMoisValue)
FROM (SELECT ambTempValue
        FROM Reading
        WHERE ambTempValue IS NOT NULL
        ORDER BY creation_time DESC
        LIMIT 1) AS r1
CROSS JOIN
     (SELECT ambMoisValue
        FROM Reading
        WHERE ambTempValue IS NOT NULL
        ORDER BY creation_time DESC
        LIMIT 1) AS r2

END;

DELIMITER ;

这会导致错误:“查询为空”

提前道歉，我对 SQL Server 更加熟悉，它通常似乎更擅长局部变量和 spoc。谢谢

Answer 1

BEGIN 和 END 在 Transact-SQL 中是可选的，但在其他版本的 SQL 中则不是。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `addSensorReading`(
      _temp DECIMAL(5,2), 
      _mois DECIMAL(5,2), 
      sourceName varchar(15), 
      _ambTemp DECIMAL(5,2), 
      _ambMois DECIMAL(5,2)
      )
BEGIN    
IF (_ambTemp IS NULL) THEN
    SET _ambTemp := (   SELECT      r.ambTempValue
                        FROM        Readings r
                        WHERE       r.ambTempValue IS NOT NULL
                        ORDER BY    r.creation_time DESC
                        LIMIT       1);
END IF;

IF (_ambMois IS NULL) THEN
    SET _ambMois := (   SELECT      r.ambMoisValue
                        FROM        Readings r
                        WHERE       r.ambMoisValue IS NOT NULL
                        ORDER BY    r.creation_time DESC
                        LIMIT       1);
END IF;


INSERT INTO reading (moisValue, tempValue, sourceName, ambTempValue, ambMoisValue)
    VALUES (_mois, _temp, sourceName, _ambTemp, _ambMois);
END;

DELIMITER ;