Stored Functions

A stored function returns a single scalar value and can be used inside SQL expressions (SELECT fn(col), WHERE fn(x) > 0).

Concept

A stored function returns a single scalar value and can be used inside SQL expressions (SELECT fn(col), WHERE fn(x) > 0). MySQL does not support table-valued user functions; use views, procedures, JSON_TABLE, or derived tables instead.

Why It Matters

Functions centralize reusable calculations (tax, bands, unit conversions, CAN signal scaling) so every query applies the same logic.

Syntax

DELIMITER //
CREATE FUNCTION [IF NOT EXISTS] fn_name (param data_type, ...)
RETURNS data_type
    [DETERMINISTIC | NOT DETERMINISTIC]
    [NO SQL | READS SQL DATA | CONTAINS SQL | MODIFIES SQL DATA]
BEGIN
    ...
    RETURN expr;
END //
DELIMITER ;
DROP FUNCTION IF EXISTS fn_name;

Example

20.1 Binary logging and determinism With binary logging enabled (default in 8.0), creating a function requires one of DETERMINISTIC, NO SQL, or READS SQL DATA, or the variable log_bin_trust_function_creators = 1; otherwise:

Declare characteristics truthfully: marking a non-deterministic function (using NOW(), RAND(), or table data that changes) as DETERMINISTIC can cause wrong results in replication and caching.

20.2 Procedure vs function Inside expressions: SELECT fn() Result sets, OUT/INOUT params Exactly one scalar value Can START TRANSACTION/COMMIT Cannot commit or roll back Use in WHERE/SELECT

Performance: a function that runs a query (like customer_lifetime_value) executes once per row — effectively a hidden correlated subquery. For large result sets, use a JOIN with GROUP BY instead.

DELIMITER //
CREATE FUNCTION price_with_gst(p_price DECIMAL(12,2), p_rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL
BEGIN
    RETURN ROUND(p_price * (1 + p_rate / 100), 2);
END //

CREATE FUNCTION salary_band(p_salary DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC NO SQL
BEGIN
    RETURN CASE
        WHEN p_salary >= 150000 THEN 'A'
        WHEN p_salary >= 90000  THEN 'B'
        ELSE 'C'
    END;
END //

CREATE FUNCTION can_physical(p_raw BIGINT, p_factor DOUBLE, p_offset DOUBLE)
RETURNS DOUBLE
DETERMINISTIC NO SQL
RETURN p_raw * p_factor + p_offset //       -- single-statement body needs no BEGIN/END

CREATE FUNCTION customer_lifetime_value(p_customer_id INT UNSIGNED)
RETURNS DECIMAL(14,2)
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(14,2);
    SELECT IFNULL(SUM(total_amount), 0) INTO v_total
    FROM orders WHERE customer_id = p_customer_id AND status <> 'CANCELLED';
    RETURN v_total;
END //
DELIMITER ;

SELECT product_name, price, price_with_gst(price, 18) AS gross FROM products WHERE category = 'Furniture';
SELECT first_name, salary_band(salary) AS band FROM employees WHERE dept_id = 2;
SELECT can_physical(4000, 0.1, 0) AS pack_voltage;       -- 400
SEL
…

+---------------+----------+----------+
| product_name  | price    | gross    |
+---------------+----------+----------+
| Desk Chair    | 12000.00 | 14160.00 |
| Standing Desk | 35000.00 | 41300.00 |
+---------------+----------+----------+

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

Expected Result

SELECT name, customer_lifetime_value(customer_id) AS clv FROM customers ORDER BY clv DESC, name;

+--------------+-----------+
| name         | clv       |
+--------------+-----------+
| Ben Carter   | 105000.00 |
| Ananya Gupta |  93900.00 |
| Diya Patel   |   1600.00 |
| Chen Wei     |      0.00 |
| Elena Rossi  |      0.00 |
+--------------+-----------+

Common Mistakes

Error 1418 because characteristics are missing.

Lying about DETERMINISTIC.

Functions on indexed columns in WHERE (non-sargable).

Per-row query functions on large tables.

Best Practices

Keep functions pure (DETERMINISTIC NO SQL) where possible.

Prefer inline expressions or generated columns for simple calculations used in filters.

Test with NULL inputs (a function receiving NULL usually returns NULL).

Interview Relevance

Medium: "Procedure vs function?", "What does DETERMINISTIC mean?", "Why error 1418?", "Can MySQL functions return tables?" (no).

Practice Questions

Write working_days(p_start DATE, p_end DATE) returning the number of weekdays.

Write vin_is_valid(p_vin CHAR(17)) using REGEXP_LIKE.

Rewrite customer_lifetime_value usage as a JOIN and compare EXPLAIN plans.