创建过程mysql CRUD时出错 [英] Error while creating a procedure mysql CRUD

问题描述

我在第9行不断收到语法错误

I keep getting a syntax error at line 9

CREATE PROCEDURE `ItemsAddOrEdit`(
_itm_id INT,
_itm_name VARCHAR(255),
_itm_price FLOAT(8,2)
)
BEGIN
    IF _itm_id = 0 THEN
    INSERT INTO items (itm_name, itm_price)
    VALUES (_itm_name, _itm_price);
ELSE
    UPDATE items
    SET
        itm_name = _itm_name,
        itm_price = _itm_price
    WHERE itm_id = _itm_id;
END IF;
END

变量是问题吗?我检查了一下桌子，看看是否把名字弄乱了，但对我来说似乎一切都很好. 这是表格代码

Are the variables the problem? I've checked the table to see if I messed the names up, but it all seems fine to me. Here's the table code

CREATE TABLE `items` (
`itm_id` INT(255) NOT NULL AUTO_INCREMENT,
`itm_name` VARCHAR(255) NOT NULL,
`itm_price` FLOAT(8,2) NOT NULL,
PRIMARY KEY (`itm_id`),
UNIQUE INDEX `itm_name` (`itm_name`)
)

推荐答案

• 您需要将Delimiter重新定义为其他名称，例如:$$.这样，解析器就可以忽略;(因此在到达;时不执行语句).
• 此外，作为一种好的做法，请始终使用DROP PROCEDURE IF EXISTS，以避免在已经存在相同名称的过程的情况下失败.
• 最后，将Delimiter重新定义回;
• You need to redefine Delimiter to something else, for eg: $$. This allows parser to ignore ; (hence do not execute statement on reaching ;).
• Also, as a good practice, always use DROP PROCEDURE IF EXISTS, to avoid failing out in case procedure with same name already exists.
• At the end, redefine the Delimiter back to ;

尝试以下操作:

DELIMITER $$

DROP PROCEDURE IF EXISTS `ItemsAddOrEdit` $$

CREATE PROCEDURE `ItemsAddOrEdit`(
_itm_id INT,
_itm_name VARCHAR(255),
_itm_price FLOAT(8,2)
)
BEGIN
    IF _itm_id = 0 THEN
    INSERT INTO items (itm_name, itm_price)
    VALUES (_itm_name, _itm_price);
ELSE
    UPDATE items
    SET
        itm_name = _itm_name,
        itm_price = _itm_price
    WHERE itm_id = _itm_id;
END IF;
END $$

DELIMITER ;

这篇关于创建过程mysql CRUD时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！