如何在 MySQL 存储过程中使用事务? [英] How can I use transactions in my MySQL stored procedure?

问题描述

我正在尝试修改我的 MySQL 存储过程并使其成为事务性的.现有的存储过程工作正常，没有问题，但是一旦我将其设置为事务性，它甚至不允许我保存更改.我检查了 MySQL 文档并在线搜索，但我找不到我的代码有任何问题.这似乎很直接，但无法弄清楚.

I'm trying to modify my MySQL stored procedure and make it transactional. The existing stored procedure works fine with no problem but as soon as I make it transactional it does not even allow me to save my changes. I checked MySQL documentation and searched online but I cannot find any problem with my code. It seems to be pretty straight forward but can't figure it out.

BEGIN

DECLARE poid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING
BEGIN
    ROLLBACK;
END

START TRANSACTION;

    -- ADD option 5
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0);
    SET poid = (SELECT LAST_INSERT_ID());
    INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+');

    -- ADD option 12
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1);

    -- ADD option 13
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0);

COMMIT;

END

有什么想法吗?

推荐答案

两个语法错误:

• 退出处理程序的条件之间需要逗号.请注意语法文档显示逗号.

您需要用分号终止退出处理程序的 END.DECLARE 语句本身(包括它的 BEGIN...END 块)是一个和其他语句一样的语句，需要有一个终止符.

You need to terminate the END of the exit handler with a semicolon. The DECLARE statement itself (including its BEGIN...END block) is a statement like any other, and need to have a terminator.

所以你需要这个:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
    ROLLBACK;
END;

这篇关于如何在 MySQL 存储过程中使用事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！