19.2. Stored Procedure Syntax
A stored routine is either a procedure or a function. Stored
routines are created with CREATE PROCEDURE and
CREATE FUNCTION statements. A procedure is
invoked using a CALL statement, and can only
pass back values using output variables. A function can be called
from inside a statement just like any other function (that is, by
invoking the function's name), and can return a scalar value.
Stored routines may call other stored routines.
A stored procedure or function is associated with a particular
database. This has several implications:
When the routine is invoked, an implicit USE
db_name is performed (and
undone when the routine terminates). USE
statements within stored routines are disallowed.
You can qualify routine names with the database name. This can
be used to refer to a routine that is not in the current
database. For example, to invoke a stored procedure
p or function f that is
associated with the test database, you can
say CALL test.p() or
test.f().
When a database is dropped, all stored routines associated
with it are dropped as well.
MySQL supports the very useful extension that allows the use of
regular SELECT statements (that is, without
using cursors or local variables) inside a stored procedure. The
result set of such a query is simply sent directly to the client.
Multiple SELECT statements generate multiple
result sets, so the client must use a MySQL client library that
supports multiple result sets. This means the client must use a
client library from a version of MySQL at least as recent as 4.1.
The client should also specify the
CLIENT_MULTI_STATEMENTS option when it
connects. For C programs, this can be done with the
mysql_real_connect() C API function (see
Section 25.2.3.51, “mysql_real_connect()”).
The following sections describe the syntax used to create, alter,
drop, and invoke stored procedures and functions.
