/*
tpcb
v.1.0.0

Stored Procedure MySQL per la transazione TPC-B

mail@meo.bogliolo.name
*/

use bench
DROP PROCEDURE IF EXISTS tpcb;
DELIMITER '/';

CREATE PROCEDURE tpcb(Aidv INTEGER, Bidv INTEGER, Tidv INTEGER, deltav FLOAT, Abalancev OUT FLOAT) 
LANGUAGE SQL NOT DETERMINISTIC  SQL SECURITY DEFINER COMMENT 'TPC-B Standard benchmarking procedure' 
BEGIN 
UPDATE accounts
 SET Abalance = Abalance + deltav
 WHERE Aid = Aidv;
SELECT Abalance 
 INTO Abalancev
 FROM accounts
 WHERE Aid = Aidv;
UPDATE tellers
 SET Tbalance = Tbalance + deltav 
 WHERE Tid = Tidv;
UPDATE branches
 SET Bbalance = Bbalance + deltav 
 WHERE Bid = Bidv ;

-- A timestamp can also be used (better in MySQL)
INSERT INTO history (Tid,Bid,Aid,delta,Xtime) VALUES (Tidv, Bidv, Aidv, deltav, SYSDATE);
COMMIT ;
END/
DELIMITER ';'/