Updated May 29, 2023
Introduction to MySQL Create Function
We execute certain business logic repetitively when we need to retrieve the value from the written business logic involving single or multiple database operations. This repetition occurs in many different places. 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 utilize the CREATE FUNCTION statement to create a new function that stores the function definition. You can call this function by providing any parameters that will return the desired 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 – The function’s name needs to be created in MySQL.
- parameter1, parameter2,… – We can pass the optional parameters to the functions that must be declared while creating it in the () brackets. A process can contain none, one, or more than one parameter. These parameters can belong to either of the three types –
- IN – You assign values to these parameters when you call the function, and the function can only reference and use these values. You cannot modify or overwrite them within the function.
- OUT – You can assign values to these parameters and override them within the function, but you cannot reference them.
- IN OUT – You assign values to these parameters while calling the function, and you can modify or overwrite them inside the function. The function can then reference and use these modified values.
- BEGIN and END – BEGIN keyword marks the beginning of the function, while END marks the completion of the function in MYSQL.
- RETURN Datatype – We can return any 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 executing the function, the execution of the function is terminated, and the value is returned.
- DETERMINISTIC – The function can be either deterministic or nondeterministic, which must be specified here. We consider a function deterministic when it returns the same value for the same parameter values. However, if the function returns a different value for the same values of functions, then we can call that function to be nondeterministic. By default, MySQL considers the function NONDETERMINISTIC when none of the function types is mentioned.
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. This part of the function can also include calls to other functions.
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 age value you wish 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, if the block wasn’t executed and execution went to the else block, returning the value “No” as the guy was not eligible to vote.
Let us consider another example that involves returning the months between the current and supplied dates. 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 the date until today by simply calling the function getMonths() in MySQL. Let’s alter the developer’s table, add the joining_date column, and update the value of the joining_date column to the inauguration of the 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 the developers’ table using the following query –
SELECT * FROM developers;that provides the following result after execution –
Suppose Rahul, Payal, and Nitin joined the company on “2016-01-01”. We will update the records in the developer’s 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 the total months that they have worked in the company by using the following query statement in which we have to give a call to the getMonths() function –
SELECT name, getMonths(joining_date) as NumberOfMonths FROM developers;that provides the following output after execution –
We can observe that Payal, Nitin, and Rahul have completed 53 months in the company, while all others have been with the company for 97 months.
Conclusion
MySQL functions can be created by using the CREATE FUNCTION statement. We can call the functions inside the query or select the function value.
Recommended Articles
We hope that this EDUCBA information on “MySQL Create Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
