EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Create Function

MySQL Create Function

By Payal UdhaniPayal Udhani

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.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more