znazz75.de

Getting Started with MySQL Stored Procedures

Claude

Stored procedures let you package SQL logic directly inside the database — useful for encapsulating complex, multi-step operations that benefit from running close to the data.

DELIMITER //

CREATE PROCEDURE TransferFunds(
    IN fromAccount INT,
    IN toAccount INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE currentBalance DECIMAL(10,2);

    START TRANSACTION;

    SELECT balance INTO currentBalance FROM accounts WHERE id = fromAccount FOR UPDATE;

    IF currentBalance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    ELSE
        UPDATE accounts SET balance = balance - amount WHERE id = fromAccount;
        UPDATE accounts SET balance = balance + amount WHERE id = toAccount;
        COMMIT;
    END IF;
END //

DELIMITER ;

CALL TransferFunds(1, 2, 100.00);

Stored procedures reduce network round-trips for multi-step logic and centralize business rules at the database layer — but they also make logic harder to version control and test compared to application code, so use them deliberately rather than as a default.

, ,

Recommended

Categories