MySQL Stored Functions Explained — Create, Use, and Avoid Common Pitfalls

-- ============================================================
-- SCENARIO: An e-commerce platform needs to show each customer
-- their loyalty tier (Bronze / Silver / Gold) based on their
-- total lifetime spend. This logic appears in dashboards, emails,
-- and reports — so it must live in one place.
-- ============================================================

-- Step 1: Switch to your target database
USE ecommerce_db;

-- Step 2: Tell MySQL what delimiter to use while defining the function.
-- Without this, MySQL would interpret the first semicolon inside the
-- function body as the END of the CREATE statement — which breaks everything.
DELIMITER $$

CREATE FUNCTION get_loyalty_tier(
    total_spend DECIMAL(10, 2)   -- lifetime spend in dollars
)
RETURNS VARCHAR(10)              -- will return 'Bronze', 'Silver', or 'Gold'
DETERMINISTIC                   -- same input always gives same output (no randomness, no NOW())
COMMENT 'Returns customer loyalty tier based on lifetime spend thresholds'
BEGIN
    -- Declare a local variable to hold our result
    DECLARE tier_label VARCHAR(10);

    -- Apply the business rule: thresholds defined by the product team
    IF total_spend >= 1000.00 THEN
        SET tier_label = 'Gold';
    ELSEIF total_spend >= 250.00 THEN
        SET tier_label = 'Silver';
    ELSE
        SET tier_label = 'Bronze';   -- default for new or low-spend customers
    END IF;

    RETURN tier_label;   -- every code path MUST hit a RETURN
END$$

-- Step 3: Restore the normal semicolon delimiter
DELIMITER ;

-- ============================================================
-- Assume this customers table exists:
-- CREATE TABLE customers (
--     customer_id   INT PRIMARY KEY AUTO_INCREMENT,
--     full_name     VARCHAR(100),
--     email         VARCHAR(150),
--     lifetime_spend DECIMAL(10,2) DEFAULT 0.00
-- );
--
-- With sample data:
-- (1, 'Alice Hartman',  'alice@example.com',  1250.75)
-- (2, 'Ben Okafor',     'ben@example.com',     310.00)
-- (3, 'Cleo Ramirez',   'cleo@example.com',     89.50)
-- ============================================================

-- USE CASE 1: Show every customer with their computed loyalty tier
SELECT
    customer_id,
    full_name,
    lifetime_spend,
    get_loyalty_tier(lifetime_spend) AS loyalty_tier   -- function called inline
FROM customers
ORDER BY lifetime_spend DESC;

-- USE CASE 2: Filter — show only Gold-tier customers for a VIP email campaign
SELECT
    full_name,
    email
FROM customers
WHERE get_loyalty_tier(lifetime_spend) = 'Gold';

-- USE CASE 3: Quick sanity check — test the function directly
SELECT
    get_loyalty_tier(1500.00) AS result_gold,
    get_loyalty_tier(500.00)  AS result_silver,
    get_loyalty_tier(10.00)   AS result_bronze;

-- ============================================================
-- EXAMPLE 1: Correctly declared DETERMINISTIC function
-- Converts a raw numeric discount rate (e.g. 0.15) to a
-- human-readable label. Same input = same output. Always.
-- ============================================================
DELIMITER $$

CREATE FUNCTION format_discount_label(
    discount_rate DECIMAL(5, 4)   -- e.g. 0.1500 for 15%
)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL                            -- no database reads or writes
BEGIN
    RETURN CONCAT(FORMAT(discount_rate * 100, 0), '% OFF');
END$$

DELIMITER ;

-- Test it:
SELECT format_discount_label(0.1500) AS label;
-- Output: '15% OFF'


-- ============================================================
-- EXAMPLE 2: Correctly declared NOT DETERMINISTIC function
-- Calculates how many days ago an order was placed.
-- The result changes every day — it reads the current date.
-- ============================================================
DELIMITER $$

CREATE FUNCTION days_since_order(
    order_placed_at DATE
)
RETURNS INT
NOT DETERMINISTIC                 -- result changes with time; optimizer must NOT cache it
NO SQL
BEGIN
    RETURN DATEDIFF(CURDATE(), order_placed_at);
END$$

DELIMITER ;

-- Test it (assuming today is 2025-07-15):
SELECT days_since_order('2025-07-01') AS days_ago;
-- Output: 14


-- ============================================================
-- EXAMPLE 3: Function that READS from a table
-- Gets a product's current category name by product_id.
-- Requires READS SQL DATA because it runs a SELECT.
-- ============================================================
DELIMITER $$

CREATE FUNCTION get_product_category(
    product_id_input INT
)
RETURNS VARCHAR(100)
NOT DETERMINISTIC                 -- data in the table can change between calls
READS SQL DATA                    -- required: this function runs a SELECT statement
BEGIN
    DECLARE category_name VARCHAR(100);

    SELECT c.category_name
    INTO category_name             -- stores query result into the local variable
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    WHERE p.product_id = product_id_input
    LIMIT 1;                       -- LIMIT 1 prevents 'Result consisted of more than one row' error

    RETURN IFNULL(category_name, 'Uncategorized');  -- graceful fallback if no match
END$$

DELIMITER ;

-- ============================================================
-- INSPECTING FUNCTIONS
-- ============================================================

-- List all stored functions in the current database
SHOW FUNCTION STATUS WHERE Db = 'ecommerce_db';

-- View the exact CREATE statement for an existing function
-- (useful for code review, documentation, or migrating servers)
SHOW CREATE FUNCTION get_loyalty_tier;

-- Query the information schema for richer metadata
SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DATA_TYPE         AS return_type,
    IS_DETERMINISTIC,
    ROUTINE_COMMENT   AS description,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'ecommerce_db'
  AND ROUTINE_TYPE   = 'FUNCTION'
ORDER BY ROUTINE_NAME;


-- ============================================================
-- UPDATING A FUNCTION (drop-and-recreate pattern)
-- The product team changed the Gold threshold from $1000 to $750
-- ============================================================

-- Always use IF EXISTS to avoid an error if it somehow doesn't exist
DROP FUNCTION IF EXISTS get_loyalty_tier;

DELIMITER $$

CREATE FUNCTION get_loyalty_tier(
    total_spend DECIMAL(10, 2)
)
RETURNS VARCHAR(10)
DETERMINISTIC
COMMENT 'v2: Gold threshold lowered to $750 per 2025 loyalty program update'
BEGIN
    DECLARE tier_label VARCHAR(10);

    IF total_spend >= 750.00 THEN      -- updated threshold
        SET tier_label = 'Gold';
    ELSEIF total_spend >= 250.00 THEN
        SET tier_label = 'Silver';
    ELSE
        SET tier_label = 'Bronze';
    END IF;

    RETURN tier_label;
END$$

DELIMITER ;


-- ============================================================
-- CONTROLLING PERMISSIONS
-- ============================================================

-- Grant the app's read-only user permission to CALL functions
-- but NOT to create or modify them
GRANT EXECUTE ON FUNCTION ecommerce_db.get_loyalty_tier TO 'app_readonly'@'%';

-- Grant a developer account full routine management rights
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE
    ON ecommerce_db.*
    TO 'dev_user'@'localhost';

-- Revoke all routine privileges from a deprecated service account
REVOKE EXECUTE ON ecommerce_db.* FROM 'legacy_service'@'%';

-- Verify current grants on the function
SHOW GRANTS FOR 'app_readonly'@'%';