MySQL Create Function

Introduction to MySQL Create Function

Whenever we want to execute certain business logic repetitively and the value retrieved from the written business logic involving single or multiple database operations is done in the same way at many different places, then it is convenient to create a MySQL function and call the function to retrieve the value from the business logic and database operation executions by simply calling that function. In MYSQL we use the CREATE FUNCTION statement to create a new function that will be stored and this function can be further called by supplying any number of parameters and returning the required value.

Syntax:

The following is the syntax of CREATE FUNCTION statement –

DELIMITER $$
CREATE FUNCTION name_of_function(
parameter1,
parameter2,…
)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
-- code of statements to be executed
END $$
DELIMITER ;

• name_of_ function – It is the name of the function that needs to be created in MySQL.
• parameter1, parameter2,… – We can pass the optional parameters to the functions that need to be declared while creating it in the () brackets. A function can contain none, one or more than one parameter. These parameters can belong to either of the three types –
• IN – These types of parameters are assigned the values while calling the function and the value cannot be modified or overwritten inside the function but only referenced and used by the function.
• OUT – These are the parameters that can be assigned the values and overridden in the function but cannot be referenced by it.
• IN OUT – These types of parameters are assigned the values while calling the function and the value can be modified or overwritten inside the function as well as referenced and used by the function.
• BEGIN and END – BEGIN keyword marks the beginning of the function while END marks the completion of function in MYSQL.
• RETURN Datatype – We can return any type of value from the execution of the function. The type of value that will be returned needs to be specified after the RETURN clause. Once, MySQL finds the RETURN statement while execution of the function, execution of the function is terminated and the value is returned.
• DETERMINISTIC – The function can be either deterministic or non-deterministic which needs to be specified here. When the function returns the same value for the same values of parameter then it is called deterministic. However, if the function returns a different value for the same values of functions then we can call that function to be nondeterministic. When none of the types of function is mentioned, then MySQL will consider function to be NON-DETERMINISTIC by default.

code of statements to be executed – We can write our program or code in this section of function that can contain conditional, looping statements, initializing and assigning the value of variables, and preparing and executing the database queries. Calls to other functions can also be included in this part of the function.

Example of MySQL Create Function

Let us write a function and try to call them bypassing the age variable instead of statically declaring and initializing in the above example –

DELIMITER $$
CREATE FUNCTION isEligible(
age INTEGER
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
IF age > 18 THEN
RETURN ("yes");
ELSE
RETURN ("No");
END IF;
END$$
DELIMITER ;

This will create a function named is eligible. We can call this function whenever we want to pass the value of the age for which you want to check eligibility. The output after copying and pasting the above statements on the MySQL command prompt terminal is as follows –

Now, we want to check the eligibility for a 20-year-old guy then we can call our function in the following way –

SELECT isEligible(20);

that results in the following output –

We can see that the output of isEligible(20) function execution returns the “Yes” as output because it is eligible to vote.

Let us check the eligibility of a 10-year-old guy by executing the following command –

SELECT is eligible(10);

that results in the following output –

Hence, our if block wasn’t executed and execution went to else block returning the value “No” as the guy was not eligible to vote.

Let us consider some other example that involves returning the number of months between the current date and the supplied date. Our function will be as follows –

DELIMITER $$
CREATE FUNCTION getMonths(sampledate date) RETURNS int DETERMINISTIC
BEGIN
DECLARE currentDate DATE;
Select current_date()into currentDate;
RETURN (12 * (YEAR(currentDate)
- YEAR(sampledate))
+ (MONTH(currentDate)
- MONTH(sampledate)));
END
$$
DELIMITER ;

The execution of the above function gives the following output –

Now, we can calculate the months from any of the given date until today by simply calling the function getMonths() in MySQL. Let’s alter the developers table and add joining_date column and update the value of joining_date column to the inauguration of company date say “2012-05-01”.

ALTER TABLE developers ADD COLUMN joining_date DATE DEFAULT "2012-05-01";

Execution of the above query gives the following output –

Let us retrieve all the records from developers table using the following query –

SELECT * FROM developers;

that gives the following output after execution –

Now, suppose Rahul, Payal and Nitin have joined the company on “2016-01-01”. We will update the records in the developers table –

UPDATE developers SET joining_date="2016-01-01" WHERE name in ("Payal","Rahul","Nitin");

that gives the following output after execution –

Let us select records by firing the same above SELECT query statement that gives us the following output –

select * from developers;

Now, we will retrieve the name of the developer and total months that he/she has worked in the company by using the following query statement in which we have to give a call to getMonths() function –

SELECT name, getMonths(joining_date) as NumberOfMonths FROM developers;

that gives the following output after execution –

We can see that Payal, Nitin, and Rahul have completed 53 months in the company while all others are joined for 97 months.

Conclusion

MySQL functions can be created by using the CREATE FUNCTION statement. We can call the functions inside the query or simply select the function value.

Recommended Articles

This is a guide to MySQL Create Function. Here we discuss the introduction, syntax, examples with code implementation respectively. You may also have a look at the following articles to learn more –

1. MySQL Split
2. MySQL Lock Table
3. MySQL Primary Key
4. MySQL Query Cache