Stored Procedures in MySQL

-- Simple procedure
DELIMITER $$
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
  SELECT * FROM users WHERE id = user_id;
END$$
DELIMITER ;

-- Call procedure
CALL GetUserById(123);

-- Procedure with OUT parameter
DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM users;
END$$
DELIMITER ;

-- Call with output
CALL GetUserCount(@count);
SELECT @count;

-- DETAILED_TAB:
-- Complex procedure with error handling
DELIMITER $$
CREATE PROCEDURE TransferFunds(
  IN from_account INT,
  IN to_account INT,
  IN amount DECIMAL(10,2)
)
BEGIN
  DECLARE exit handler for sqlexception
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  UPDATE accounts 
  SET balance = balance - amount
  WHERE id = from_account AND balance >= amount;
  
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  UPDATE accounts
  SET balance = balance + amount
  WHERE id = to_account;
  
  COMMIT;
END$$
DELIMITER ;