SQL存储过程语法错误缺少“结束”

Christofer Klassen 发表于 Dev

Christofer课程

我一直在研究SQL存储过程，并且对该过程非常陌生。我正在尝试编写一个从一个表中检索值并将其作为新行的一部分插入到另一个表中的过程。

我正在使用MySQL Workbench，这给了我一个错误

WHERE blockId = blk;

作为以下代码的一部分：

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_userblock`(IN user INT(11), IN blk INT(11))
BEGIN
    -- Determine how many blocks you can add
    DECLARE blockNum INT DEFAULT -1;
    SELECT addCount INTO blockNum
        FROM block
        WHERE blockId = blk;

    -- Determine if the block already exists for the user
    DECLARE entryExists INT DEFAULT 0;
    SELECT COUNT(*) INTO entryExists
        FROM userblock
        WHERE blockId = blk AND userId = user;

    IF (entryExists = 0)
        -- This is a new entry
        BEGIN
        INSERT INTO userblock (userId, blockId, num) VALUES (user, blk, blockNum);
        SELECT LAST_INSERT_ID() as 'id';
        END
    ELSE
        -- This is an existing entry
        BEGIN
        UPDATE userblock
            SET num = (num + blockNum)
            WHERE userId = user AND blockId = blk;
        END
END

错误为“语法错误：缺少'end'”。我不完全清楚这意味着什么，而且无法解决问题。

谢谢，感谢您的宝贵时间！

Christofer课程

在帮助下，我能够使用以下代码解决此问题（至少在编译方面）：

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_userblock`(IN user INT(11), IN blk INT(11))
BEGIN
    DECLARE blockNum INT DEFAULT -1;
    DECLARE entryExists INT DEFAULT 0;

    -- Determine how many blocks you can add
    SELECT addCount INTO blockNum
        FROM block
        WHERE blockId = blk;

    -- Determine if the block already exists for the user
    SELECT COUNT(*) INTO entryExists
        FROM userblock
        WHERE blockId = blk AND userId = user;

    IF entryExists = 0
    THEN
        -- This is a new entry
        INSERT INTO userblock (userId, blockId, num) VALUES (user, blk, blockNum);
        SELECT LAST_INSERT_ID() as 'id';
    ELSE
        -- This is an existing entry
        UPDATE userblock
            SET num = (num + blockNum)
            WHERE userId = user AND blockId = blk;
        SELECT LAST_INSERT_ID() as 'id';
    END IF;
END