Stored Functions, as the Stored Procedures described in the previous post, are also routines that are saved inside your database.
On a Stored Function you can write instructions to perform operations on your data.
To create a Stored Function we start by changing the default delimiter (the semicolon) so that we can use it
on our code.
The function is created with the
CREATE FUNCTION instruction as shown in the following code:
delimiter // create function my_func(qty int) returns varchar(9) begin declare stock varchar(9); if qty > 2 then set stock = 'In Stock'; else set stock = 'Low Stock'; end if; return stock; end // delimiter ;
END statements, we will put our code.
A Stored Function can be used as any built-in function defined in MySQL. That means that you will use the Stored Function in you sql queries:
select products.*, my_func(quantity) from products
When you no longer need a Stored Function you can delete it with the
DROP FUNCTION instruction.
drop function my_func;