Prev Next

MySQL Stored Procedures and Functions

Stored Procedures and User-Defined Functions (UDFs) in MySQL are used to encapsulate complex queries or logic that can be reused. Stored procedures can perform actions, while functions return a value.

Stored Procedures

Example: Creating a Stored Procedure

DELIMITER //
CREATE PROCEDURE TransferAmount(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    COMMIT;
END //
DELIMITER ;

Code Explanation: This procedure transfers a specified amount from one account to another. The DELIMITER command is used to change the statement delimiter temporarily.

User-Defined Functions

Example: Creating a Function

DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price - (price * discount_rate / 100);
END //
DELIMITER ;

Code Explanation: This function calculates the discount on a price given a discount rate. The DETERMINISTIC keyword indicates that the function always produces the same output for the same input.

Best Practices

  • Use stored procedures to simplify complex operations and ensure data consistency.
  • Use functions to perform calculations that can be reused in queries.

Key Takeaways

  • Stored procedures and functions help encapsulate and reuse SQL logic.
  • Procedures can modify data, while functions return values.
Prev Next