mysql - 在 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 is returning this error:

Ein oder mehrere Fehler sind aufgetreten während Ihre Anfrage verarbeitet wurde: Die folgende Abfrage ist fehlgeschlagen: "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 DECLARE receiverDeviceId AS INT(11) SET receiverDeviceId = (SELECT ID FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier) IF receiverDeviceId > 0 UPDATE ReceiverDevice SET Informations = informations WHERE ID = @receiverDeviceId ELSE INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations) END IF"

MySQL meldet: #1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'DECLARE receiverDeviceId AS INT(11) SET receiverDeviceId = (SELECT ID FROM Re' in Zeile 1

这是我的服务器:

• 服务器类型:MariaDB
• 服务器版本:10.3.11-MariaDB-1:10.3.11+maria~bionic - 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 ;