IFsearch_condition
THENstatement_list
[ELSEIFsearch_condition
THENstatement_list
] ... [ELSEstatement_list
] END IF
IF
implements a basic conditional construct. If the
search_condition
evaluates to true,
the corresponding SQL statement list is executed. If no
search_condition
matches, the
statement list in the ELSE
clause is
executed. Each statement_list
consists of one or more statements.
There is also an IF()
function, which differs from the
IF
statement described here. See
Section 11.3, “Control Flow Functions”.
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:
DELIMITER // 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:
DELIMITER // 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
.
User Comments
Add your own comment.