Shulou(Shulou.com)06/01 Report--

Mysql flow control statements may not be used most of the time, but they will be used if we need to write stored procedures. With flow control statements, mysql can write programs like other programming languages. The conditional bifurcation and the loop body are introduced below.

Conditional branching

Mysql has two kinds of conditional bifurcations, IF and CASE.

IF condition

First of all, let's look at its syntax:

IF CONDITION THEN statement; [ELSEIF CONDITION THEN statement]. [ELSE statement] END IF

It looks a bit like php, but there is no "{}" in mysql, so use END IF to end conditional branches in mysql.

Next, let's write the simplest conditional branch to determine whether the input value is greater than 60, and output pass if it is true.

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, the output PERFETCT is greater than or equal to 80, the output great; is greater than or equal to 70, the output good; is greater than or equal to 60 output common;, otherwise the output 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 bifurcation

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

Syntax 1:CASE WHEN V1 THEN statement [WHEN V2 THEN statement]... [ELSE statement] END CASE; syntax 2:CASE VALUE WHEN V1 THEN statement [WHEN V2 THEN statement]... [ELSE statement] END CASE

Let's use Syntax 2 to accomplish the same function as IF.

BEGIN CASE FLOOR (score/10) WHEN 10 THEN SELECT 'excellent'; WHEN 9 THEN SELECT 'excellent'; WHEN 8 THEN SELECT 'good'; WHEN 7 THEN SELECT 'average'; WHEN 6 THEN SELECT 'pass'; ELSE SELECT 'fail'; END CASE; END

Cyclic body

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

WHILE

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

WHILE CONDITION DO cycle body END WHILE

Next, let's write the simplest loop body and calculate 1 percent 2 +. The sum of + 100.

BEGIN DECLARE i TINYINT DEFAULT 1; DECLARE n TINYINT DEFAULT 100; DECLARE s INT DEFAULT 0; WHILE I 100 END REPEAT; SELECT

LOOP cycle

LOOP can execute a code block repeatedly and label loop.

LEAVE is equivalent to the break statement in php, and if loop wants to exit the loop, it must be implemented using leave. In addition, ITERATE can be used to implement the function of continue statements in php.

Next, let's look at two examples.

The first is to calculate the sum of all natural numbers by 100, but there is a note here that 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 sten end

For 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

The above are the conditions of Mysql process control, the details of the loop structure, more please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.