? MySQL Stored Functions

? Quick Overview

A MySQL Stored Function is a database object that accepts input parameters, performs calculations or logic, and returns a single value. Stored functions are commonly used inside SQL queries such as SELECT, WHERE, and ORDER BY.

? Key Concepts

• Always returns exactly one value
• Can be reused across multiple queries
• Improves query readability and consistency
• Executed on the database server
• Can be deterministic or non-deterministic

? Syntax / Theory

-- Basic structure of a stored function
CREATE FUNCTION function_name(parameters)
RETURNS data_type
DETERMINISTIC
BEGIN
RETURN value;
END;

? Code Example(s)

? Example 1: Simple Addition Function

-- Function to add two numbers
DELIMITER $$

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN a + b;
END$$

DELIMITER ;

? Example 2: Function with Business Logic

-- Function to calculate final price with tax
DELIMITER $$

CREATE FUNCTION final_price(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN price + (price * 0.18);
END$$

DELIMITER ;

? Live Output / Explanation

-- Using stored functions in SELECT query
SELECT add_numbers(10, 20) AS total;
SELECT final_price(1000) AS amount_with_tax;