What is a user-defined function, and how do you create one in MySQL?

Question

Accepted Answer

A user-defined function (UDF) is a reusable, named block of SQL logic that you can call directly within queries, just like a built-in function. It lets you encapsulate calculations or transformations that would otherwise need to be repeated across many queries.

Key Points:
• UDFs improve modularity by encapsulating complex or repeated logic in one place.
• They make queries more readable since the calculation detail is hidden behind a descriptive function name.
• Unlike stored procedures, a function must return a single value and can be used directly inside a SELECT statement.
• The DETERMINISTIC keyword tells MySQL the function always returns the same output for the same input, which allows certain query optimizations.
• Changes to business logic only need to be made in one place, the function definition, instead of every query that used the old logic.

Example:
An e-commerce application needs to calculate sales tax on a product's price repeatedly across many queries; instead of rewriting the multiplication each time, it defines a CalculateSalesTax function once and calls it wherever needed.

Code Example:
```
DELIMITER //
CREATE FUNCTION CalculateSalesTax(price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE tax_rate DECIMAL(5, 2) DEFAULT 0.07;
    RETURN price * tax_rate;
END //
DELIMITER ;

SELECT product_name, price, CalculateSalesTax(price) AS sales_tax
FROM products;
```

Interview Tip:
A concise interview answer is:

"A user-defined function packages reusable SQL logic under a name I can call from within a query, similar to a built-in function like SUM. I'd create one with CREATE FUNCTION, mark it DETERMINISTIC if the output only depends on its inputs, and use it, for example, to calculate sales tax consistently across every query instead of duplicating the formula everywhere."