JIYIK CN >

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

函数What is in MySQL

In the MySQL programming language, 函数a set of statements that executes a set of rows and returns a value.

When the function block is called externally, this set of code executes the business logic and the conversion happens to return void or any single value.

Function helps to process the data before saving and retrieving it. It is of various types like single value or multiple parameter functions.

The benefits we can get from functions include:

1. Use the same function in different places to do some operations.
2. One change, and the effect is seen everywhere.
3. Functionality is easy to maintain.
4. It allows code sharing and consistency across all uses.
5. Reduce rework of identical tasks and reuse existing functionality.

The function prototype or syntax in MySQL is as follows.

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

Let's have some more insights and details on the above syntax.

1. The syntax starts with CREATE FUNCTIONa statement; it represents MySQL internally and is required to create a new method.
2. After the function keyword, function_namecomes a specific name indicating that the line block is an alias for the provided statement. When a function name is given, the user attempts to execute the given set to perform the desired function.
3. Next to the function name, there can be nparameters passed as arguments to the function. The parameter list consists of two parts: the variable name and the variable data type.
   3.1. The variable name can be used in the function operation logic. The set of statements where the conversion occurs uses the parameter variable to process the received data.
   3.2 The data type declaration after the variable name restricts the use of the defined parameters. The data type serves as the main validation to verify the passed parameters.
4. RETURNSThe keyword specifies that the next value is the single return value of the function.
5. Return_datatypeIs the data type of the function's return value. It also includes the size of the data type, VARCHAR(20)e.g.
6. BEGINKeywords internally specify that the line next to the keyword is the starting point for the next set of lines to be executed. It indicates that this is the beginning of a function block.
7. Declaration_sectionis the location or top of the function block where all variables are declared.
8. Executable_sectionBlocks are the actual lines of code used for the operation. These are the exact lines that execute the business transformation logic.
9. END;Is a terminal keyword that specifies the end of a block. This keyword BEGINis used in conjunction with the statement. The statement describes the beginning and end of a block.

CREATE FUNCTIONCreate a function in MySQL using

The prerequisites for creating a function are:

1. Have a proper MySQL connection and keep the server running.
2. Create a database and use it wherever you need to create functions (for example: myFirstDb).
3. Pre-create a table where you can call the function (for example: stu_name).
4. Enter some entries in the table where the function works and responds.

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 ;

When we use SELECTthe function name with the keyword, the following function call occurs. The calling statement is shown below.

Select get_stu_name('111');

The call statement 1passes the parameter as studentID and executes the function. To successfully run the function name, there must be a student table with idas one of the attributes.

When the record exists, the function returns the student name corresponding to the student ID as 1.