Stored Procedures

Code

DELIMITER //
CREATE PROCEDURE transfer_funds(
  IN from_id INT,
  IN to_id   INT,
  IN amount  DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END //
DELIMITER ;

-- Call and inspect
CALL transfer_funds(1, 2, 50.00);
SHOW CREATE PROCEDURE transfer_funds;
DROP PROCEDURE transfer_funds;

Explanation

Stored procedures encapsulate SQL logic on the server, reducing round-trips and centralizing business rules. Parameters can be IN, OUT, or INOUT, and DECLARE HANDLER manages errors. Delimiters are changed while defining the body so semicolons inside the procedure are not treated as statement terminators.

More MySQL Snippets