User-Defined Functions
In this page:
CREATE FUNCTION function_name(param datatype)
RETURNS return_datatype DETERMINISTIC
BEGIN
RETURN value;
ENDWhat is a User-Defined Function?
A user-defined function packages reusable logic that always returns exactly one value, which lets you drop it directly into a SELECT statement's column list or WHERE clause just like a built-in function.
Example: What is a User-Defined Function?
DELIMITER //
CREATE FUNCTION FullName(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
RETURN CONCAT(first_name, ' ', last_name);
END //
DELIMITER ;
SELECT FullName('Amit', 'Sharma');Functions with Multiple Arguments
Functions can accept multiple arguments the same way built-in functions do, which is useful for calculations that depend on combining several inputs, like computing a discount from a price and a percentage.
Example: Functions with Multiple Arguments
DELIMITER //
CREATE FUNCTION ApplyDiscount(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
RETURN price - (price * pct / 100);
END //
DELIMITER ;
SELECT ApplyDiscount(100, 20);Functions vs Stored Procedures
The key distinction from stored procedures is that functions must return a single value and are called from within a query, while procedures are invoked with CALL and can return multiple result sets or none at all.
Example: Functions vs Stored Procedures
-- A function returns exactly one value and can be used in a SELECT list:
SELECT ApplyDiscount(100, 20);
-- A procedure is invoked with CALL and can return multiple result sets:
CALL GetAllUsers();Dropping a Function
DROP FUNCTION removes a user-defined function from the database schema permanently, the same way DROP PROCEDURE removes a stored procedure.
Example: Dropping a Function
DROP FUNCTION IF EXISTS ApplyDiscount;Deterministic and Non-Deterministic
A function marked deterministic always produces the same output for the same inputs, while a non-deterministic one — such as one that reads the current time — can return different results on each call even with identical arguments.
Example: Deterministic and Non-Deterministic
DELIMITER //
CREATE FUNCTION CurrentYear() RETURNS INT NOT DETERMINISTIC
BEGIN
RETURN YEAR(NOW());
END //
DELIMITER ;
SELECT CurrentYear();- Forgetting
RETURNS, or aRETURNstatement inside the function body. - Omitting
DETERMINISTIC(orREADS SQL DATA), which causes an error when binary logging is enabled. - Using
CALLfor a function, when functions are used inside expressions likeSELECT FullName(...).
Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: