A.4.4:
Where can I find the ANSI SQL 2003 specification for stored
procedures?

Unfortunately, the official specifications are not freely
available (ANSI makes them available for purchase). However,
there are books—such as SQL-99 Complete,
Really by Peter Gulutzan and Trudy
Pelzer—which give a comprehensive overview of the
standard, including coverage of stored procedures.

A.4.5:
How do you manage stored routines?

It is always good practice to use a clear naming scheme for your
stored routines. You can manage stored procedures with
CREATE [FUNCTION|PROCEDURE], ALTER
[FUNCTION|PROCEDURE], DROP
[FUNCTION|PROCEDURE], and SHOW CREATE
[FUNCTION|PROCEDURE]. You can obtain information about
existing stored procedures using the
ROUTINES table in the
INFORMATION_SCHEMA database (see
Section 21.17, “The INFORMATION_SCHEMA ROUTINES Table”).

A.4.6:
Is there a way to view all stored procedures and stored
functions in a given database?

Stored procedures can, but stored functions
cannot. If you perform an ordinary
SELECT inside a stored procedure,
the result set is returned directly to the client. You need to
use the MySQL 4.1 (or above) client/server protocol for this to
work. This means that—for instance—in PHP, you need
to use the mysqli extension rather than the
old mysql extension.

A.4.15:
Is WITH RECOMPILE supported for stored
procedures?

Not in MySQL 5.5.

A.4.16:
Is there a MySQL equivalent to using
mod_plsql as a gateway on Apache to talk
directly to a stored procedure in the database?

There is no equivalent in MySQL 5.5.

A.4.17:
Can I pass an array as input to a stored procedure?

Not in MySQL 5.5.

A.4.18:
Can I pass a cursor as an IN parameter to a
stored procedure?

In MySQL 5.5, cursors are available inside stored
procedures only.

A.4.19:
Can I return a cursor as an OUT parameter
from a stored procedure?

In MySQL 5.5, cursors are available inside stored
procedures only. However, if you do not open a cursor on a
SELECT, the result will be sent
directly to the client. You can also SELECT
INTO variables. See Section 13.2.9, “SELECT Syntax”.

A.4.20:
Can I print out a variable's value within a stored routine for
debugging purposes?

Yes, you can do this in a stored procedure,
but not in a stored function. If you perform an ordinary
SELECT inside a stored procedure,
the result set is returned directly to the client. You will need
to use the MySQL 4.1 (or above) client/server protocol for this
to work. This means that—for instance—in PHP, you
need to use the mysqli extension rather than
the old mysql extension.

A.4.21:
Can I commit or roll back transactions inside a stored
procedure?

Yes. However, you cannot perform transactional operations within
a stored function.

A.4.22:
Do MySQL 5.5 stored procedures and functions work
with replication?

A.4.23:
Are stored procedures and functions created on a master server
replicated to a slave?

Yes, creation of stored procedures and functions carried out
through normal DDL statements on a master server are replicated
to a slave, so the objects will exist on both servers.
ALTER and DROP statements
for stored procedures and functions are also replicated.

A.4.24:
How are actions that take place inside stored procedures and
functions replicated?

MySQL records each DML event that occurs in a stored procedure
and replicates those individual actions to a slave server. The
actual calls made to execute stored procedures are not
replicated.

Stored functions that change data are logged as function
invocations, not as the DML events that occur inside each
function.

A.4.25:
Are there special security requirements for using stored
procedures and functions together with replication?

Yes. Because a slave server has authority to execute any
statement read from a master's binary log, special security
constraints exist for using stored functions with replication.
If replication or binary logging in general (for the purpose of
point-in-time recovery) is active, then MySQL DBAs have two
security options open to them:

Any user wishing to create stored functions must be
granted the SUPER
privilege.

Nondeterministic (random) or time-based actions embedded in
stored procedures may not replicate properly. By their very
nature, randomly produced results are not predictable and cannot
be exactly reproduced, and therefore, random actions replicated
to a slave will not mirror those performed on a master.
Declaring stored functions to be
DETERMINISTIC or setting the
log_bin_trust_function_creators
system variable to 0 will not allow random-valued operations to
be invoked.

In addition, time-based actions cannot be reproduced on a slave
because the timing of such actions in a stored procedure is not
reproducible through the binary log used for replication. It
records only DML events and does not factor in timing
constraints.

Finally, nontransactional tables for which errors occur during
large DML actions (such as bulk inserts) may experience
replication issues in that a master may be partially updated
from DML activity, but no updates are done to the slave because
of the errors that occurred. A workaround is for a function's
DML actions to be carried out with the IGNORE
keyword so that updates on the master that cause errors are
ignored and updates that do not cause errors are replicated to
the slave.

A.4.27:
Do the preceding limitations affect MySQL's ability to do
point-in-time recovery?

The same limitations that affect replication do affect
point-in-time recovery.

A.4.28:
What is being done to correct the aforementioned limitations?

You can choose either statement-based replication or row-based
replication. The original replication implementation is based on
statement-based binary logging. Row-based binary logging
resolves the limitations mentioned earlier.

Mixed replication is also available (by
starting the server with
--binlog-format=mixed). This
hybrid, “smart” form of replication
“knows” whether statement-level replication can
safely be used, or row-level replication is required.