Chapter 18. Stored Procedures and Functions


MySQL provides capabilities for defining and executing stored procedures and functions. A client sends the definition of a procedure or function to the server, which stores it for later use. Clients then invoke it whenever necessary to cause SQL operations to be performed or to produce values.

Stored procedures and functions have a great deal in common and much of the discussion here applies to both. However, to avoid many repetitions of the term "procedures and functions," the following discussion often uses "routine" to indicate material that applies to both. In cases where procedures and functions do have different properties, material that pertains only to one type of routine is indicated by one of the more specific terms "procedure" or "function."

This chapter covers the following exam topics:

  • The benefits of using stored routines

  • Differences between procedures and functions

  • The namespace for stored routines

  • Creating, altering, and dropping stored routines

  • Invoking stored routines

  • Obtaining metadata about stored routines

  • Privileges required for operations on stored routines

Stored routine syntax comprises a number of statements, and some of the examples here necessarily use statements that are not discussed until a later section. If you see a statement that has not yet been covered, just keep reading or flip ahead a few pages.

To use stored routines, you must have the access privileges that are described in Section 18.10, "Stored Routine Privileges and Execution Security." However, even with those privileges, the server by default will not let you create stored routines if binary logging is enabled except under certain conditions. This is because use of stored routines introduces certain security issues for replication. Also, routines that modify data may make it problematic to use the binary log for data recovery, particularly if you use routines that have non- deterministic behavior. The details of these issues are not covered here. For purposes of this study guide, it's assumed that if you have binary logging enabled, you have also disabled the restrictions on routine creation so that you can try the examples in this chapter. You can disable the restrictions by starting the server with the --log-bin-trust-routine-creators option at server startup, or you can set the log_bin_trust_routine_creators system variable at runtime as follows:

 mysql> SET GLOBAL log_bin_trust_routine_creators = 1; 

For details about the security and data recovery concerns regarding use of stored routines, and how best to deal with these concerns, see the MySQL Reference Manual.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net