MySQL Pocket Reference, 2nd Edition

Stored Procedures and Functions

Stored routines are encapsulated SQL components that are stored in the database for reuse in your database applications. MySQL supports two kinds of stored routines: procedures and functions. They behave very similarly except for three key differences:

• Functions accept only IN parameters; procedures can accept IN, OUT, and INOUT parameters.

• Functions return a value; procedures return values via OUT or INOUT parameters.

• Functions may be called in a query just like MySQL functions or user-defined functions; procedures are called independently via the CALL command.

The CREATE PROCEDURE/CREATE FUNCTION command creates a stored procedure. You must have CREATE ROUTINE privileges in order to create any stored procedure in MySQL. You must define a name and a body for the procedure:

CREATE PROCEDURE sitecount( )  SELECT COUNT(*) FROM web_site;

You may subsequently call the procedure using the CALL command:

CALL sitecount(  );

Parameters

MySQL supports three kinds of stored procedure parameters:

IN

The parameter is passed into the procedure as input. The procedure can then operate on the parameter values. By default, a parameter is an IN parameter. Stored functions can accept only IN parameters.

OUT

An output value is stored in the parameter for use by the caller of the stored procedure.

INOUT

The caller passes into the procedure a value for the INOUT parameter and any changes made by the procedure then become available to the caller after the procedure is executed.

Parameters ...