在MySQL/MariaDB中创建过程并定义变量原因错误#1064

Question

我试图在mysql/mariadb中创建一个SP，里面有一个声明的变量——但我不明白，它有什么问题！？！

DROP PROCEDURE IF EXISTS UpdateReceiverDevice;
DELIMITER $$

CREATE PROCEDURE `UpdateReceiverDevice`(IN `deviceIdentifier` VARCHAR(45), IN `deviceName` VARCHAR(45), IN `deviceLocation` VARCHAR(45), IN `informations` TEXT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER
BEGIN
    DECLARE receiverDeviceId AS INT(11) DEFAULT 0
    SET receiverDeviceId = (SELECT ID FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier);
    IF (receiverDeviceId > 0) BEGIN
        UPDATE ReceiverDevice SET Informations = informations WHERE ID = receiverDeviceId;
    ELSE
        INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations);
    END IF
END $$

DELIMITER ;

MySQL返回以下错误：

• 服务器类型：MariaDB
• 服务器-版本： 10.3.11-MariaDB-1:10.3.11 maria~仿生-mariadb.org二进制分布
• ProTokoll-版本：10
• 服务器-Zeichensatz：UTF-8 Unicode（utf8）

Answer 1

评论中的提示“每个声明都需要终止”非常有用。

DROP PROCEDURE IF EXISTS UpdateReceiverDevice;
DELIMITER $$

CREATE PROCEDURE `UpdateReceiverDevice`(IN `deviceIdentifier` VARCHAR(45), IN `deviceName` VARCHAR(45), IN `deviceLocation` VARCHAR(45), IN `informations` TEXT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER 
BEGIN
    DECLARE receiverDeviceId INT(11) DEFAULT 0;
    SELECT ID INTO receiverDeviceId FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier;

    IF receiverDeviceId > 0 THEN
        UPDATE ReceiverDevice SET Informations = informations WHERE ID = receiverDeviceId;
    ELSE
        INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations);
    END IF;
END $$

DELIMITER ;