i m practicing with this stored procedure on a MYSQL database to make payments between accounts. it takes inputs of two accounts(acct1, acct2), an amount(amt) to be paid and outputs a confirmation message(pmessage)
它只想在以下情况下付款:(IF (balance1 >=amt)theN......),但是,如果一投入超出现有余额的任何数额,它仍要付款。 i 不理解。 插手帮助。
这部法典:
DELIMITER //
CREATE PROCEDURE `make_payment`(IN `acct1` int(4), IN `acct2` int(4), IN `amt` float(10,2) unsigned, OUT `pmessage` varchar(100))
BEGIN
DECLARE balance1 FLOAT;
DECLARE balance2 FLOAT;
IF(acct1 !=acct2) THEN
SELECT balance INTO balance1 FROM mydb.accounts
WHERE account_no =acct1
ORDER BY balance DESC LIMIT 1;
SELECT balance INTO balance2 FROM mydb.accounts
WHERE account_no =acct2
ORDER BY balance DESC LIMIT 1;
IF (balance1 >=amt) THEN
begin
SET balance1 =balance1-amt;
SET balance2 =balance2+amt;
INSERT INTO mydb.accounts(account_no, outflow, balance)
VALUES(acct1, amt, balance1);
INSERT INTO mydb.accounts(account_no, inflow, balance)
VALUES(acct2, amt, balance2);
SET pmessage ="payment was successful";
END;
ELSE SET pmessage ="insufficient balance";
END IF;
END IF;
END //
DELIMITER ;