...

/

Iterative Statements

Iterative Statements

This lesson discusses the various statements supported by MySQL for repeated execution of commands. These are LOOP, WHILE, and REPEAT as well as ITERATE and LEAVE.

Iterative Statements

Iterative processing allows repeated execution of a set of statements. This is an important feature in database programming because we may need to loop through the rows returned by a query.

LOOP:

The most basic iterative statement is the LOOP statement. Any statements between the LOOP and END LOOP keywords are repeated until a condition for termination is met. The LEAVE statement is used to break the iterative processing.

[label]: LOOP

statements;

IF condition THEN

LEAVE [label];

END IF;

END LOOP [label];

The LOOP statement can start with an optional label which is used to refer to the loop. The LEAVE statement is used to break the execution. The ITERATE statement is used to ignore processing and start a new iteration of the loop.

WHILE:

WHILE statement provides an alternate way of iterative processing. It is better to understand because the terminating condition is clearly written between the WHILE and DO keywords as opposed to somewhere inside the LOOP. The WHILE statement is functionally similar to the LOOP- LEAVE- END LOOP construct as the terminating condition is checked before the execution of the loop begins. The syntax of while statement is:

[label] WHILE condition DO

statements;

END WHILE [label]

The terminating condition is checked at the beginning of each iteration and if TRUE then the statements are executed. The process continues as long as ...