Questions

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.