O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

A stored procedure with an embedded SELECT INTO statement
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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required