返回顶部

MySQL IF 语句

更多

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 three value TRUE, FALSE or NULL.

MySQL IF statement syntax

The following illustrates the syntax of the IF statement:

IF if_expression THEN commands
   [ELSEIF elseif_expression THEN commands]
   [ELSE commands]
   END IF;

If the if_expression evaluates to TRUE the commands in the IF branch will execute. If it evaluates to FALSE, MySQL will check the elseif_expression and execute the commands in 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 commands in the ELSE branch will execute.

MySQL IF statement examples

Let’s take a look at an example of how to use MySQL IF statements.

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$$

We pass customer number to the stored procedure to get customer level based on credit limit. We use IF ELSEIF and ELSE statement to check customer credit limit against multiple values.

The following is the flow chart  that demonstrates the logic of determining customer level.

MySQL IF statement flow chart

In this tutorial, you have learned how to use MySQL IF statement to execute a block of SQL code based on conditions.

推荐阅读:
支持

0

反对

0

发表评论