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:

DELIMITER $$  

   

CREATE FUNCTION fun_name(fun_parameter(s))  

RETURNS datatype  

[NOT] {Characteristics}  

fun_body;

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.

 DELIMITER $$  

CREATE FUNCTION Customer_Occupation(  

    age int  

)   

RETURNS VARCHAR(20)  

DETERMINISTIC  

BEGIN  

    DECLARE customer_occupation VARCHAR(20);  

    IF age > 35 THEN  

        SET customer_occupation = 'Scientist';  

    ELSEIF (age <= 35 AND   

            age >= 30) THEN  

        SET customer_occupation = 'Engineer';  

    ELSEIF age < 30 THEN  

        SET customer_occupation = 'Actor';  

    END IF;  

    -- return the customer occupation  

    RETURN (customer_occupation);  

END$$  

DELIMITER; 

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:

SHOW FUNCTION STATUS WHERE db = 'mysqltestdb';  

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:

SELECT name, age, Customer_Occupation(age)  

FROM customer ORDER BY age;

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.

  DELIMITER $$  

CREATE PROCEDURE GetCustomerDetail()  

BEGIN  

    SELECT name, age, Customer_Occupation(age) FROM customer ORDER BY age;  

END$$  

DELIMITER ;  

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

CALL GetCustomerDetail();  

We will get the output as below: