Questions
23.4.1: How do you manage stored routines?
23.4.2: Does MySQL 5.1 support stored procedures and functions?
23.4.3: Can I print out a variable's value within a stored routine for debugging purposes?
23.4.4: Can a stored procedure call a trigger?
23.4.5: Where can I find documentation for MySQL stored procedures and stored functions?
23.4.6:
Can I return a cursor as an OUT
parameter
from a stored procedure?
23.4.7: How are actions that take place inside stored procedures and functions replicated?
23.4.8:
Is there a MySQL equivalent to using
mod_plsql
as a gateway on Apache to talk
directly to a stored procedure in the database?
23.4.9:
Is WITH RECOMPILE
supported for stored
procedures?
23.4.10: Are there special security requirements for using stored procedures and functions together with replication?
23.4.11: What limitations exist for replicating stored procedure and function actions?
23.4.12: Is it possible to group stored procedures or stored functions into packages?
23.4.13: Can MySQL 5.1 stored routines return result sets?
23.4.14: Where can I find the ANSI SQL 2003 specification for stored procedures?
23.4.15: Is there a way to view all stored procedures and stored functions in a given database?
23.4.16: Do MySQL 5.1 stored procedures and functions work with replication?
23.4.17: What is being done to correct the aforementioned limitations?
23.4.18: Can I pass an array as input to a stored procedure?
23.4.19: Can a stored procedure access tables?
23.4.20: Are stored procedures and functions created on a master server replicated to a slave?
23.4.21:
Can I pass a cursor as an IN
parameter to a
stored procedure?
23.4.22: Can a stored procedure call another stored procedure?
23.4.23: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?
23.4.24: Do stored procedures have a statement for raising application errors?
23.4.25: Is there a discussion forum for MySQL stored procedures?
23.4.26: Do stored procedures provide exception handling?
23.4.27: Where are stored procedures stored?
23.4.28: Can I commit or roll back transactions inside a stored procedure?
Questions and Answers
23.4.1: 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 20.14, “The INFORMATION_SCHEMA ROUTINES
Table”).
23.4.2: Does MySQL 5.1 support stored procedures and functions?
Yes. MySQL 5.1 supports two types of stored routines — stored procedures and stored functions.
23.4.3: 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.
23.4.4: Can a stored procedure call a trigger?
A stored procedure can execute an SQL statement, such as an
UPDATE
, that causes a trigger to
activate.
23.4.5: Where can I find documentation for MySQL stored procedures and stored functions?
See Section 19.2, “Using Stored Routines (Procedures and Functions)”.
23.4.6:
Can I return a cursor as an OUT
parameter
from a stored procedure?
In MySQL 5.1, 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 12.2.8, “SELECT
Syntax”.
23.4.7: 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.
23.4.8:
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.1.
23.4.9:
Is WITH RECOMPILE
supported for stored
procedures?
Not in MySQL 5.1.
23.4.10: 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.
Alternatively, a DBA can set the
log_bin_trust_function_creators
system variable to 1, which enables anyone with the
standard CREATE ROUTINE
privilege to create stored functions.
23.4.11: What limitations exist for replicating stored procedure and function actions?
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. Note
that 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.
23.4.12: Is it possible to group stored procedures or stored functions into packages?
No. This is not supported in MySQL 5.1.
23.4.13: Can MySQL 5.1 stored routines return result sets?
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.
23.4.14: 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.
23.4.15: Is there a way to view all stored procedures and stored functions in a given database?
Yes. For a database named dbname
, use
this query on the
INFORMATION_SCHEMA.ROUTINES
table:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='dbname
';
For more information, see Section 20.14, “The INFORMATION_SCHEMA ROUTINES
Table”.
The body of a stored routine can be viewed using
SHOW CREATE FUNCTION
(for a
stored function) or SHOW CREATE
PROCEDURE
(for a stored procedure). See
Section 12.4.5.11, “SHOW CREATE PROCEDURE
Syntax”, for more information.
23.4.16: Do MySQL 5.1 stored procedures and functions work with replication?
Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 19.6, “Binary Logging of Stored Programs”.
23.4.17: What is being done to correct the aforementioned limitations?
As of MySQL 5.1.5, 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.
Beginning with MySQL 5.1.8, 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.
For additional information, see Section 16.1.2, “Replication Formats”.
23.4.18: Can I pass an array as input to a stored procedure?
Not in MySQL 5.1.
23.4.19: Can a stored procedure access tables?
Yes. A stored procedure can access one or more tables as required.
23.4.20: 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.
23.4.21:
Can I pass a cursor as an IN
parameter to a
stored procedure?
In MySQL 5.1, cursors are available inside stored procedures only.
23.4.22: Can a stored procedure call another stored procedure?
Yes.
23.4.23: 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.
23.4.24: Do stored procedures have a statement for raising application errors?
Not in MySQL 5.1. The SQL standard
SIGNAL
and RESIGNAL
statements are implemented in MySQL 5.5.
23.4.25: Is there a discussion forum for MySQL stored procedures?
Yes. See http://forums.mysql.com/list.php?98.
23.4.26: Do stored procedures provide exception handling?
MySQL implements HANDLER
definitions according to the SQL standard. See
Section 12.7.4.2, “DECLARE
for Handlers”, for details.
23.4.27: Where are stored procedures stored?
In the proc
table of the
mysql
system database. However, you should
not access the tables in the system database directly. Instead,
use SHOW CREATE FUNCTION
to
obtain information about stored functions, and
SHOW CREATE PROCEDURE
to obtain
information about stored procedures. See
Section 12.4.5.11, “SHOW CREATE PROCEDURE
Syntax”, for more information
about these statements.
You can also query the ROUTINES
table in the INFORMATION_SCHEMA
database
— see Section 20.14, “The INFORMATION_SCHEMA ROUTINES
Table”, for information
about this table.
23.4.28: Can I commit or roll back transactions inside a stored procedure?
Yes. However, you cannot perform transactional operations within a stored function.
User Comments
Add your own comment.