How to use if in mysql stored procedure

In mysql, the if statement is used to execute a set of SQL statements based on a certain condition or value result of the expression. The syntax is "IF expression THEN statements;END IF;"; when the expression evaluates to When TRUE, the statements statement will be executed.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to use if in mysql stored procedure

The MySQL IF statement allows you to execute a statement based on a certain condition or value result of an expression. Group SQL statements. To form an expression in MySQL, you can combine literals, variables, operators, and even functions. The expression can return TRUE, FALSE or NULL, one of these three values.

MySQL IF statement syntax

The following explains the syntax of the IF statement:

IF expression THEN 
   statements;
END IF;

If the expression (expression) evaluates to TRUE, then The statements statement is executed, otherwise control flow passes to the next statement after the END IF.

The following flow chart demonstrates the execution process of the IF statement:

MySQL IF ELSE statement

If the expression To execute a statement when the expression calculation result is FALSE, please use the IF ELSE statement, as shown below:

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

The following flow chart illustrates the execution process of the IF ELSE statement:

MySQL IF ELSEIF ELSE statement

If you want to conditionally execute a statement based on multiple expressions, use the IF ELSEIF ELSE statement as follows:

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

If the expression If the expression evaluates to TRUE, the statements in the IF branch will be executed; if the expression evaluates to FALSE, then if elseif_expression evaluates to TRUE, MySQL will execute elseif-expression, otherwise the ELSE branch will be executed. else-statements statement. The specific process is as follows

MySQL IF statement example

The following example shows how to use the IF ESLEIF ELSE statement, which the GetCustomerLevel() stored procedure accepts Two parameters: customer number and customer level.

First, it gets the credit limit from the customers table

Then, based on the credit limit, it decides the customer level: PLATINUM, GOLD and SILVER.

The parameter p_customerlevel stores the customer's level and is used by the calling program.

USE yiibaidb;
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 = &#39;GOLD&#39;;
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = &#39;SILVER&#39;;
    END IF;
END$$

The following flow chart demonstrates the logic of determining customer level

Recommended learning: mysql video tutorial

The above is the detailed content of How to use if in mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!