A stored procedure is a named block of SQL code stored in the database that can be called by name. Procedures encapsulate business logic in the database layer, reduce network round-trips, and can be reused across multiple applications. They support parameters, variables, conditional logic, loops, and cursors.
-- Change the delimiter so MySQL doesn't interpret ; inside the procedure
DELIMITER $$
-- Simple procedure: get all orders for a customer
CREATE PROCEDURE GetCustomerOrders(IN p_customer_id INT)
BEGIN
SELECT
o.order_id,
o.total,
o.status,
o.ordered_at
FROM orders o
WHERE o.customer_id = p_customer_id
ORDER BY o.ordered_at DESC;
END$$
-- Procedure with IN, OUT, and INOUT parameters
CREATE PROCEDURE GetOrderStats(
IN p_customer_id INT,
OUT p_order_count INT,
OUT p_total_spent DECIMAL(10,2)
)
BEGIN
SELECT COUNT(*), COALESCE(SUM(total), 0)
INTO p_order_count, p_total_spent
FROM orders
WHERE customer_id = p_customer_id;
END$$
DELIMITER ;
-- Call the procedures
CALL GetCustomerOrders(1);
CALL GetOrderStats(1, @count, @spent);
SELECT @count AS order_count, @spent AS total_spent;
-- Show all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'shop';
-- Drop a procedure
DROP PROCEDURE IF EXISTS GetCustomerOrders;DELIMITER $$
CREATE PROCEDURE ClassifyCustomer(IN p_customer_id INT, OUT p_tier VARCHAR(20))
BEGIN
DECLARE v_total_spent DECIMAL(10,2) DEFAULT 0;
-- Get total spent by this customer
SELECT COALESCE(SUM(total), 0)
INTO v_total_spent
FROM orders
WHERE customer_id = p_customer_id AND status = 'delivered';
-- Classify based on spending
IF v_total_spent >= 1000 THEN
SET p_tier = 'Platinum';
ELSEIF v_total_spent >= 500 THEN
SET p_tier = 'Gold';
ELSEIF v_total_spent >= 100 THEN
SET p_tier = 'Silver';
ELSE
SET p_tier = 'Bronze';
END IF;
END$$
-- Procedure with WHILE loop: apply bulk discount
CREATE PROCEDURE ApplyCategoryDiscount(IN p_category VARCHAR(100), IN p_discount DECIMAL(5,2))
BEGIN
DECLARE v_done INT DEFAULT 0;
DECLARE v_prod_id INT;
DECLARE v_price DECIMAL(10,2);
-- Cursor to iterate over products in the category
DECLARE cur CURSOR FOR
SELECT product_id, price FROM products WHERE category = p_category;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_prod_id, v_price;
IF v_done THEN
LEAVE read_loop;
END IF;
UPDATE products
SET price = ROUND(v_price * (1 - p_discount / 100), 2)
WHERE product_id = v_prod_id;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
-- Call the procedures
CALL ClassifyCustomer(1, @tier);
SELECT @tier;
CALL ApplyCategoryDiscount('Electronics', 10.00);A stored function is similar to a procedure but always returns a single value and can be used directly in SQL expressions (like built-in functions). Functions cannot modify data (no INSERT/UPDATE/DELETE by default).
DELIMITER $$
-- Function: calculate discounted price
CREATE FUNCTION DiscountedPrice(p_price DECIMAL(10,2), p_discount_pct DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN ROUND(p_price * (1 - p_discount_pct / 100), 2);
END$$
-- Function: get customer full name
CREATE FUNCTION GetFullName(p_customer_id INT)
RETURNS VARCHAR(101)
READS SQL DATA
BEGIN
DECLARE v_name VARCHAR(101);
SELECT CONCAT(first_name, ' ', last_name)
INTO v_name
FROM customers
WHERE customer_id = p_customer_id;
RETURN COALESCE(v_name, 'Unknown');
END$$
DELIMITER ;
-- Use functions in queries (just like built-in functions)
SELECT
name,
price,
DiscountedPrice(price, 15) AS sale_price
FROM products
WHERE category = 'Electronics';
SELECT order_id, GetFullName(customer_id) AS customer_name, total
FROM orders
ORDER BY ordered_at DESC
LIMIT 5;
-- Drop a function
DROP FUNCTION IF EXISTS DiscountedPrice;A trigger is a stored program that automatically executes in response to an INSERT, UPDATE, or DELETE event on a table. Triggers run BEFORE or AFTER the event. Use them for audit logging, enforcing business rules, or maintaining derived data.
-- Create an audit log tl-table
CREATE tl-table order_audit_log (
log_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id INT UNSIGNED NOT NULL,
old_status VARCHAR(20),
new_status VARCHAR(20),
changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (log_id)
);
DELIMITER $$
-- AFTER UPDATE trigger: log order status changes
CREATE TRIGGER trg_orders_status_change
AFTER UPDATE ON orders
FOR EACH tl-row
BEGIN
IF OLD.status != NEW.status THEN
INSERT INTO order_audit_log (order_id, old_status, new_status)
VALUES (NEW.order_id, OLD.status, NEW.status);
END IF;
END$$
-- BEFORE INSERT trigger: ensure price is not negative
CREATE TRIGGER trg_products_before_insert
BEFORE INSERT ON products
FOR EACH tl-row
BEGIN
IF NEW.price < 0 THEN
SET NEW.price = 0;
END IF;
END$$
DELIMITER ;
-- Test the trigger
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
SELECT * FROM order_audit_log;
-- Show all triggers
SHOW TRIGGERS FROM shop;
-- Drop a trigger
DROP TRIGGER IF EXISTS trg_orders_status_change;-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;
DELIMITER $$
-- One-time event: run once at a specific time
CREATE EVENT evt_one_time_cleanup
ON SCHEDULE AT '2025-01-01 00:00:00'
DO
BEGIN
DELETE FROM order_audit_log WHERE changed_at < '2024-01-01';
END$$
-- Recurring event: run every day at midnight
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
-- Purge soft-deleted customers older than 90 days
DELETE FROM customers
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 90 DAY;
END$$
DELIMITER ;
-- Show all events
SHOW EVENTS FROM shop;
-- Disable an event
ALTER EVENT evt_daily_cleanup DISABLE;
-- Drop an event
DROP EVENT IF EXISTS evt_one_time_cleanup;Explore 500+ free tutorials across 20+ languages and frameworks.