Iterative Statements

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 the condition is TRUE. If the terminating condition is not true to begin with then the WHILE loop will not execute. Statements to be executed are provided between the DO and END WHILE keywords.

REPEAT:

Another iterative processing statement that is easier to read than the LOOP statement is REPEAT. It offers the same functionality as WHILE with one difference - the terminating condition is written at the end of the loop meaning that at least one iteration is performed before the condition is checked. WHILE loops are pre-test loops because they test the condition before the statements are executed and the REPEAT loops are post-test loops as they test the condition after executing the loop statements. The termination condition is written after the UNTIL keyword.

[label:] REPEAT

statements;

UNTIL condition

END REPEAT [label]

LEAVE:

The LEAVE statement is used within the LOOP construct to terminate the execution of the loop. It can also be used to exit from a stored procedure. Basically the LEAVE statement that has a label associated with it, exits the flow control of that label. So in case of nested loops, we can break out of both loops with a single statement. The following syntax is used to exit a stored procedure:

CREATE PROCEDURE Procedure1() label:

BEGIN

statements;

IF condition THEN

LEAVE label;

END IF;

statements

END

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/55lesson.sh and wait for the MySQL prompt to start-up.