SQL Tutorial › Performance & Objects › User-Defined Functions

SQL User-Defined Functions

Q: How do I create a function in MySQL?

Use CREATE FUNCTION name(params) RETURNS type [DETERMINISTIC | READS SQL DATA] BEGIN ... RETURN value; END, wrapped in DELIMITER // in the mysql client.

AdvancedLesson 64 of 699 min read3 queries run on MySQL 8.4

MySQL has hundreds of built-in functions, but business rules are your own: GST on a price, a customer tier from total spend, a formatted employee code. A stored function, created with CREATE FUNCTION, packages such a rule so you can use it inside any query, just like UPPER() or ROUND().

In this lesson

Create a function with parameters and RETURNS
Mark functions DETERMINISTIC correctly
Use functions in SELECT, WHERE and ORDER BY
Choose between a function and a procedure

A first function

MySQL

DELIMITER //CREATE FUNCTION price_with_gst(p_price INT)RETURNS INTDETERMINISTICBEGIN  RETURN ROUND(p_price * 1.18);END //DELIMITER ; SELECT product_name, price, price_with_gst(price) AS price_incl_gstFROM productsWHERE category = 'Electronics';

ResultMySQL 8.4

mysql> CREATE FUNCTION price_with_gst(p_price INT)

Query OK, 0 rows affected

mysql> SELECT product_name, price, price_with_gst(price) AS price_incl_gst

product_name	price	price_incl_gst
Wireless Mouse	799	943
Mechanical Keyboard	3499	4129
USB-C Hub	1999	2359

3 rows in set

How this query works, step by step

CREATE FUNCTION price_with_gst(p_price INT) takes one input. Function parameters are always input-only.
RETURNS INT declares the type of the result.
DETERMINISTIC promises that the same input always gives the same output.
RETURN sends back the value; the function is then used in SELECT like a built-in one.

Note

MySQL with binary logging enabled (the default) refuses to create a function unless it is declared DETERMINISTIC, NO SQL or READS SQL DATA. Declare honestly: a function that reads tables is READS SQL DATA.

A function that reads data

Customer tier from total spend:

MySQL

DELIMITER //CREATE FUNCTION customer_tier(p_customer_id INT)RETURNS VARCHAR(10)READS SQL DATABEGIN  DECLARE v_spend INT;  SELECT COALESCE(SUM(oi.quantity * p.price), 0) INTO v_spend  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.customer_id = p_customer_id AND o.status <> 'Cancelled';   RETURN CASE           WHEN v_spend >= 20000 THEN 'Gold'           WHEN v_spend >= 5000  THEN 'Silver'           WHEN v_spend > 0      THEN 'Bronze'           ELSE 'New'         END;END //DELIMITER ; SELECT customer_id, customer_name, customer_tier(customer_id) AS tierFROM customersORDER BY customer_id;

ResultMySQL 8.4

mysql> CREATE FUNCTION customer_tier(p_customer_id INT)

Query OK, 0 rows affected

mysql> SELECT customer_id, customer_name, customer_tier(customer_id) AS tier

customer_id	customer_name	tier
1	Ananya Sharma	Gold
2	Rohit Kumar	Silver
3	Fatima Khan	Bronze
4	David Fernandes	Bronze
5	Ishita Bose	Silver
6	Manoj Pillai	New
7	Sara Thomas	New

7 rows in set

How this query works, step by step

The function sums the customer’s non-cancelled order lines into v_spend.
A CASE turns the amount into a tier.
The query calls the function once per customer row.

Watch out

A function that runs a query is called once for every row. On a large table that is a hidden loop of queries and can be very slow. For reports over many rows, a join with GROUP BY is usually much faster.

Functions in WHERE and ORDER BY

MySQL

DELIMITER //CREATE FUNCTION emp_code(p_last_name VARCHAR(30), p_emp_id INT)RETURNS VARCHAR(12)DETERMINISTICRETURN CONCAT(UPPER(LEFT(p_last_name, 3)), '-', p_emp_id) //DELIMITER ; SELECT emp_id, first_name, emp_code(last_name, emp_id) AS codeFROM employeesWHERE emp_code(last_name, emp_id) LIKE 'S%'ORDER BY code;

ResultMySQL 8.4

mysql> CREATE FUNCTION emp_code(p_last_name VARCHAR(30), p_emp_id INT)

Query OK, 0 rows affected

mysql> SELECT emp_id, first_name, emp_code(last_name, emp_id) AS code

emp_id	first_name	code
104	Karan	SHA-104
105	Vikram	SIN-105

2 rows in set

How this query works, step by step

A one-line function does not need BEGIN ... END.
It can be used in WHERE and ORDER BY like any expression: employees whose code starts with S (Shah and Singh).

Manage functions

Task	Statement
List	`SHOW FUNCTION STATUS WHERE Db = 'company';`
See the code	`SHOW CREATE FUNCTION customer_tier;`
Remove	`DROP FUNCTION IF EXISTS customer_tier;`

Function vs procedure

	Function	Procedure
Called with	inside SQL: `SELECT f(x)`	`CALL p(x)`
Returns	exactly one value (`RETURN`)	result sets and/or OUT parameters
Parameters	IN only	IN, OUT, INOUT
Transactions (`COMMIT`)	not allowed	allowed
Typical use	calculations, formatting	multi-step operations

Quick quiz

1. How is a stored function used?

2. What does DETERMINISTIC mean?

3. Which can contain COMMIT?

User-defined function FAQ

How do I create a function in MySQL?

Use CREATE FUNCTION name(params) RETURNS type [DETERMINISTIC | READS SQL DATA] BEGIN ... RETURN value; END, wrapped in DELIMITER // in the mysql client.

What is the difference between a function and a stored procedure in SQL?

A function returns one value and is used inside queries. A procedure is executed with CALL, can return result sets and OUT parameters, and can manage transactions.

Why do I get error 1418 when creating a function?

Binary logging requires functions to declare their nature. Add DETERMINISTIC, NO SQL or READS SQL DATA to the definition.