MySQL Stored Procedures

Table of Contents

• What are Stored Procedures in MySQL?
• Create a Stored Procedure
• Call a Stored Procedure
• Using Input Parameters
• Using Output Parameters
• Call a Stored Procedure with Parameters
• Navigating a Sql Query
• Drop a Stored Procedure

Stored Procedures are routines that are saved inside your database. On a Stored Procedure you can write instructions to query and/or update your database.

Stored Procedures can be used when you have a set of queries or updates that you want to be able to reuse. Or you may use it for processes that could not be written on just one sql sentence, or if it would become to complex.

To create a Stored Procedure we start by changing the default delimiter (the semicolon) so that we can use it on our code.
The procedure is created with the CREATE PROCEDURE instruction as shown in the following code:

delimiter //

create procedure my_proc()
begin
    select code, name, description from products;
end //

delimiter ;

Inside the BEGIN and END statements, we will put our code.

To run our Stored Procedure we will use the CALL instruction:

call my_proc();

Stored Procedures can receive parameters.
The parameters can be of input or output types. The input parameters are identified by the IN keyword.

delimiter //

create procedure my_proc(in qty int)
begin
    select * from products where quantity < qty;
end //

delimiter ;

Output parameters are identified by the OUT keyword.

delimiter //

create procedure my_proc(in qty int, out prod_code char(4), out prod_name varchar(25), out prod_desc text)
begin
    set prod_code = (select code from products where quantity < qty limit 1);
    
    select name, description from products where quantity < qty limit 1 into prod_name, prod_desc;
end //

delimiter ;

When calling a Stored Procedure that receives parameters, we put them inside the procedure definition's parenthesis.
For Output parameters we will be sending variables.

call my_proc(30, @p_code, @p_name, @p_desc);
select @p_code, @p_name, @p_desc;

On a Store Procedure, to navigate a sql query we would need to use a CURSOR. A CURSOR allows us to read the data of the query results--one row at a time--and put it into corresponding variables.

On the following code, we are using a REPEAT .. UNTIL instruction to read the data from the cursor. Note that we have defined a variable to detect the end of the loop.

delimiter //

create procedure my_proc()
begin
    -- Set variable to exit loop
    declare done int default false;

    -- Set variables to retrieve data from query
    declare prod_code char(4);
    declare prod_price decimal(5,2);

    -- Set "cursor" variable to navigate the query
    declare prod_cursor cursor for
        select code, price from products;

    -- Set handler to detect end of loop
    declare continue handler for not found set done = true;

    -- Open cursor
    open prod_cursor;

    -- Loop to navigate the query, and perform updates
    repeat
        fetch prod_cursor into prod_code, prod_price;

        if not done then
            update products set price = prod_price + 1.25 where code = prod_code;
        end if;
    until done end repeat;

    close prod_cursor;
end //

delimiter ;

When you no longer need a Stored Procedure you can delete it with the DROP PROCEDURE instruction.

drop procedure my_proc;

Related Posts