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:
| Parameter Name | Descriptions |
|---|---|
| fun_name | It is the name of the stored function that we want to create in a database. It should not be the same as the built-in function name of MySQL. |
| fun_parameter | It contains the list of parameters used by the function body. It does not allow to specify IN, OUT, INOUT parameters. |
| datatype | It is a data type of return value of the function. It should any valid MySQL data type. |
| characteristics | The CREATE FUNCTION statement only accepted when the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are defined in the declaration. |
| fun_body | This parameter has a set of SQL statements to perform the operations. It requires at least one RETURN statement. When the return statement is executed, the function will be terminated automatically. The function body is given below: BEGIN — SQL statements END $$ DELIMITER |
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:
