如何在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
有什么主意吗?
推荐答案
尝试这样,即在START TRANSACTION;
中包含您的Declare
语句.先前您的ROLLBACK
不是TRANSACTION
的一部分,因为它是在START TRANSACTION
上方编写的:-
Try like this ie, include your Declare
statement inside the START TRANSACTION;
. Earlier your ROLLBACK
was not a part of TRANSACTION
as you wrote it above the 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
这篇关于如何在MySQL存储过程中使用事务?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!