MySQL — Functions

Basic 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('John', 'Doe');

Scalar Function

DELIMITER //
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN amount * rate / 100;
END //
DELIMITER ;

SELECT CalculateTax(100.00, 8.5);

DETERMINISTIC vs NOT DETERMINISTIC

Keyword	Meaning
DETERMINISTIC	Same input = same output
NOT DETERMINISTIC	Output may vary (e.g., uses NOW())

DELIMITER //
CREATE FUNCTION CurrentYear()
RETURNS INT
NOT DETERMINISTIC
BEGIN
    RETURN YEAR(CURDATE());
END //
DELIMITER ;

Function with SQL Statements

DELIMITER //
CREATE FUNCTION CustomerOrderCount(cust_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE order_count INT;
    SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = cust_id;
    RETURN order_count;
END //
DELIMITER ;

SELECT name, CustomerOrderCount(id) AS orders FROM customers;

Dropping Functions

DROP FUNCTION IF EXISTS FullName;

Functions vs Procedures

Feature	Function	Procedure
Return	Single value	Multiple result sets
Call in SQL	Yes (SELECT)	No (CALL)
Parameters	IN only	IN, OUT, INOUT
Side effects	Not recommended	Allowed

Mini Practice

Write SQL code that:

1. Creates a scalar function with parameters
2. Creates a DETERMINISTIC function
3. Uses a function in a SELECT statement
4. Drops a function

Up Next

Continue with Triggers — automatic actions on table events.