Press CTRL+C to copyIF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
The IF
statement for stored
programs implements a basic conditional construct.
There is also an IF()
function, which differs from the
IF
statement described here. See
Section 14.5, “Flow Control Functions”. The
IF
statement can have
THEN
, ELSE
, and
ELSEIF
clauses, and it is terminated with
END IF
.
If a given search_condition
evaluates
to true, the corresponding THEN
or
ELSEIF
clause
statement_list
executes. If no
search_condition
matches, the
ELSE
clause
statement_list
executes.
Each statement_list
consists of one
or more SQL statements; an empty
statement_list
is not permitted.
An IF ... END IF
block, like all other
flow-control blocks used within stored programs, must be
terminated with a semicolon, as shown in this example:
Press CTRL+C to copyDELIMITER // CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20) BEGIN DECLARE s VARCHAR(20); IF n > m THEN SET s = '>'; ELSEIF n = m THEN SET s = '='; ELSE SET s = '<'; END IF; SET s = CONCAT(n, ' ', s, ' ', m); RETURN s; END // DELIMITER ;
As with other flow-control constructs, IF ... END
IF
blocks may be nested within other flow-control
constructs, including other IF
statements. Each IF
must be
terminated by its own END IF
followed by a
semicolon. You can use indentation to make nested flow-control
blocks more easily readable by humans (although this is not
required by MySQL), as shown here:
Press CTRL+C to copyDELIMITER // CREATE FUNCTION VerboseCompare (n INT, m INT) RETURNS VARCHAR(50) BEGIN DECLARE s VARCHAR(50); IF n = m THEN SET s = 'equals'; ELSE IF n > m THEN SET s = 'greater'; ELSE SET s = 'less'; END IF; SET s = CONCAT('is ', s, ' than'); END IF; SET s = CONCAT(n, ' ', s, ' ', m, '.'); RETURN s; END // DELIMITER ;
In this example, the inner IF
is
evaluated only if n
is not equal to
m
.