Creating stored functions in MySQL

Introduction

Stored functions let you save a small program inside the database that you can use again and again to get a result.

When you want to calculate a value many times without rewriting the code.

When you need to reuse a common calculation in different queries.

When you want to keep your database logic organized and easy to maintain.

When you want to simplify complex queries by using a function.

When you want to share a calculation with other people using the database.

Syntax

MySQL

CREATE FUNCTION function_name (parameters) 
RETURNS data_type
BEGIN
  -- function code here
  RETURN value;
END

The function must return a value using RETURN.

Parameters are optional if your function does not need inputs.

Examples

This function adds two numbers and returns the sum.

MySQL

DELIMITER $$
CREATE FUNCTION add_two_numbers(a INT, b INT) 
RETURNS INT
BEGIN
  RETURN a + b;
END$$
DELIMITER ;

This function returns the value of pi without any input.

MySQL

DELIMITER $$
CREATE FUNCTION get_pi() 
RETURNS FLOAT
BEGIN
  RETURN 3.14159;
END$$
DELIMITER ;

Sample Program

This creates a function that squares a number and then uses it to find the square of 5.

MySQL

DELIMITER $$
CREATE FUNCTION square_number(n INT) 
RETURNS INT
BEGIN
  RETURN n * n;
END$$
DELIMITER ;

SELECT square_number(5) AS result;

Important Notes

Use DELIMITER to change the statement end symbol when creating functions in MySQL.

Stored functions can be used in SELECT, WHERE, and other SQL clauses.

Make sure your function always returns a value to avoid errors.

Summary

Stored functions save reusable code inside the database.

They must return a value using RETURN.

Use DELIMITER to define the function body in MySQL.