Stored Procedures in MySQL
Stored procedures are reusable SQL code blocks stored in the database.
Creating
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
SELECT o.id, o.amount, o.created_at
FROM orders o
WHERE o.user_id = userId
ORDER BY o.created_at DESC;
END //
DELIMITER ;Calling
CALL GetUserOrders(42);With OUT Parameter
DELIMITER //
CREATE PROCEDURE GetOrderCount(IN userId INT, OUT orderCount INT)
BEGIN
SELECT COUNT(*) INTO orderCount
FROM orders WHERE user_id = userId;
END //
DELIMITER ;
CALL GetOrderCount(42, @count);
SELECT @count;