MySQL Stored Functions

Table of Contents

• What are Stored Functions in MySQL?
• Create a Stored Function
• Use a Stored Function
• Drop a Stored Function

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 ;

Inside the BEGIN and 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;

Related Posts