User-Defined Functions

Learning Focus

Use this lesson to understand User-Defined Functions (UDFs) — server-side functions you write once and call anywhere in your SQL, just like built-in functions.

Concept Overview

What Is a User-Defined Function?

A User-Defined Function is a named SQL routine that returns a single value. Unlike procedures (which you CALL), functions are used inside SQL expressions:

-- Use it like a built-in function
SELECT full_name, calculate_discount(total_amount, 'gold') AS discount
FROM customers
JOIN orders ON ...;

Functions vs. Procedures

Feature	Function	Procedure
Returns	Exactly one value	Zero or more result sets via OUT params
Called with	Inside expressions: `SELECT func()`	`CALL proc()`
Side effects	Should NOT modify data	Can INSERT, UPDATE, DELETE
Use in WHERE	✅ `WHERE func(col) > 10`	❌ Cannot be used in WHERE
Deterministic	Ideally yes (same input → same output)	Not required

Why Use Functions?

DRY (Don't Repeat Yourself) — calculate tax, discount, or formatting once, use everywhere
Consistency — every query uses the same calculation logic
Readability — calculate_tax(amount) is clearer than inline math scattered across queries
Testability — call the function directly to verify its output

Basic Syntax & Rules

Creating a Function

DELIMITER //

CREATE FUNCTION function_name(param1 TYPE, param2 TYPE)
RETURNS return_type
DETERMINISTIC
BEGIN
    DECLARE result return_type;
    -- calculation logic
    SET result = ...;
    RETURN result;
END //

DELIMITER ;

Key Requirements

Requirement	Description
`RETURNS type`	Must declare what type the function returns
`RETURN value`	Must include a RETURN statement in every code path
`DETERMINISTIC` or `NOT DETERMINISTIC`	Tells MySQL if the function always returns the same result for the same input
No data modification	Functions should not contain INSERT, UPDATE, or DELETE

DETERMINISTIC vs. NOT DETERMINISTIC

Type	Meaning	Example
`DETERMINISTIC`	Same input always produces same output	Tax calculation, string formatting
`NOT DETERMINISTIC`	Output may vary	Functions using `NOW()`, `RAND()`, or reading changing data

MySQL requires you to declare this. If you're wrong (marking a non-deterministic function as DETERMINISTIC), the optimizer may cache incorrect results.

Step-by-Step Examples

Example 1: Tax Calculator

Step 1: Create the function

DELIMITER //

CREATE FUNCTION calculate_tax(
    p_amount  DECIMAL(12,2),
    p_tax_pct DECIMAL(5,2)
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_amount * p_tax_pct / 100, 2);
END //

DELIMITER ;

Step 2: Use it in a query

SELECT
    order_id,
    total_amount,
    calculate_tax(total_amount, 11.00)  AS tax,
    total_amount + calculate_tax(total_amount, 11.00) AS grand_total
FROM orders
LIMIT 5;

order_id	total_amount	tax	grand_total
1001	250.00	27.50	277.50
1002	180.50	19.86	200.36

Example 2: Customer Tier Based on Spending

Step 1: Create a function that returns a tier label

DELIMITER //

CREATE FUNCTION get_customer_tier(p_lifetime_spend DECIMAL(12,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF p_lifetime_spend >= 10000 THEN
        RETURN 'Platinum';
    ELSEIF p_lifetime_spend >= 5000 THEN
        RETURN 'Gold';
    ELSEIF p_lifetime_spend >= 1000 THEN
        RETURN 'Silver';
    ELSE
        RETURN 'Bronze';
    END IF;
END //

DELIMITER ;

Step 2: Use it in a report

SELECT
    c.full_name,
    SUM(o.total_amount) AS lifetime_spend,
    get_customer_tier(SUM(o.total_amount)) AS tier
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY lifetime_spend DESC;

full_name	lifetime_spend	tier
Alice Rahman	12500.00	Platinum
Bob Santoso	6200.00	Gold
Charlie Lim	800.00	Bronze

Example 3: Formatting Helper

DELIMITER //

CREATE FUNCTION format_currency(
    p_amount   DECIMAL(12,2),
    p_currency VARCHAR(3)
)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    RETURN CONCAT(p_currency, ' ', FORMAT(p_amount, 2));
END //

DELIMITER ;

SELECT order_id, format_currency(total_amount, 'IDR') AS formatted
FROM orders LIMIT 3;

order_id	formatted
1001	IDR 250.00
1002	IDR 180.50

Example 4: Managing Functions

-- View function source
SHOW CREATE FUNCTION calculate_tax\G

-- List all functions
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Drop a function
DROP FUNCTION IF EXISTS calculate_tax;

Practical Use Cases

1. Tax and Discount Calculations

Centralize financial formulas so every query uses the exact same logic.

2. Tiering and Classification

Classify customers, products, or risk levels based on thresholds — one function replaces scattered CASE statements.

3. Data Formatting

Format currencies, dates, or phone numbers consistently across all queries.

4. Derived Columns in Views

Views can call functions to compute columns that don't exist in the base table.

5. Validation Helpers

Create functions like is_valid_email(p_email) that return TRUE/FALSE for use in CHECK constraints or procedure logic.

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Forgetting `RETURNS` clause	Syntax error	Always declare the return type
Missing `RETURN` in a code path	Function returns NULL unexpectedly	Ensure every IF/ELSE branch has a RETURN
Wrongly marking as `DETERMINISTIC`	Optimizer caches stale results	Only use DETERMINISTIC if same input truly gives same output
Embedding heavy queries inside functions	Multiplied N+1 performance cost	Keep functions lightweight; use JOINs instead
Using functions in WHERE on large tables	Function called once per row — no index usage	If critical for filtering, consider generated columns + indexes
Modifying data inside a function	Violates function contract, unexpected side effects	Use procedures for data modification, not functions

Best Practices

Keep functions pure — same input → same output, no side effects
Use descriptive names — calculate_tax() not func1()
Handle edge cases — check for NULL inputs and return sensible defaults
Avoid heavy queries — functions should do math/logic, not scan tables
Document parameters — what units, ranges, and types are expected
Test independently — SELECT calculate_tax(100, 10) should return 10.00

Hands-On Practice

Exercise 1: Percentage Calculator (Easy)

Create a function calculate_percentage(p_part DECIMAL, p_whole DECIMAL) that returns the percentage (e.g., 25 out of 100 → 25.00). Handle division by zero by returning 0.

Exercise 2: Age from Birth Date (Medium)

Create a function calculate_age(p_birth_date DATE) that:

Returns the age in years (integer)
Handles NULL input by returning NULL
Is marked as NOT DETERMINISTIC (because the result changes daily)

Use it: SELECT full_name, calculate_age(birth_date) AS age FROM users;

Exercise 3: Scoring Function (Advanced)

Create a function calculate_credit_score(p_customer_id BIGINT) that:

Reads the customer's total orders, lifetime spend, and account age
Returns a score from 0–100 based on a formula you design
Discuss: should this be DETERMINISTIC or NOT? Why?

Connection to Other Concepts

Related Concept	How It Connects
Stored Procedures	Procedures handle workflows; functions handle calculations
Control Flow	IF/ELSE, CASE — used inside functions for branching logic
Views	Views can call functions to create computed columns
CHECK Constraints	Functions can validate data in constraint expressions
Generated Columns	`ALTER TABLE ADD col AS (func(...))` — pre-compute function results

What to Learn Next

Control Flow and Error Handling — add IF/ELSE, loops, and handlers to your routines

Visual Learning Diagram

flowchart TD
    A["SELECT query"] --> B["Encounters calculate_tax(amount, rate)"]
    B --> C["Function executes on server"]
    C --> D["Returns single value"]
    D --> E["Value used in result set"]

    F["Stored Procedure"] -.->|"CALL, workflows"| G["Multi-step operations"]
    H["Function"] -.->|"Expressions, SELECT"| I["Single value calculations"]

    classDef func fill:#0d6efd,stroke:#fff,color:#fff
    class C,H func

Quick Reference

-- Create function
DELIMITER //
CREATE FUNCTION func_name(p1 TYPE, p2 TYPE)
RETURNS RETURN_TYPE
DETERMINISTIC
BEGIN
    RETURN expression;
END //
DELIMITER ;

-- Use in query
SELECT func_name(col1, col2) FROM table;
SELECT * FROM table WHERE func_name(col) > 100;

-- View/list/drop
SHOW CREATE FUNCTION func_name\G
SHOW FUNCTION STATUS WHERE Db = DATABASE();
DROP FUNCTION IF EXISTS func_name;

What's Next

Control Flow and Error Handling

Concept Overview​

What Is a User-Defined Function?​

Functions vs. Procedures​

Why Use Functions?​

Basic Syntax & Rules​

Creating a Function​

Key Requirements​

DETERMINISTIC vs. NOT DETERMINISTIC​

Step-by-Step Examples​

Example 1: Tax Calculator​

Example 2: Customer Tier Based on Spending​

Example 3: Formatting Helper​

Example 4: Managing Functions​

Practical Use Cases​

1. Tax and Discount Calculations​

2. Tiering and Classification​

3. Data Formatting​

4. Derived Columns in Views​

5. Validation Helpers​

Common Mistakes & Troubleshooting​

Best Practices​

Hands-On Practice​

Exercise 1: Percentage Calculator (Easy)​

Exercise 2: Age from Birth Date (Medium)​

Exercise 3: Scoring Function (Advanced)​

Connection to Other Concepts​

What to Learn Next​

Visual Learning Diagram​

Quick Reference​

What's Next​