Using Unbounded SELECT Statements

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

CREATE PROCEDURE emps_in_dept(in_department_id INT)
BEGIN
 SELECT department_name, location
 FROM departments
 WHERE department_id=in_department_id;

 SELECT employee_id,surname,firstname
 FROM employees
 WHERE department_id=in_department_id;
END;

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

1 

Department listing

2 3 4 5 query("call department_list( )")) 24 { 25 printf(''); 26 while($row=$result_set->fetch_object( )) 27 { 28 printf(" ", 29 $row->department_id, $row->department_name); 30 } 31 } 32 else // Query failed - show error 33 { 34 printf("

Error retrieving stored procedure result set:%d (%s) %s ", 35 mysqli_errno($dbh),mysqli_sqlstate($dbh),mysqli_error($dbh)); 36 $dbh->close( ); 37 exit( ); 38 } 39 /* free result set */ 40 $result_set->close( ); 41 $dbh->close( ); 42 43 ?> 44

Department ID	Department Name
%s	%s

45 46

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

Line(s)	Explanation
23	Call the department_list stored procedure, which will return a result set containing a list of departments. The $result_set object represents the result set that is returned.
26	Iteratively call the fetch_object method, which returns an object representing a single row.
28 and 29	Extract individual columns from the $row object, by using the department_id and department_name properties, which contain the values for the corresponding columns.

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

1 private void empsInDept(Connection myConnect, int deptId) throws SQLException {
2
3 CallableStatement cStmt = myConnect
4 .prepareCall("{CALL sp_emps_in_dept(?)}");
5 cStmt.setInt(1, deptId);
6 cStmt.execute( );
7 ResultSet rs1 = cStmt.getResultSet( );
8 while (rs1.next( )) {
9 System.out.println(rs1.getString("department_name") + " "
10 + rs1.getString("location"));
11 }
12 rs1.close( );
13
14 /* process second result set */
15 if (cStmt.getMoreResults( )) {
16 ResultSet rs2 = cStmt.getResultSet( );
17 while (rs2.next( )) {
18 System.out.println(rs2.getInt(1) + " " + rs2.getString(2) + " "
19 + rs2.getString(3));
20 }
21 rs2.close( );
22 }
23 cStmt.close( );
24 }

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

Line(s)	Explanation
3	Create a CallableStatement object corresponding to the stored procedure from Example 5-20.
5	Provide the parameter (department_id) to the stored procedure.
6	Execute the stored procedure.
7	Create a ResultSet object corresponding to the first result set.
8-11	Loop through the rows in that result set and print the results to the console.
15	Use the getMoreResults method to move to the next result set.
16	Create a ResultSet object for the second result set.
17-20	Retrieve the rows from the result set and print them to the console.

 

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

CREATE PROCEDURE sp_overdue_sales ( )

BEGIN
 DROP TEMPORARY TABLE IF EXISTS overdue_sales_tmp;
 CREATE TEMPORARY TABLE overdue_sales_tmp AS
 SELECT sales_id,customer_id,sale_date,quantity,sale_value
 FROM sales
 WHERE sale_status='O';

END;

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

CREATE PROCEDURE sp_issue_invoices( )

BEGIN
 DECLARE l_sale_id INT;
 DECLARE l_last_sale INT DEFAULT 0;

 DECLARE sale_csr CURSOR FOR
 SELECT sales_id
 FROM overdue_sales_tmp;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;

 CALL sp_overdue_sales( );

 OPEN sale_csr;
 sale_loop:LOOP
 FETCH sale_csr INTO l_sale_id;
 IF l_last_sale THEN
 LEAVE sale_loop;
 END IF;
 CALL sp_issue_one_invoice(l_sale_id);
 END LOOP sale_loop;
 CLOSE sale_csr;

END;

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.