MySQL - CREATE FUNCTION Statement

---

---

MySQL CREATE FUNCTION Statement

A function is a block of organized, reusable code that is used to perform a single, related action. Functions provide better modularity for your application and a high degree of code reusing.

MySQL provides a set of built-in function which performs particular tasks for example the CURDATE() function returns the current date.

You can create a stored function using the CREATE FUNCTION statement.

Syntax

Following is the syntax the CREATE FUNCTION statement −

CREATE FUNCTION function_Name(input_arguments) RETURNS output_parameter

Where, function_name is the name of the function you need to create, input_arguments are the input values of the function and output_parameter is the return value of the function.

Example 1

Suppose we have created a table named Emp in the database using the CREATE statement as shown below −

CREATE TABLE Emp(
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

And we have inserted three records in the Emp table as −

INSERT INTO Emp VALUES
('Amit', DATE('1970-01-08'), 'Hyderabad'),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam'),
('Sudha', DATE('1980-11-06'), 'Vijayawada');

Following query creates a function named getDob() which accepts the name of the employee, retrieves and returns the value of DOB column.

DELIMITER //
CREATE FUNCTION test.getDob(emp_name VARCHAR(50))
   RETURNS DATE
   DETERMINISTIC
   BEGIN
      declare dateOfBirth DATE;
      select DOB into dateOfBirth from test.emp where 
	  Name = emp_name; MySQL CREATE FUNCTION Statement
         return dateOfBirth;
   END//
DELIMITER ;

If you call the function you can get date of birth of an employee as shown below −

SELECT getDob('Amit');

Output

The above query produces the following output −

getDob('Amit')
1970-01-08

Example 2

Assume we have created another table as shown below −

CREATE TABLE student (
   Name VARCHAR(100), 
   Math INT, 
   English INT, 
   Science INT, 
   History INT
);

Now, let us insert few records into student table −

INSERT INTO student values
('Raman', 95, 89, 85, 81),
('Rahul' , 90, 87, 86, 81),
('Mohit', 90, 85, 86, 81),
('Saurabh', NULL, NULL, NULL, NULL );

Following query creates a function with name tbl_Update −

DELIMITER //
Create Function tbl_Update(S_name Varchar(50), M1 INT, 
   M2 INT, M3 INT, M4 INT)
   RETURNS INT
   DETERMINISTIC
   BEGIN
   UPDATE student SET Math = M1, English = M2, 
   Science = M3, History = M4 WHERE Name = S_name;
   RETURN 1;
   END //
DELIMITER ;

You can call the above created function as shown below −

Select tbl_update('Saurabh',85,69,75,82);

Output

Following is the output of the above query −

tbl_update('Saurabh',85,69,75,82)
1

If you get the records of the table student using the select statement you can observe the modified record −

SELECT * from student;

Output

The above mysql query produces the following output −

Name	Math	English	Science	History
Raman	95	89	85	81
Rahul	90	87	86	81
Mohit	90	85	86	81
Saurabh	85	69	75	82