时间戳

首页 > 解决方案 > Mysql存储过程创建与TRANSACTION问题

mysql - Mysql存储过程创建与TRANSACTION问题

问题描述

我必须创建这样一个程序，但是我得到了错误，我只是通过在其他程序中删除 BEGIN 和 END 来解决它，但是这个程序有 TRANSACTION，我找不到解决方案。


CREATE PROCEDURE sp_device_post_device_status (IN deviceID int(11), IN zone int(11), IN stat int(11)) 
BEGIN 
    START TRANSACTION
    UPDATE device_statuses
    SET is_last_one = 0
    WHERE id_device = deviceID
      AND is_last_one = 1
      AND zoneNo = zone;
   INSERT INTO device_statuses (id_device, zoneNo, status, is_last_one)
    VALUES (deviceID, zone, stat, 1);
    commit
END;

它返回：

SQL query: Copy


CREATE PROCEDURE sp_device_post_device_status (IN deviceID int(11), IN zone int(11), IN stat int(11)) 
BEGIN 
    START TRANSACTION
    UPDATE device_statuses
    SET is_last_one = 0
    WHERE id_device = deviceID
      AND is_last_one = 1
      AND zoneNo = zone
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE device_statuses
    SET is_last_one = 0
    WHERE id_device = deviceID
' at line 4

标签： mysqllaravelstored-procedures

解决方案

我以这种方式解决了

DELIMITER $$ CREATE PROCEDURE sp_device_post_device_status(IN deviceID int(11), IN zone int(11), IN stat int(11)) BEGIN START TRANSACTION;

UPDATE device_statuses
SET is_last_one = 0
WHERE id_device = deviceID
  AND is_last_one = 1
  and zoneNo = zone;

insert into device_statuses (id_device, zoneNo, status, is_last_one)
VALUES (deviceID, zone, stat, 1);
commit;

结束$$ DELIMITER ;

推荐阅读