MySQL Custom Functions: How to Create, Use, and Compare with Stored Procedures

Definition

MySQL built‑in functions cover most data manipulation needs, but for complex business logic that can be performed at the database level, you can write custom MySQL functions.

A function is a pre‑compiled set of SQL statements that must return a single value, similar to a method in C#.

Purpose

Encapsulate business logic in the database layer.

Improve efficiency by reducing data transfer between server and application.

Provide reusable logic that can be called from any language.

Using Functions

Create Function

<code>CREATE FUNCTION func_name(param_list)
RETURNS TYPE
BEGIN
    -- function body
END</code>

param_list is optional; a function must have a single return value and cannot return a result set. Use BEGIN…END for multiple statements and set a custom delimiter if needed.

Call Function

<code>SELECT func_name(param_list);</code>

Show Functions

<code>SHOW FUNCTION STATUS;</code>

Show Create Function

<code>SHOW CREATE FUNCTION func_name;</code>

Drop Function

<code>DROP FUNCTION IF EXISTS func_name;</code>

Example: Parameterless Function

<code>DROP FUNCTION IF EXISTS fun_test1;
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE avg_score DECIMAL(10,2) DEFAULT 0;
    SELECT AVG(score) INTO avg_score FROM students WHERE classid <> 0;
    RETURN avg_score;
END $
DELIMITER ;
SELECT fun_test1();</code>

Example: Function with Parameter

<code>DROP FUNCTION IF EXISTS fun_test2;
DELIMITER $
CREATE FUNCTION fun_test2(cid INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE avg_score DECIMAL(10,2) DEFAULT 0;
    SELECT AVG(score) INTO avg_score FROM students WHERE classid = cid;
    RETURN avg_score;
END $
DELIMITER ;
SELECT fun_test2(1);</code>

Summary

Difference between Stored Procedures and Functions

Aspect

Stored Procedure

Function

Return value

0 or many

Exactly one

Keyword

PROCEDURE

FUNCTION

Invocation

CALL

SELECT