MySQL tutorial - 7 Function

In MySQL, a function can be declared inside the database that can be used later for operation inside the table. There are two types of function:

• Function: this function returns a certain value.
• Stored procedure: this function is identical with void function. This function doesn't have return value.

The available built-in function in MySQL can be checked here.

Create a function

This is the basic structure of creating a new function in MySQL. The deterministic_option can be filled with DETERMINISTIC or NOT DETERMINISTIC. DETERMINISTIC means that a function always return the same value for the same parameter input. However, the NOT DETERMINISTIC means that a function return different value for the same parameter input.

NOT DETERMINISTIC is a default deterministic_option in MySQL.

DELIMITER $$
CREATE FUNCTION function_name(
    parameter(s)_name data_type
)
RETURNS data_type

deterministic_option

BEGIN
    function_body
END$$
DELIMITER ;

In this example, the function called discountPrice() with the input parameter that has a FLOAT data type that represents as a price.

DELIMITER $$
CREATE FUNCTION discountPrice(
    price FLOAT
)
RETURNS FLOAT
DETERMINISTIC

BEGIN
    -- declare a variable called discountedPrice
    DECLARE discountedPrice FLOAT;
    -- give a 10% discount to the price
    SET discountedPrice = price - (price * 0.1);
    -- return the value, in this case the discountedPrice variable
    RETURN discountedPrice;
END$$
DELIMITER ;

This function can be used just like the built-in function, in this example the discountPrice() function is used.

SELECT shop.product_name, shop.price, discountPrice(shop.price) AS discounted_price FROM shop;

This is the output from the query above.

+---------------+-------+------------------+
| product_name  | price | discounted_price |
+---------------+-------+------------------+
| Mango         |  12.5 |            11.25 |
| Low Fat Milk  |   8.8 |             7.92 |
| Apple         |   7.8 |             7.02 |
| Fresh Chicken |  10.3 |             9.27 |
| Corn Flakes   |  2.99 |            2.691 |
+---------------+-------+------------------+

To see the function status that already created, use SHOW FUNCTION STATUS query.

Create a stored procedure

Stored procedure can be created using this basic structure.

DELIMITER $$

CREATE PROCEDURE procedure_name(
    IN  param_name data_type,
    OUT procedure_output data_type
)
BEGIN
    stored_procedure_body
END$$

DELIMITER ;

In this example, the stored procedure called getPriceWithTax() is created. This stored procedure calculates the product's price with tax included.

-- create a stored procedure
DELIMITER $$
CREATE PROCEDURE getPriceWithTax(
    IN productId INT,
    OUT updatedPrice FLOAT
)
BEGIN
    -- declare the tax variable with the value = 10
    DECLARE tax FLOAT DEFAULT 10;
    -- declare the price variable to store the price value
    DECLARE originPrice FLOAT DEFAULT 0;

    -- get the product's price based on the productId
    SELECT shop.price INTO originPrice FROM shop WHERE shop.id = productId;
    -- set the updatedPrice
    SET updatedPrice = originPrice + tax;
END$$
DELIMITER ;

To call the stored procedure, use the CALL query.

-- call the stored procedure
-- @updatedPrice is a variable
CALL getPriceWithTax(1,@updatedPrice);

-- retrieve the value from @updatedPrice variable
SELECT @updatedPrice;

This is the output from the query above.

+---------------+
| @updatedPrice |
+---------------+
|          22.5 |
+---------------+

Notes

• Learn more about function in MySQL here.

I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.