mysql - SQL 存储过程语法错误缺失 'end'

Question

我一直在研究 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’”。我不完全确定这是什么意思，而且我在解决问题时遇到了问题。

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

Answer 1

在帮助下，我能够使用以下代码解决问题(至少就编译而言):

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

这个问题似乎是两件事的结合:

1. 我对 MySQL Workbench 的存储过程使用了错误的 IF ELSE 语法
2. 我需要在过程的顶部声明我的变量

感谢支持!