MySQL Function

1. What Is a FUNCTION in MySQL
2. Use CREATE FUNCTION to Create a Function in MySQL

This article introduces how to use and create a simple function in MySQL.

What Is a FUNCTION in MySQL

In MySQL programming language, a function is a set of statements that executes the set of lines and returns a single value.

When the function block gets called from outside, this set of lines performs business logic, and transformation happens to return void or any value alone.

Functions help in the manipulation of data before saving and retrieving the data. It is of various types like single-valued or more than one parameter function.

The benefits that we can take from functions include:

1. Using the same function at different places to perform some manipulations.
2. The changes at one place will suffice to see the effects at all places.
3. Functions are easy to maintain.
4. It allows code sharing and consistency all across the uses.
5. Reduces re-work of the same task and reusing the existing functions.

The function prototype or syntax in MySQL is below.

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) RETURNS return_datatype
BEGIN
Declaration_section
Executable_section
END;

Let’s take more insights and details about the above syntax.

1. The syntax starts with the CREATE FUNCTION statement; it indicates MySQL internally, a new method needs to get created.
2. Following the function keyword, function_name is the specific name saying the block of lines is the alias for the provided statement. When a function name is given, the user tries executing the given set to perform the required function.
3. Next to the function name, there can be an n number of parameters passed to the function as the arguments. The parameter list comprises two parts, variable name and variable datatype.
   3.1. Variable names can be used in function manipulation logic. The set of statements where transformation happens uses the arguments variables to work on the data received.
   3.2 Datatype declaration after variable name restricts the use to pass defined argument. The datatype acts as primary validation to validate the parameter that gets passed.
4. The RETURNS keyword specifies the next value is the single return value of the function.
5. Return_datatype is the datatype of the returning value from the function. It also includes the size of the datatype, for example, VARCHAR(20).
6. BEGIN keywords specify internally that the lines next to the keyword are the starting point for executing the next set of lines. It shows that this is the beginning of the functional block.
7. Declaration_section is the place or the top of the functional block where all variables get declared.
8. Executable_section is the block where actual code lines are present for manipulation. The lines are exact lines that do business transformation logic.
9. END; is the terminal keyword specifying the end of the block. The keyword is in conjunction with the BEGIN statement. This statement states the start and end of the block.

Use CREATE FUNCTION to Create a Function in MySQL

Prerequisites to create a function is:

1. Have a proper MySQL connection and keep the server up.
2. Create a database and use that database where the function needs to get created (Ex: myFirstDb).
3. Create a table beforehand where the function can get called (Ex: stu_name).
4. Make some entries in the table where the function works and respond.

CREATE TABLE `stu` (
  `id` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL
);

INSERT INTO `myFirstDb`.`stu`(`id`,`name`,`dob`) VALUES ('111','Test',DATE('1970-01-08'));

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_stu_name`(stu_id VARCHAR(50)) RETURNS varchar(50) CHARSET utf8mb4
BEGIN
declare stu_name varchar(50) ;
select name into stu_name from myFirstDb.stu where id = stu_id;
RETURN stu_name;
END$$
DELIMITER ;

The following function call happens when we use the function name with the SELECT keyword. The calling statement looks like below.

Select get_stu_name('111');

The calling statement passes argument 1 as student id and executes the function. For the function name to get successfully run, a student table must exist with id as one of the attributes.

When the record is present, the function returns the student name corresponding to the student id as one.