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 ;
END statements, we will put our code.
To run our Stored Procedure we will use the
Stored Procedures can receive parameters.
The parameters can be of input or output types. The input parameters are identified by the
delimiter // create procedure my_proc(in qty int) begin select * from products where quantity < qty; end // delimiter ;
Output parameters are identified by the
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
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;