Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from 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.

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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page