How to Create a Function in MySQL

Today’s tutorial educates about how to create a function in MySQL. It explains the syntax first and then creates a function using a sample table.

Create a Function in MySQL

A stored program to which we can pass one or multiple parameters and get a value in return is known as a function. It has a set of SQL statements to accomplish some tasks and output one value.

The following is the syntax for creating a MySQL function.

CREATE FUNCTION functionName(parameter1, parameter2, ..)
          RETURN dataType [characteristics]
          function_body

Here, the function_body comprises SQL statements to complete an operation or task. Its structure looks as given below.

BEGIN
    MySQL Statements
    RETURN Expression
END

Here is the complete syntax of creating a function.

DELIMITER ;;

CREATE FUNCTION functionName(parameter1,parameter2,…)
    RETURNS datatype
    [NOT] DETERMINISTIC

BEGIN
    MySQL Statements
    RETURN Expression
END ;;

DELIMITER ;

In the above syntax, we are doing the following things.

1. Reset the delimiter so that all the statements cannot be executed individually.
2. Write the function’s name after the CREATE FUNCTION keyword.
3. Write all the parameters inside the parentheses after the function name. We can’t specify the modifiers named INOUT, IN or OUT to parameters, but they (all parameters) are the IN parameters by default.
4. Write the valid return data type.
5. Specify if the function is using/not using a DETERMINISTIC. MySQL Server uses the NOT DETERMINISTIC if we don’t write the NOT DETERMINISTIC/DETERMINISTIC keyword.
6. Write the main code between the BEGIN and END blocks. In the function body, we need to write a minimum of one RETURN statement.
7. Finally, change the delimiter to its default value ;.

Let’s prepare a table named users with two attributes, ID and CREDIT, where the ID is a primary key. The following queries can be used to create and populate the users table.

Example Code:

# create a table
CREATE TABLE users (
      ID INT NOT NULL AUTO_INCREMENT,
      CREDIT INT NOT NULL,
      PRIMARY KEY (id)
);

# insert data
INSERT INTO users (CREDIT) VALUES (5000);
INSERT INTO users (CREDIT) VALUES (4000);
INSERT INTO users (CREDIT) VALUES (3000);
INSERT INTO users (CREDIT) VALUES(1000);

# display data
SELECT * FROM users;

Output:

+----+--------+
| ID | CREDIT |
+----+--------+
|  1 |   5000 |
|  2 |   4000 |
|  3 |   3000 |
|  4 |   1000 |
+----+--------+
4 rows in set (0.00 sec)

We write the following function to decide the userLevel depending on the CREDIT. See a code example given below.

Example Code:

DELIMITER ;;

CREATE FUNCTION UserLevels(
	credit DECIMAL(10,2)
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE userLevel VARCHAR(20);

    IF credit >= 5000 THEN
		SET userLevel = 'PLATINUM';
    ELSEIF (credit >= 4000 AND
			credit < 5000) THEN
        SET userLevel = 'GOLD';
    ELSEIF (credit > 1000 AND credit < 4000 ) THEN
        SET userLevel = 'SILVER';
    ELSE
    	SET userLevel = 'BASIC';
    END IF;

	RETURN (userLevel);
END;;
DELIMITER ;

Once the function is created, use the following query to call the function.

SELECT ID,CREDIT, UserLevels(CREDIT) AS USER_LEVEL
FROM users;

Output:

+----+--------+------------+
| ID | CREDIT | USER_LEVEL |
+----+--------+------------+
|  1 |   5000 | PLATINUM   |
|  2 |   4000 | GOLD       |
|  3 |   3000 | SILVER     |
|  4 |   1000 | BASIC      |
+----+--------+------------+
4 rows in set (0.06 sec)