Interacting with the Database

Most stored programs involve some kind of interaction with database tables. There are four main types of interactions:

• Store the results of a SQL statement that returns a single row into local variables.
• Create a "cursor" that allows the stored program to iterate through the rows returned by a SQL statement.
• Execute a SQL statement, returning the result set(s) to the calling program.
• Embed a SQL statement that does not return a result set, such as INSERT, UPDATE, DELETE, etc.

The following sections look briefly at each type of interaction.

To run the examples in this section of the chapter, you should install the book's sample database, available at this book's web site (see the Preface for details).

 

2.8.1. SELECTing INTO Local Variables

Use the SELECT INTO syntax when you are querying information from a single row of data (whether retrieved from a single row, an aggregate of many rows, or a join of multiple tables). In this case, you include an INTO clause "inside" the SELECT statement that tells MySQL where to put the data retrieved by the query.

Figure 2-10 shows a stored procedure that obtains and then displays the total sales for the specified customer ID. Figure 2-6 executes the procedure.

Figure 2-10. A stored procedure with an embedded SELECT INTO statement

 

Example 2-6. Executing a stored procedure that includes a SELECT INTO statement

mysql> CALL customer_sales(2) $$
+--------------------------------------------------------------+
| CONCAT('Total sales for ',in_customer_id,' is ',total_sales) |
+--------------------------------------------------------------+
| Total sales for 2 is 7632237 |
+--------------------------------------------------------------+
1 row in set (18.29 sec)

Query OK, 0 rows affected (18.29 sec)

2.8.2. Using Cursors

SELECT INTO is fine for single-row queries, but many applications require the querying of multiple rows of data. You will use a cursor in MySQL to accomplish this. A cursor lets you fetch one or more rows from a SQL result set into stored program variables, usually with the intention of performing some row-by-row processing on the result set.

The stored procedure in Figure 2-11 uses a cursor to fetch all rows from the employees table.

Here is an explanation of the significant lines in this procedure:

Figure 2-11. Using a cursor in a stored procedure

Line(s)	Explanation
8-12	Declare local variables. The first three are created in order to receive the results of our SELECT statement. The fourth (done) lets us know when all the rows have been retrieved from the result set.
14-16	Define our cursor. This is based on a simple SELECT that will retrieve results from the employees table.
18	Declare a "handler" that defines the actions we will take when no more rows can be retrieved from a SELECT statement. Handlers can be used to catch all kinds of errors, but a simple handler like this is always needed to alert us that no more rows can be retrieved from a result set.
20	Open the cursor.
21-26	The simple loop that fetches all the rows from the cursor.
22	Use the FETCH clause to get a single row from the cursor into our local variables.
23-25	Check the value of the done variable. If it is set to 1, then we have fetched beyond the last row within the cursor, so we execute the LEAVE statement to terminate the loop.

 

2.8.3. Returning Result Sets from Stored Procedures

An unbounded SELECT statementone not associated with an INTO clause or a cursorreturns its result set to the calling program. We have used this form of interaction between a stored procedure and the database quite a few times already in this book, using simple SELECTs to return some kind of status or result from a stored procedure. So far, we've used only single-row result sets, but we could equally include a complex SQL statement that returns multiple rows within the stored procedure.

If we execute such a stored procedure from the MySQL command line, the results are returned to us in the same way as if we executed a SELECT or SHOW statement. Figure 2-12 shows a stored procedure that contains such an unbounded SELECT statement.

Figure 2-12. An unbounded SELECT statement in a stored procedure

If we execute the stored procedure and supply an appropriate value for the input parameter, the results of the SELECT within the stored procedure are returned. In Figure 2-13 we see the results of the SELECT statement being returned from the stored procedure call from within the MySQL Query Browser.

Figure 2-13. Results returned from a stored procedure that has an unbounded SELECT

Note that a stored program call can return more than one result set. This creates special challenges for the calling program, which we discussfor each specific programming languagein Chapters 13 through 17.

2.8.4. Embedding Non-SELECTs

"Simple" SQL statements that do not return results can also be embedded in your stored programs. These statements include DML statements such as UPDATE, INSERT, and DELETE and may also include certain DDL statements such as CREATE TABLE. Some statementsspecifically those that create or manipulate stored programsare not allowed; these are outlined in Chapter 5.

Figure 2-14 shows a stored procedure that includes an update operation. The UPDATE statement is enclosed in some validation logic that prevents the update from proceeding if the input values are invalid.

Figure 2-14. Stored procedure with an embedded UPDATE