What are Stored Procedures?

-- MySQL
DELIMITER //
CREATE PROCEDURE GetActiveUsers()
BEGIN
  SELECT * FROM users WHERE status = 'active';
END //
DELIMITER ;

-- Call it
CALL GetActiveUsers();

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

CALL GetUserOrders(5);

-- OUT parameter (output)
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(5, @count);
SELECT @count;

DELIMITER //
CREATE PROCEDURE ProcessOrder(IN orderId INT)
BEGIN
  DECLARE orderTotal DECIMAL(10,2);
  DECLARE userEmail VARCHAR(255);

  -- Get order total
  SELECT total INTO orderTotal FROM orders WHERE id = orderId;

  -- Apply discount if over 100
  IF orderTotal > 100 THEN
    UPDATE orders SET total = orderTotal * 0.9 WHERE id = orderId;
  END IF;

  -- Get user email for notification
  SELECT u.email INTO userEmail
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.id = orderId;

  -- Log the action
  INSERT INTO logs (message) VALUES (CONCAT('Processed order ', orderId));
END //
DELIMITER ;