Stored procedures can is an efficient
mechanism to provide code re usability. MySQL
Stored procedures can be used as an API which can be used from different
programming languages. Below is an example of a MySQL stored procedure, which
selects the values from a table named flowers by taking the id as argument.
DELIMITER //
CREATE PROCEDURE flower_list
(fid int)
BEGIN
select id, name from flowers where id=fid;
END //
DELIMITER ;
Here we specify a double slash (//) as
the delimiter while the procedure is being defined, so that the default delimiter
(;) in the procedure definition, can be passed through the server.
The procedure can be executed from
MySQL command prompt as well as from MySQL workbench by using the call function
along with the argument value for id. This can also be called from triggers,
other procedures and also from various application programmers. Recursive call
from the same procedure is also possible. Below is an example call to this
procedure.
call flower_list(101);
This will return the row from flowers
table where id equals 1.
Parameters in MySQL stored procedure
There can be three types of
parameters in MySQL stored procedures namely IN, OUT and INOUT.
The default parameter mode is IN. The
IN parameter requires that the procedure call must pass an input parameter. The
value of out parameter is passed back to the calling program and also its value
can be changed inside the stored procedure. The INOUT parameter specifies that
the calling program must pass the input parameter to the procedure, its value
can be changed inside the procedure and pass the value back to the calling
program.
Below is an example MySQL stored
procedure with IN and OUT parameters.
DELIMITER //
CREATE PROCEDURE flower_list
(in fid int, out fname varchar(50))
BEGIN
select name into fname from flowers where id=fid;
END //
DELIMITER ;
This MySQL stored procedure can be
executed by invoking the call function with in and out parameter as shown
below.
call flower_list(102, @fnames);
select @fnames;
