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
Functions return a value; procedures return values via
Functions may be called in a query just like MySQL functions or user-defined functions; procedures are called independently via the
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 sitecount( );
MySQL supports three kinds of stored procedure parameters:
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
An output value is stored in the parameter for use by the caller of the stored procedure.
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.