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




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.


Define our cursor. This is based on a simple SELECT that will retrieve results from the employees table.


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.


Open the cursor.


The simple loop that fetches all the rows from the cursor.


Use the FETCH clause to get a single row from the cursor into our local variables.


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

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions


Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development

MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 © 2008-2020.
If you may any questions please contact us: