我的SQL事务将1000从一个账户转移到另一个账户(名称,余额)?
我在下面写了查询,我不确定如何检查条件。
START TRANSACTION
UPDATE account SET balance = balance - 100 WHERE name = 'D'
UPDATE account SET balance = balance + 100 WHERE name = 'A'
COMMIT
转账前如何检查是否有足够的金额?
使用存储过程定义事务:
DELIMITER $$
DROP PROCEDURE IF EXISTS sample_balance_transfer $$
CREATE PROCEDURE sample_balance_transfer ()
BEGIN
DECLARE balance_var INT DEFAULT 0;
SELECT IFNULL(balance,0)
INTO balance_var
FROM account
WHERE name = 'D';
IF balance_var >= 1000 THEN
START TRANSACTION;
UPDATE account
SET balance = balance - 1000
WHERE name = 'D';
UPDATE account
SET balance = balance + 1000
WHERE name = 'A';
COMMIT;
END IF;
END $$
DELIMITER ;
然后执行存储的过程:
CALL sample_balance_transfer();
注意 - 可能希望为错误添加退出处理程序