Home arrow MySQL arrow Delving Deeper into MySQL 5.0

Delving Deeper into MySQL 5.0

MySQL 5.0 gives the developer access to features that earlier versions of MySQL do not support. These include stored procedures and stored functions. This article, the third in a series, continues our exploration of the potential of these two features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

  1. Delving Deeper into MySQL 5.0
  3. Cursors
  4. Cursor Example
By: Apress Publishing
Rating: starstarstarstarstar / 10
May 04, 2006

print this article



Looping in Procedures

One major enhancement that stored procedures and stored functions provide to you in your SQL programming is that they give you the ability to execute code in loops. MySQL 5.0 supports three looping constructs: LOOP , REPEAT , and WHILE . They are largely equivalent, and which one you use depends on both the situation you’re in and your own style or preferences. Each looping construct allows you to perform an action or set of actions a specified number of times, while a condition is true, or until a desired condition has been met. We’ll examine these and provide you with some working examples in the next three sections of this chapter.

While there is no FOR or FOREACH loop in MySQL procedures, controlling loops by means of a counter is not difficult to accomplish using any of the three looping constructs that are available, as you’ll see from some of the examples in the following sections.


The most basic looping construct of the three offered in MySQL 5.0 and newer is LOOP, which, together with a set of labels, identifies a block of SQL code that is to be repeated one or more times. The labels must appear at both the beginning and end of the loop, and must match. A beginning label must be followed with a colon ( : ) and precedes the LOOP keyword; an ending label follows the END LOOP keywords but precedes the final semicolon ( ; ) marking the end of the loop. A label can be any valid MySQL identifier.

A LEAVE statement (see the text that follows) won’t function without a label. Therefore, you should label every LOOP construct that you use in a stored procedure or stored function. Labels aren’t quite so necessary with REPEAT and WHILE (see the next two sections), but using them in longer procedures will help make your code easier to read and maintain.

The syntax for a loop with a label is shown here:

label: LOOP
END LOOP label;

Once begun, a LOOP will repeat indefinitely until something happens to cause execution to pass out of it. In order for it to be useful, MySQL provides a statement for doing just that: the LEAVE statement:

LEAVE label;

If you’re familiar with the break keyword in programming languages such as Java, Perl, C, or PHP, then you’ll find that LEAVE works in much the same fashion; it terminates the loop at the point where LEAVE is encountered.

There’s also a statement that causes execution to pass back to the beginning of a loop. This is the ITERATE statement, which, like LEAVE , also uses a label:

ITERATE label;

In this example, we create a stored function using LOOP , LEAVE , and ITERATE . This function, which we’ve named loop_example, is basically a reimplementation of the integer division ( DIV ) operator that became available in MySQL 4.1; it takes two integers as arguments, and returns the number of times that the first one will go into the second without regard to any fraction or remainder:

Here’s the loop_example function in use:

Let’s look more closely at how the LOOP construct in this function works, by repeating the code for it with some comments:

myloop:  # marks the beginning of the loop "myloop"
  SET temp = temp - start;   # perform subtraction
  SET counter = counter + 1; # increment counter
IF temp >= start # is the new value greater than the input value?
    THEN ITERATE myloop;  # if it is, go back to the "myloop" label
LEAVE myloop;  # (otherwise) break out of the loop
END LOOP myloop; # marks the end of the loop "myloop"

We could also have written this loop as

  SET temp = temp - start;
  SET counter = counter + 1;
IF temp < start
    THEN LEAVE myloop;
END LOOP myloop;

In this instance, the ITERATE wasn’t really necessary; however, in more complex loops, you may want to return to the beginning of the loop from one of several dif ferent points, depending on the circumstances; ITERATE makes this possible.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: