MySQL Stored Function

Summary: in this tutorial, you will learn how to create a stored function using the CREATE FUNCTION statement.

Introduction to MySQL Stored Function

A stored function is a specialized type of stored program designed to return a single value. Typically, you use stored functions to encapsulate common formulas or business rules, making them reusable across SQL statements or other stored programs.

Unlike a stored procedure, you can use a stored function in SQL statements wherever you use an expression. This enhances the readability and maintainability of the procedural code.

To create a stored function, you use the CREATE FUNCTION statement. The following illustrates the basic syntax for creating a new stored function:

DELIMITER $$

CREATE FUNCTION function_name(
    param1,
    param2,…
)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the stored function that you want to create after CREATE FUNCTION keywords.

Second, list all parameters of the stored function inside the parentheses followed by the function name.

By default, stored functions consider all parameters as IN parameters. You cannot specify IN , OUT or INOUT modifiers to parameters

Third, specify the data type of the return value in the RETURNS statement, which can be any valid MySQL data types.

Fourth, determine whether a function is deterministic or not using the DETERMINISTIC keyword.

A deterministic function always returns the same result for the same input parameters, while a non-deterministic function produces different results for the same input parameters.

If you don’t use DETERMINISTIC or NOT DETERMINISTIC, MySQL defaults to the NOT DETERMINISTIC option.

Finally, write the code in the body of the stored function in the BEGIN...END block.

Inside the body section, you need to include at least one RETURN statement. The RETURN statement sends a value to the calling programs.

Upon reaching the RETURN statement, the stored function terminates the execution of the stored function immediately.

MySQL CREATE FUNCTION example

Let’s take an example of creating a stored function. We will use the customers table in the sample database for the demonstration.

The following CREATE FUNCTION statement creates a function that returns the customer level based on credit:

DELIMITER $$

CREATE FUNCTION CustomerLevel(
	credit DECIMAL(10,2)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Once the function is created, you can view it in MySQL Workbench under the Functions section:

Or, you can view all stored functions in the current classicmodels database by using the SHOW FUNCTION STATUS as follows:

SHOW FUNCTION STATUS WHERE db = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

Calling a stored function in an SQL statement

The following statement illustrates how to call the CustomerLevel stored function:

SELECT 
    customerName, 
    CustomerLevel(creditLimit)
FROM
    customers
ORDER BY 
    customerName;Code language: SQL (Structured Query Language) (sql)

Calling a stored function in a stored procedure

The following statement creates a new stored procedure that calls the CustomerLevel() stored function:

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(
    IN  customerNo INT,  
    OUT customerLevel VARCHAR(20)
)
BEGIN

	DECLARE credit DEC(10,2) DEFAULT 0;
    
    -- get credit limit of a customer
    SELECT 
		creditLimit 
	INTO credit
    FROM customers
    WHERE 
		customerNumber = customerNo;
    
    -- call the function 
    SET customerLevel = CustomerLevel(credit);
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The following illustrates how to call the GetCustomerLevel() stored procedure:

CALL GetCustomerLevel(131,@customerLevel);
SELECT @customerLevel;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+
| @customerLevel |
+----------------+
| PLATINUM       |
+----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

It’s important to notice that if a stored function contains SQL statements that retrieve data from tables, then you should avoid using it in other SQL statements; otherwise, the stored function may slow down the query speed.

Summary

• A stored function is a reusable and encapsulated piece of code in a database that performs a specific task and returns a single value.
• Use the CREATE FUNCTION statement to create a stored function.
• Use stored functions to enhance the modularity and efficiency of SQL statements.