1. Home
  2. MySQL Stored Function

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:

MySQL Stored Function

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:

MySQL Stored Function

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

MySQL Stored Function

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:

MySQL Stored Function

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.

MySQL Stored Function

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:

MySQL Stored Function
Next TopicMySQL Signal Resignal




Hot Tutorials

Contact US

Email:[email protected]

MySQL Stored Function
10/30