DEVELOPER ZONE ::
Login / Register
Questions
28.4.1: Does MySQL 5.1 support stored procedures?
28.4.2: Where can I find documentation for MySQL stored procedures and stored functions?
28.4.3: Is there a discussion forum for MySQL stored procedures?
28.4.4: Where can I find the ANSI SQL 2003 specification for stored procedures?
28.4.5: How do you manage stored routines?
28.4.6: Is there a way to view all stored procedures and stored functions in a given database?
28.4.7: Where are stored procedures stored?
28.4.8: Is it possible to group stored procedures or stored functions into packages?
28.4.9: Can a stored procedure call another stored procedure?
28.4.10: Can a stored procedure call a trigger?
28.4.11: Can a stored procedure access tables?
28.4.12: Do stored procedures have a statement for raising application errors?
28.4.13: Do stored procedures provide exception handling?
28.4.14: Can MySQL 5.1 stored routines return result sets?
28.4.15: Is WITH RECOMPILE
supported for stored procedures?
28.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?
28.4.17: Can I pass an array as input to a stored procedure?
28.4.18: Can I pass a cursor as an IN
parameter to a stored procedure?
28.4.19: Can I return a cursor as an OUT
parameter from a stored procedure?
28.4.20: Can I print out a variable's value within a stored routine for debugging purposes?
28.4.21: Can I commit or roll back transactions inside a stored procedure?
Questions and Answers
28.4.1: Does MySQL 5.1 support stored procedures?
Yes. MySQL 5.1 supports two types of stored routines — stored procedures and stored functions.
28.4.2: Where can I find documentation for MySQL stored procedures and stored functions?
See Chapter 18, Stored Procedures and Functions.
28.4.3: Is there a discussion forum for MySQL stored procedures?
Yes. See http://forums.mysql.com/list.php?98.
28.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.
28.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 22.14, “The INFORMATION_SCHEMA ROUTINES
Table”).
28.4.6: 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 22.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 13.5.4.8, “SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
Syntax”, for more information.
28.4.7: 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 13.5.4.8, “SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
Syntax”, for more information about these statements.
You can also query the ROUTINES
table in the INFORMATION_SCHEMA
database — see Section 22.14, “The INFORMATION_SCHEMA ROUTINES
Table”, for information about this table.
28.4.8: Is it possible to group stored procedures or stored functions into packages?
No. This is not supported in MySQL 5.1.
28.4.9: Can a stored procedure call another stored procedure?
Yes.
28.4.10: Can a stored procedure call a trigger?
A stored procedure can execute an SQL statement, such as an UPDATE
, that causes a trigger to fire.
28.4.11: Can a stored procedure access tables?
Yes. A stored procedure can access one or more tables as required.
28.4.12: Do stored procedures have a statement for raising application errors?
Not in MySQL 5.1. We intend to implement the SQL standard SIGNAL
and RESIGNAL
statements in a future MySQL release.
28.4.13: Do stored procedures provide exception handling?
MySQL implements HANDLER
definitions according to the SQL standard. See Section 18.2.8.2, “DECLARE
Handlers”, for details.
28.4.14: 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.
28.4.15: Is WITH RECOMPILE
supported for stored procedures?
Not in MySQL 5.1.
28.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.1.
28.4.17: Can I pass an array as input to a stored procedure?
Not in MySQL 5.1.
28.4.18: Can I pass a cursor as an IN
parameter to a stored procedure?
In MySQL 5.1, cursors are available inside stored procedures only.
28.4.19: 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 13.2.7, “SELECT
Syntax”.
28.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.
28.4.21: 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.