MySQL Database Management

Chapter 8: Stored Procedures and Functions

8.1 Stored Procedures

Stored procedures are precompiled SQL statements stored in the database.

DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

-- Call procedure
CALL GetUserOrders(1);

8.2 User-Defined Functions

Functions return a single value and can be used in SQL statements.

DELIMITER //
CREATE FUNCTION CalculateTotal(price DECIMAL(10,2), quantity INT)
RETURNS DECIMAL(10,2)
BEGIN
    RETURN price * quantity;
END //
DELIMITER ;