MySQL 5.0 gives the developer access to features that earlier versions of MySQL do not support. These include stored procedures and stored functions. This article, the second in a series, begins exploring the potential of these two features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).
It’s difficult to say which is the greater source of excitement surrounding MySQL 5.0—the arrival of stored procedures in MySQL, or the appearance of views. In this section, we’ll discuss both of these features in detail. We’ll define and explain the concepts behind both stored procedures and views, and show you some working examples of each. (Of course, when we say “working,” we mean that these examples will work if you try them on an installation of MySQL 5.0.1 or newer.) We’ll also talk about how these features will help to improve your MySQL applications in terms of both efficiency and security.
Stored Procedures and Stored Functions
A database stored procedure or stored function acts much like a function or method does in programming languages such as PHP, C, Java, or Perl: It’s a way to group together a series of statements and to identify this grouping so that we can later refer to it by name instead of repeating all the statements again. The following is a comparison of stored procedures and stored functions in MySQL 5.0:
In MySQL 5.0.0 and 5.0.1, a stored procedure could refer to tables, but a stored function could not do so. This restriction is expected to be lifted in later 5.X releases, so you’ll want to be sure to keep abreast of new developments in this area.
A stored function actually returns a value, whereas the output of a procedure must be assigned to a user variable. Because of this, stored functions don’t have output parameters, only input parameters.
Both stored procedures and stored functions may use existing MySQL functions as well as other stored procedures and stored functions.
It’s very important not to confuse stored functions with the user-defined functions (UDFs) that have been supported in MySQL since version 3.23.XX. (This is why we refer to them here as “stored functions” rather than simply “functions.”) A UDF must be written in C, then compiled as a shared object file and installed on the same system where the MySQL server is being run. A stored function is written in SQL as part of a CREATE FUNCTION statement, just as a stored procedure is written using SQL within a CREATE PROCEDURE statement.
CAUTION UDFs continue to be supported in MySQL 5.0, and stored functions and UDFs share the same namespace. This means that if you’re already using a UDF with a given name, you may encounter problems if you try to create a stored function with the same name that acts within the same database.
It’s very possible that MySQL will be extended in some fashion to allow stored procedures and stored functions to be written in other languages, but in any case no compilation is necessary to use stored procedures or stored functions, nor do we expect it to become this way in the future.
Stored procedures and stored functions allow you to execute queries and other blocks of SQL code that are frequently repeated much more quickly because they’re precompiled. This means that MySQL doesn’t have to try to optimize them again—they’ve already been stored in optimized form.
Queries made by means of stored procedures are very portable between applications written in different languages or running on different platforms. Minimal application logic is required to execute a stored procedure (as we’ll see shortly), and the same is true of tasks like string manipulation in order to insert limiting values into queries and other SQL code—instead, we can pass those values as parameters to the stored procedure or stored function.
Stored procedures and stored functions allow us to encapsulate and modularize our queries—that is, just as we can do with functions, objects, and methods in programming languages like C, Java, PHP, and so on, we can treat stored procedures as “black box” entities with input and output parameters that are known to us, but whose internals needn’t concern us when we use them, so long as they perform as specified. This keeps application code cleaner and easier to maintain because we’re no longer mixing it with SQL. It also makes it easier to construct larger, more complex queries using the results of smaller and simpler ones, because you can call an existing stored procedure or stored function that supplies some of the data or functionality that you require in the new stored procedure or stored function that you’re currently writing.
Because database internals such as database and table names, passwords, and so forth aren’t readily visible to the application or the end user, using stored procedures is more secure. Applications have access only to the functionality they require to accomplish their tasks; there’s no direct access to stored data, only to procedures that return just what’s required. By means of stored procedures, we can allow application writers to manipulate data without giving them direct access to the data itself. (As we’ll see a bit later in this chapter, even in cases where it is desirable to give programmers and other users access to some but not all data, you’ll be able in MySQL 5.0 to employ views to accomplish this.) Banking and other financial entities make heavy use of stored procedures (and views) for these reasons.