MySQL Stored Function

A stored function in MySQL is a set of SQL statements that perform some task/operation and return a single value. It is one of the types of stored programs in MySQL. When you will create a stored function, make sure that you have a CREATE ROUTINE database privilege. Generally, we used this function to encapsulate the common business rules or formulas reusable in stored programs or SQL statements.

The stored function is almost similar to the procedure in MySQL, but it has some differences that are as follows:

• The function parameter may contain only the IN parameter but can't allow specifying this parameter, while the procedure can allow IN, OUT, INOUT parameters.
• The stored function can return only a single value defined in the function header.
• The stored function may also be called within SQL statements.
• It may not produce a result set.

Thus, we will consider the stored function when our program's purpose is to compute and return a single value only or create a user-defined function.

The syntax of creating a stored function in MySQL is as follows:

Parameter Used

The stored function syntax uses the following parameters which are discussed below:

MySQL Stored Function Example

Let us understand how stored function works in MySQL through the example. Suppose our database has a table named "customer" that contains the following data:

Now, we will create a function that returns the customer occupation based on the age using the below statement.

Execute the above statement on the command-line tool, as shown below:

Once the function creation is successful, we can see it in the MySQL workbench under the Function section like below image:

We can also see all stored functions available in the current database using the following statement:

After executing the above command, we will get the output as below:

Stored Function Call

Now, we are going to see how stored function is called with the SQL statement. The following statement uses customer_occupation stored function to get the result:

It will give the output as below.

We can also call the above function within another stored program, such as procedure, function, or trigger or any other MySQL built-in function.

Stored Function Call in Procedure

Here, we are going to see how this function can be called in a stored procedure. This statement creates a procedure in a database that uses Customer_Occupation() stored function.

The below statement can be used to call the stored procedure:

We will get the output as below: