转载自：http://www.mysqltutorial.org/mysql-if-statement/
MySQL IF Statement
Summary: in this tutorial, you will learn how to use MySQL IF statement to execute a block of SQL code based on conditions.
The MySQL IF statement allows you to execute a set of SQL statements based on a certain condition or value of an expression. To form an expression in MySQL, you can combine literals, variables, operators, and even functions. An expression can return TRUE FALSE, or NULL.
Note that there is an IF function that is different from the IF statement specified in this tutorial.
MySQL IF statement syntax
The following illustrates the syntax of the IF statement:
|
1
2
3
|
IF expression THEN
statements;
END IF;
|
If the expression evaluates to TRUE , then the statements will be executed, otherwise, the control is passed to the next statement following the END IF.
The following flowchart demonstrates the IF statement:
MySQL IF-ELSE statement
In case you want to execute statements when the expression does not evaluate to TRUE, you use the IF-ELSE statement as follows:
|
1
2
3
4
5
|
IF expression THEN
statements;
ELSE
else-statements;
END IF;
|
The following flowchart illustrates the IF ELSE statement:
MySQL IF-ELSEIF-ELSE statement
If you want to execute statements conditionally based on multiple expressions, you use the IF-ELSEIF-ELSE statement as follows:
|
1
2
3
4
5
6
7
8
|
IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF;
|
If the expression evaluates to TRUE , the statements in the IF branch executes; Otherwise, MySQL will check the elseif-expression and execute the elseif-statements in the ELSEIF branch if the elseif_expression evaluates to TRUE .
The IF statement may have multiple ELSEIF branches to check multiple expressions. If no expression evaluates to TRUE, the else-statements in the ELSE branch will execute.
MySQL IF statement examples
The following example illustrates how to use the IF-ESLEIF-ELSE statement. The GetCustomerLevel() stored procedure accepts two parameters customer number and customer level.
- First, it gets the credit limit from the
customerstable. - Then, based on the credit limit, it determines the customer level:
PLATINUM,GOLD, andSILVER.
The parameter p_customerlevel stores the level of the customer and is used by the calling program.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
in p_customerNumber int(11),
out p_customerLevel varchar(10))
BEGIN
DECLARE creditlim double;
SELECT creditlimit INTO creditlim
FROM customers
WHERE customerNumber = p_customerNumber;
IF creditlim > 50000 THEN
SET p_customerLevel = 'PLATINUM';
ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
SET p_customerLevel = 'GOLD';
ELSEIF creditlim < 10000 THEN
SET p_customerLevel = 'SILVER';
END IF;
END$$
|
The following flowchart demonstrates the logic of determining customer level.
In this tutorial, you have learned how to use MySQL IF statement to execute a block of SQL code based on conditions.