如何在 MySQL 存储过程中使用事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18817148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use transactions in my MySQL stored procedure?
提问by Tohid
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.
我正在尝试修改我的 MySQL 存储过程并使其成为事务性的。现有的存储过程工作正常，没有问题，但是一旦我将其设置为事务性，它甚至不允许我保存更改。我检查了 MySQL 文档并在线搜索，但我找不到我的代码有任何问题。这似乎很直接，但无法弄清楚。
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
any idea ?
任何的想法 ？
回答by Rahul Tripathi
Try like this ie, include your Declarestatement inside the START TRANSACTION;. Earlier your ROLLBACKwas not a part of TRANSACTIONas you wrote it above the START TRANSACTION:-
像这样尝试，即，将您的Declare声明包含在START TRANSACTION;. 早些时候你ROLLBACK不是TRANSACTION你在上面写的一部分START TRANSACTION：-
BEGIN
DECLARE poid INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
START TRANSACTION;
BEGIN
ROLLBACK;
END
-- 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
回答by Bill Karwin
Two syntax errors:
两个语法错误：
You need commas in between the conditions for your exit handler. Notice the syntax documentationshows commas.
You need to terminate the
ENDof the exit handler with a semicolon. TheDECLAREstatement itself (including its BEGIN...END block) is a statement like any other, and need to have a terminator.
您的退出处理程序的条件之间需要逗号。请注意语法文档显示逗号。
您需要
END用分号终止退出处理程序的 。该DECLARE语句本身（包括其BEGIN ... END块）是一个像任何其他语句，需要有一个终止符。
So you need this:
所以你需要这个：
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
END;
回答by Henrik Erlandsson
Put your DECLAREs after the first BEGINand it should work.
把你的DECLAREs放在第一个之后BEGIN，它应该可以工作。
If you use BEGIN and END to group multiple statements, you normally also need to declare an alternate DELIMITER at the top and replace the ; after the last END with it.
如果使用 BEGIN 和 END 对多个语句进行分组，通常还需要在顶部声明一个备用 DELIMITER 并替换 ; 最后用它END后。
