Detailed example of using if statement in mysql stored procedure

This article uses an example to illustrate the usage of the if statement in MySQL stored procedure. Share with you for your reference, the details are as follows:

The IF statement in MySQL allows us to execute a set of SQL statements based on a certain condition or value result of an expression, so we have to form an expression in MySQL, which can be combined with text, variables, operators, and even functions. An expression can return one of three values: TRUE, FALSE, or NULL. Let's look at the grammatical structure:

IF expression THEN
  statements;
END IF;

If the above expression evaluates to TRUE, then the statements will be executed, otherwise the control flow will pass to the next statement after END IF. Let's take a look at the execution process of the IF statement:

Let's look at the syntax of the IF ELSE statement:

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

Now let's look at the execution process of the IF ELSE statement:

If we want to conditionally execute statements based on multiple expressions, we use the IF ELSE IF ELSE statement, and its syntax structure is as follows:

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

If expression evaluates to TRUE, the statements in the IF branch are executed; if expression evaluates to FALSE and elseif_expression evaluates to TRUE, mysql executes elseif-expression, otherwise it executes the else-statements in the ELSE branch. Let's take a look at the specific implementation process:

We then use the IF ESLEIF ELSE statement and the GetCustomerLevel() stored procedure to accept two parameters: customer number and customer level. First, the GetCustomerLevel() stored procedure must first obtain the credit limit from the customers table. Then, based on the credit limit, it determines the customer level: PLATINUM, GOLD, and SILVER. The parameter p_customerlevel stores the customer level and is used by the calling program. Let's look at the specific SQL:

DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
  in p_customerNumber int(11),
  out p_customerLevel varchar(10))
BEGIN
  DECLARE creditlim double;
  SELECT creditlimit INTO creditlimit
  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 flow chart of the logic for determining customer level is as follows:

Okay, that’s all for this sharing.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.