MySQL:如果条件为真，则启动Transaction

Question

我想在事务中执行一些运行时生成的SQL命令，没有问题，但如果条件为真，我应该启动此事务，例如:

  SQLText := 'IF (SELECT COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString + ' > 0) BEGIN '
           + 'SET autocommit = 0;'
           + 'START TRANSACTION;'
           + 'INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)'
           + ' VALUES (' + QuotedStr(User.UName) + ', ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString
           + ', ' + QuotedStr(MTodayString) + ', ' + QuotedStr(HTodayString) + ', 2);'
           + 'UPDATE desk_table SET Status = 2 WHERE Number = ' 
           + IndDeskGrid.DataSource.DataSet.FieldByName('Number').AsString + ';'
           + 'COMMIT;'
           + 'SET autocommit = 1;'
           + 'END;';

生成SQL:

IF (SELECT COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = 202 > 0)
BEGIN
SET autocommit = 0;
START TRANSACTION;
INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)
VALUES ('UserName', 202, '2015/09/25', '2015/09/25', 2);
UPDATE desk_table SET Status = 2 WHERE Number = 202;
COMMIT;
SET autocommit = 1;
END;

但是当我像上面的代码一样使用IF时，我得到了语法错误

I have try IF…THEN…我得到了相同的错误

如何在不使用存储过程和参数的情况下做到这一点?

我正在使用UniDAC和Delphi XE6和MySQL(InnoDB)

谢谢…

Answer 1

实际上你必须把end if;放在末尾

Answer 2

使用局部变量存储计数查询的结果，然后在if条件中使用该变量。

declare @total int
SELECT @total = COUNT(ID) FROM desk_table WHERE Status = 1 AND Number = 202;
IF (@total > 0)
BEGIN
SET autocommit = 0;
START TRANSACTION;
INSERT INTO waiting_table (UName, DNumber, MDate, HDate, HaveReq)
VALUES ('UserName', 202, '2015/09/25', '2015/09/25', 2);
UPDATE desk_table SET Status = 2 WHERE Number = 202;
COMMIT;
SET autocommit = 1;
END;