EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Create Function
 

MySQL Create Function

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated May 29, 2023

MySQL Create Function

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 –

MySQL Create Function 1

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 –

MySQL Create Function 2

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 –

MySQL Create Function 3

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 –

MySQL Create Function 4

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 –

MySQL Create Function 5

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

SELECT * FROM developers;

that provides the following result after execution –

output 1

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 –

output 2

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

select * from developers;

output 3

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 –

output 4

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW