MySQL stored procedures (but not functions) can return result sets to the calling program (though not, unfortunately, directly to another stored procedure). A result set is returned from a stored procedure whenever a SQL statement that returns a result set is not associated with either an INTO clause or a cursor. We call these SQL statements unbounded. Such SQL statements will usually be SELECT statements, although other statements that return result setsSHOW, EXPLAIN, DESC, and so oncan also be included within the stored procedure.

We have used unbounded SELECT statements throughout many of our examples in order to return information about stored procedure execution. You'll most likely do the same either for debugging purposes or to return some useful status information to the user or calling program. Example 5-20 shows an example of a stored procedure that uses this feature to return a list of employees within a specific department.

Example 5-20. Using unbounded SELECTs to return data to the calling program

When run, the stored procedure from Example 5-20 produces the following output:

mysql> CALL emps_in_dept(31) // +-------------------+----------+ | department_name | location | +-------------------+----------+ | ADVANCED RESEARCH | PAYSON | +-------------------+----------+ 1 row in set (0.00 sec) +-------------+----------+-----------+ | employee_id | surname | firstname | +-------------+----------+-----------+ | 149 | EPPLING | LAUREL | | 298 | CHARRON | NEWLIN | | 447 | RAMBO | ROSWALD | | 596 | GRESSETT | STANFORD | | 745 | KANE | CARLIN | | 894 | ABELL | JAMIE | | 1043 | BROOKS | LYNN | | 1192 | WENSEL | ZENAS | | 1341 | ZANIS | ALDA | | 1490 | PUGH | ALICE | | 1639 | KUEHLER | SIZA | | 1788 | RUST | PAINE | | 1937 | BARRY | LEO | +-------------+----------+-----------+ 13 rows in set (0.00 sec)

In some respects, using stored procedures to return result sets in this way provides similar functionality to creating a view to support specific queries. Like a view, the stored procedure can encapsulate complex SQL operations, thus making it easier for a user to retrieve data without necessarily having to understand the complexities of the schema design. Encapsulating SQL inside a stored procedure can also improve security, because you can perform complex validation checks or even encryption/decryption before returning the result set.

Unlike a view, a stored procedure can return multiple result sets, as shown in Example 5-20. Returning multiple result sets can be a convenient way to encapsulate all of the logic required to produce multiple sets of application data in a single call to the database.

5.4.1. Retrieving the Result Sets in the Calling Program

It is relatively easy to retrieve a result set from a stored procedure. Provided that the stored procedure returns only a single result set, it can be handled in the same way as a normal SQL call. Example 5-21 shows a PHP program using the mysqli interface that retrieves a single result set from a stored procedure call.

Example 5-21. Retrieving a stored procedure result set from PHP

The significant lines of code from Example 5-21 include:

The output of the PHP program is shown in Figure 5-1.

Figure 5-1. Output of a PHP program that retrieves a stored procedure result set

The ability to return multiple result sets from a stored procedure can be either a blessing or a curse, depending on your perspective. The multiple result set feature can allow you to return multiple logically related sets of data in a single operation. For instance, all the result sets necessary to populate a multilevel master-detail report can be requested from the database in one operation. This could result in a greater level of separation between presentation (often web) logic and data access (database) logic.

However, handling multiple result sets may require unfamiliar processing requirements in our client-side programming. Some third-party report-generating tools may be unprepared for the possibility of multiple result sets being sent out by a single database call. In fact, some of these third-party tools may be unable to cope with a stored procedure sending out a result set at all.

Luckily, the major programming interfaces we use with MySQLPHP, Java, Perl, Python, and .NET C# and VB.NETare all capable of handling multiple result sets. In Chapters 13 through 17, we explore in detail how to process result sets and perform other operations on MySQL stored procedures in these languages. To give you a preview of the general process, Example 5-22 shows how we retrieve multiple results sets from a MySQL stored procedure in Java.

Example 5-22. Retrieving multiple result sets from a stored procedure in Java

Let's step through the important parts of Example 5-22:

5.4.2. Returning Result Sets to Another Stored Procedure

We know that we can return result sets to a calling program (such as PHP)but is there a way to return the result set to another stored procedure?

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution, and because the temporary table has scope throughout the entire sessionit creates many of the same maintainability issues raised by the use of global variables. But if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Let's look at an example. In Example 5-23, we have a stored procedure that is responsible for creating a temporary table that contains all overdue sales. Although this SQL is simple enough that we could replicate the SQL in every stored procedure that needs to process overdue orders, our performance is improved if we create this list only once during our batch run, and modularity and maintainability are improved if we define this query in only one place.

Example 5-23. Stored procedure that creates a temporary table

In Example 5-24 we see a stored procedure that calls the previous stored procedure and consumes the rows placed in the temporary table. In practice, this is pretty much equivalent to passing the result set from one stored procedure to another.

Example 5-24. Stored procedure that consumes data from a temporary table

Note that in MySQL, temporary tables have scope only within the specific session that creates the table, and they are automatically de-allocated when that session completes. So we don't have to worry about cleaning up the temporary table or be concerned that the table could be simultaneously updated by another session.