MySQL Stored Procedure Programming

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.

Tip

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).

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 ...