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
voidfunction. 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 DETERMINISTICis a defaultdeterministic_optionin 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 STATUSquery.
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.
Top comments (0)