Home arrow MySQL arrow Page 2 - Delving Deeper into MySQL 5.0

REPEAT - MySQL

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

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

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
 

REPEAT is another useful looping construct. Itís somewhat easier to use in some circumstances than LOOP , because it has a terminating condition built into its syntax, as shown here:

[label:] REPEAT
statement-block
UNTIL condition
END REPEAT [label];

The block of statements appearing between the REPEAT and UNTIL keywords is executed repeatedly until the condition indicated by UNTIL is met. Hereís an example of a stored procedure (named repeat_sum) that gets the sum of an integer and all positive integers less than itself using a REPEAT ... UNTIL loop:

 

In this procedure, we start by declaring a counter equal to 0, then, each time we go through the REPEAT loop, we increment it, adding it to the output parameter output, and then checking to see if itís yet equal to the original value. When counter and value are equal, we exit the loop. We can test it as shown here:


CAUTION
  When writing REPEAT loops, make sure that the condition following UNTIL will be met at some point. Otherwise youíll have an endless loop, which means youíll quite likely have to kill the thread and start over.

WHILE

The WHILE loop in some ways can be regarded as the inverse of a REPEAT loop. With a REPEAT loop, statements are executed until some condition is met. In a WHILE loop, execution of the statement block following the DO keyword continues only so long as the specified condition is true.

Hereís the formal syntax for WHILE:

[label:] WHILE condition DO
statement-block
END WHILE [label];

In the next example, we use a WHILE loop in a stored function to obtain the factorial of an integer:

The factorial of an integer is defined as the product of that integer and all the positive integers less than itself and greater than 1, so the factorial of 5 (often written as 5!) is 5 X 4 X 3 X 2 = 120, as you can see from our test of the factorial function above. 0! (zero factorial) is defined as 1 and the factorial of a negative number is undefined. With these additional conditions in mind, you could write a somewhat improved version of the stored function. This better_factorial function uses a CASE block to distinguish the cases where the input value is equal to or less than zero, and to act accordingly as shown here:

CREATE FUNCTION better_factorial(value INT ) RETURNS INT
BEGIN
 
DECLARE temp INT;
  DECLARE output INT DEFAULT 1;
 
SET temp = value;
 
CASE
    WHEN value < 0 THEN SET output = 0; 
    WHEN value = 0 OR value = 1 THEN SET output = 1;
    ELSE
     
WHILE temp > 1 DO
        SET output = output * temp;
        SET temp = temp - 1;
      END WHILE;
  END CASE;
 
RETURN output;
END

This example also illustrates how you can nest multiple flow-control con structs inside one another within a single stored function or stored procedure.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

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