Stored Procedures in MySQL with example

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;