MySQL tutorial - 7 Function
Tags: mysql, beginners, tutorial
Published: 2021-03-10 00:17:12

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](https://www.w3schools.com/sql/sql_ref_mysql.asp).

## 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.

```sql
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.

```sql
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.

```sql
-- 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.

```sql
-- 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](https://www.mysqltutorial.org/mysql-stored-function/).

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