? MySQL User Defined Functions

? Quick Overview

A User Defined Function (UDF) in MySQL is a reusable program unit that accepts parameters, performs operations, and returns a single value. Functions are commonly used inside SQL statements like SELECT, WHERE, and ORDER BY.

? Key Concepts

• Returns exactly one value
• Can accept input parameters
• Cannot return result sets
• Used inside SQL expressions
• Improves code reusability and readability

? Syntax / Theory

Basic structure of a MySQL user defined function:

-- General syntax of MySQL user defined function
CREATE FUNCTION function_name(parameter datatype)
RETURNS return_datatype
DETERMINISTIC
BEGIN
RETURN value;
END;

? Code Example(s)

? Example 1: Simple Addition Function

-- Function to add two integers
DELIMITER $$
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN a + b;
END$$
DELIMITER ;

? Example 2: Calculate Annual Salary

-- Function to calculate yearly salary from monthly salary
DELIMITER $$
CREATE FUNCTION annual_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN monthly_salary * 12;
END$$
DELIMITER ;

? Live Output / Explanation

-- Calling user defined functions in SELECT statement
SELECT add_numbers(10, 20) AS result;
SELECT annual_salary(25000) AS yearly_salary;

? Interactive Playground

Simulate how a UDF works logically. Below is a Discount Calculator. In MySQL, this would be a function like calculate_final_price(price, discount).