Mysql process control conditions and loop structure

Mysql's flow control statements may not be used most of the time, but if we need to write a stored procedure, we will use it. With flow control statements, mysql can write programs like other programming languages. Conditional branches and loop bodies are introduced below respectively.

Conditional branches

Mysql has two types of conditional branches, IF and CASE.

IF condition

First let’s look at its syntax:

IF CONDITION THEN 语句；
[ELSEIF CONDITION THEN 语句]
……
[ELSE 语句]
END IF;

looks a bit like php, but in mysql There is no "{}" here, so use END IF in mysql to end the conditional branch.

Let’s write the simplest conditional branch to determine whether the input value is greater than 60. If it is true, pass will be output.

BEGIN
 IF score >= 60 THEN
  SELECT 'PASS';
  END IF;
END

Now, let’s write a multi-branch structure. When the score is greater than or equal to 90, PERFETCT is output, and when it is greater than or equal to 80, it outputs great; when it is greater than or equal to 70, it outputs good; when it is greater than or equal to 60, it outputs common; otherwise it outputs bad;

BEGIN
 IF score >= 90 THEN SELECT 'PERFECT';
    ELSEIF score >= 80 THEN SELECT 'GREAT';
    ELSEIF score >=70 THEN SELECT 'GOOD';
    ELSEIF score >= 60 THEN SELECT 'COMMON';
    ELSE SELECT 'BAD';
    END IF;
END

CASE conditional branch

case conditional branch has two syntaxes. The first is similar to IF in PHP, and the other is similar to the switch-case type in PHP.

语法1：
CASE 
  WHEN V1 THEN 语句 
  [WHEN V2 THEN 语句]
  ……
  [ELSE 语句]
END CASE;

语法2：
CASE VALUE 
  WHEN V1 THEN 语句
  [WHEN V2 THEN 语句]
  ……
  [ELSE 语句]
END CASE;

Let’s use syntax 2 to complete the same function as IF.

BEGIN
    CASE FLOOR(score/10) 
    WHEN 10 THEN SELECT '优秀';
    WHEN 9 THEN SELECT '优秀';
    WHEN 8 THEN SELECT '良好';
    WHEN 7 THEN SELECT '一般';
    WHEN 6 THEN SELECT '及格';
    ELSE SELECT '不及格';
    END CASE;
 END

Loop body

There are also several different loop body structures in Mysql, and there are also statements similar to break and continue in PHP. Next, let's take a look at these loops and their differences.

WHILE

The while here is basically the same as php. His syntax is as follows:

WHILE CONDITION DO
  循环体
END WHILE;

Next, let's write the simplest loop body to calculate the sum of 1 2...100.

BEGIN
  DECLARE i TINYINT DEFAULT 1;
  DECLARE n TINYINT DEFAULT 100;
  DECLARE s INT DEFAULT 0;
  WHILE i <= n DO
    SET s = s + i;
    SET i = i + 1;
  END WHILE;
 SELECT s;
END

Here DECLARE is equivalent to defining a variable and then setting the initial value with default. The assignment of variables requires the SET command.

REPEAT

repeat is equivalent to the do-while loop body in PHP. The loop body is executed first, and then the condition is judged. Its syntax is as follows:

REPEAT
  循环体
UNTIL CONDITION
END REPEAT;

Below, we use REPEAT to complete the sum of 1 2... 100.

BEGIN
 DECLARE i TINYINT DEFAULT 1;
  DECLARE n TINYINT DEFAULT 100;
  DECLARE s INT DEFAULT 0;
  REPEAT 
    SET s = s+i;
    SET i = i+1;
  UNTIL i > 100
  END REPEAT;
 SELECT s;
END

LOOP loop

LOOP can repeatedly execute a code block and set labels for the loop.

LEAVE is equivalent to the break statement in php. If loop wants to exit the loop, it must use leave to achieve it. In addition, ITERATE can be used to implement the function of the continue statement in php.

Now, let’s take a look at two examples.

The first step is to calculate the sum of all natural numbers starting from 100, but there is a point to note here. Because LOOP does not have a CONDITION statement, LEAVE must be used in conjunction with IF to complete the function of jumping out of the loop.

BEGIN
 DECLARE i TINYINT DEFAULT 1;
  DECLARE n TINYINT DEFAULT 100;
  DECLARE s INT DEFAULT 0;
  l:LOOP
    SET s = s + i;
    SET i = i + 1;
 
  IF i > n THEN LEAVE l;
    END IF;
  END LOOP l;  
 SELECT s;
END

In the second example, we will use iterate. Calculate the sum of all odd numbers within 100.

BEGIN
 DECLARE i TINYINT DEFAULT 0;
  DECLARE n TINYINT DEFAULT 100;
  DECLARE s INT DEFAULT 0;
  l:LOOP
  IF i >= n THEN LEAVE l;
  END IF;
  SET i = i + 1;
  IF i % 2 = 0 THEN ITERATE l;
  END IF;
    SET s = s + i;
  END LOOP l;  
 SELECT s;
END

The above is the detailed content of Mysql process control conditions and loop structure. For more information, please follow other related articles on the PHP Chinese website!