Hello Reader's ! In this blog you will learn how to create store functions using CREATE FUNCTION statement in Mysql.

In Mysql we can create user user defined functions that returns a valid value. To process and manipulate data Mysql Stored functions provide a powerful and flexible way.

Stored function example

Create Customer table in your Database

create table customer
(
customerNumber INT(11) not null auto_increment,
customerName VARCHAR(50) not null,
contactLastName VARCHAR(50) not null,
contactFirstName VARCHAR(50)not null,
phone VARCHAR(50) not null,
addressLine1 VARCHAR(50) not null,
addressLine2 VARCHAR(50) not null,
city VARCHAR(50) not null,
state VARCHAR(50) not null,
postalCode VARCHAR(15) not null,
country VARCHAR(50) not null,
salesRepEmployeeNumber INT(11) not null,
creditLimit DOUBLE not null,
created datetime not null,
modified datetime not null,
primary key(customerNumber)
);

The following example is a function that returns the level of a customer based on credit limit. We use the IF statement to decide the credit limit.

DELIMITER $$
 
CREATE FUNCTION lavel(p_creditLimit double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);
 
    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;
 RETURN (lvl);
END $$

Now, we can call the lavel() in an mySQL SELECT statement as follows:

SELECT
    customerName, lavel(creditLimit)
FROM
    customer
ORDER BY customerName;