Aulas práticas - Ficha 6 - Soluções

CREATE PROCEDURE transfer_sol(IN id1 INT, IN id2 INT, IN amount INT, OUT done BOOLEAN)
BEGIN
  DECLARE v1 INT;
  DECLARE v2 INT;

  START TRANSACTION;

  SET v1 = NULL;
  SET v2 = NULL;
  
  SELECT Value INTO v1 
  FROM ACCOUNT 
  WHERE AccountId = id1;
  
  SELECT Value INTO v2 
  FROM ACCOUNT 
  WHERE AccountId = id2;
 
  IF v1 IS NULL OR V2 IS NULL OR v1 < amount OR id1 = id2 THEN
    ROLLBACK;
    SET done = FALSE;
  ELSE
    UPDATE ACCOUNT 
    SET Value = Value - amount 
    WHERE AccountId = id1;
    
    UPDATE ACCOUNT 
    SET Value = Value + amount 
    WHERE AccountId = id2;
    
    COMMIT;
    SET done = TRUE;
  END IF;
END $