IF,
LOOP, ITERATE, LEAVE, REPEAT/UNTIL and WHILE/DO.
The
IF statement:
What is it?
It is a statement; IF also exists as a function, and we should not confuse them.
Syntax
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
How it works
IF implements a basic conditional construct. If search_condition evaluates to true, the corresponding "THEN" SQL command is executed. If no search_condition matches, it jumps to the ELSEIF/ELSE sequence if present.
Example
We will create a small procedure. The following example uses the IF statement to check if a number is a multiple of 100.
delimiter //
create procedure select_arbitre(in n int)
begin
if (mod(n,100) = 0) then
select n/100;
end if;
end //
The
CASE statement:
What is it?
The CASE command for stored procedures implements a complex conditional construct.
SyntaxCASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASECASEWHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASEHow it works
If a search_condition evaluates to true, the corresponding SQL command is executed. If no search condition matches, the command in the ELSE clause is executed.
Note: The syntax of a CASE command shown here for use within stored procedures differs slightly from the CASE expression. The CASE command cannot have an ELSE NULL clause and ends with END CASE instead of END.
The LOOP statement:
What is it?
LOOP implements a simple loop construct that allows repeated execution of particular commands.
Syntax
[begin_label:] LOOP
statment_list
END LOOP [end_label]
How it works
The execution of the command repeats until the loop ends, usually by the "LEAVE" command.
Normally, LOOP commands are labeled:
"end_label" cannot be given if "begin_label" is not present, and if both are present, they must be the same.
It is normally used together with the "LEAVE" constructs to exit the loop if a condition is met, and "ITERATE" to re-run the loop if a condition is met.
The LEAVE statement:
What is it?
This instruction is used to exit some control structure. It can be used inside a BEGIN ... END or inside some cycle (Loops).
Syntax
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1; ---> Here we break a Loop
END LOOP label1;
How it works
With this command we manage to break the loop and exit it.
The ITERATE statement:
What is it?
ITERATE means "do the loop again".
ITERATE can only appear in LOOP, REPEAT, and WHILE commands.
Syntax
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
How it works
If the condition is met, ITERATE repeats the loop.
The REPEAT/UNTIL statement
What is it?
The REPEAT/UNTIL statement is a command that repeats the command inside it until a condition is met.
Syntax
This statement consists of two basic parameters:
1. The statement or list of statements to be repeated.
2. The loop exit condition.
The statement must end with an END REPEAT; statement.
In addition, the REPEAT statement can be labeled with matching start (BEGIN) and end (END) labels.
How it works
In this statement, the list of statements is repeated until the condition is true. The loop always enters at least once and the list of statements can consist of more than one, separated by a semicolon (;) after having delimited the //.
The WHILE/DO statement
What is it?
The WHILE/DO statement is a command that repeats the command inside it while the WHILE condition is true.
Syntax
[begin_label:]
WHILE search_condition DO
statement_list
END WHILE
[end_label]
This statement consists of two basic parameters:
1. The condition to remain in the loop.
2. The statement or list of statements to be repeated.
The statement must end with an END WHILE; statement.
In addition, the WHILE/DO statement can be labeled with matching start (BEGIN) and end (END) labels.
How it works
In this statement, the list of statements is repeated while the condition is true. The loop always enters at least once and the list of statements can consist of more than one, separated by a semicolon (;) after having delimited the //.
In addition, the WHILE/DO statement can be labeled with matching start (BEGIN) and end (END) labels.