MySQL全面瓦解20：可編程性之流程式控制制語句

2021 年 1 月 22 日
筆記
CASE, iterate, leave, loop, MySQL, MySQL全面瓦解, repeat, While

背景

說到流程式控制制語句，我們在程式語法中用的比較多，比如C#的if..else…，while…，?: 等。同樣的，在MySQL中，也有一些流程式控制制的語法，方便我們在寫函數、存儲過程的時候對邏輯進行控制和處理。

常見的過程式SQL語句可以用在存儲過程或者函數體中。其中包括：IF函數、IF條件語句、CASE語句、LOOP語句、WHILE語句、REPEAT語句、LEAVE語句和ITERATE語句，它們極大的方便了我們進行流程式控制制。

下面我們一個一個來看。

流程語句分解

數據基礎

 1 mysql> select *  from students;
 2 +-----------+-------------+-------+---------+-----+
 3 | studentid | studentname | score | classid | sex |
 4 +-----------+-------------+-------+---------+-----+
 5 |         1 | brand       | 105.5 |       1 |   1 |
 6 |         2 | helen       | 98.5  |       1 |   0 |
 7 |         3 | lyn         | 97    |       1 |   0 |
 8 |         4 | sol         | 97    |       1 |   1 |
 9 |         5 | b1          | 89    |       2 |   1 |
10 |         6 | b2          | 90    |       2 |   1 |
11 |         7 | c1          | 76    |       3 |   0 |
12 |         8 | c2          | 73.5  |       3 |   0 |
13 |         9 | lala        | 73    |       0 |   0 |
14 |        10 | A           | 100   |       3 |   1 |
15 |        16 | test1       | 100   |       0 |   1 |
16 |        17 | trigger2    | 107   |       0 |   1 |
17 |        22 | trigger1    | 100   |       0 |   0 |
18 +-----------+-------------+-------+---------+-----+
19 13 rows in set
20 
21 mysql> select *  from scores;
22 +-----------+---------+-------+
23 | scoregrad | downset | upset |
24 +-----------+---------+-------+
25 | A         |      81 |    90 |
26 | B         |      71 |    80 |
27 | C         |      61 |    70 |
28 | D         |      51 |    60 |
29 | S         |      91 |   100 |
30 | S+        |     101 |   120 |
31 +-----------+---------+-------+
32 6 rows in set

IF函數

有點類似C#語法中的三元表達式，有3個參數，第一個參數是表達式，後面兩個是值，當表達式成立的時候取第一個值，表達式不成立的時候取第二個值。

1 if(expr,val1,val2);   --語法

輸出學生資訊中的名稱和性別（1為男，0為女，這邊用if函數進行轉換）

 1 mysql> select studentname,if(sex=0,'女','男') from students where classid<>0;
 2 +-------------+---------------------+
 3 | studentname | if(sex=0,'女','男') |
 4 +-------------+---------------------+
 5 | brand       | 男                  |
 6 | helen       | 女                  |
 7 | lyn         | 女                  |
 8 | sol         | 男                  |
 9 | b1          | 男                  |
10 | b2          | 男                  |
11 | c1          | 女                  |
12 | c2          | 女                  |
13 | A           | 男                  |
14 +-------------+---------------------+
15 9 rows in set

IF條件語句

IF語句用來進行條件判斷，根據不同的條件執行不同的操作。該語句在執行時首先判斷IF後的條件是否為真，則執行THEN後的語句，如果為假則繼續判斷IF語句直到為真為止，當以上都不滿足時則執行ELSE語句後的內容。

1 IF condition THEN
2     ...
3 ELSEIF condition THEN
4     ...
5 ELSE
6     ...
7 END IF

程式碼示例，根據考試成績來分布不同的成績等級

 1 mysql> 
 2 /*如果存在函數func_test2，則刪除*/
 3 DROP FUNCTION IF EXISTS fun_if;
 4 /*聲明結束符為$*/
 5 DELIMITER $
 6 /*創建函數*/
 7 CREATE FUNCTION fun_if(score DECIMAL(10,2))
 8   RETURNS CHAR
 9 BEGIN
10 DECLARE score_grad VARCHAR(5) DEFAULT '';
11 IF score>100 THEN SET score_grad='S'; 
12 ELSEIF (score BETWEEN 91 AND 100) THEN SET score_grad='A';
13 ELSEIF (score BETWEEN 81 AND 90) THEN SET score_grad='B';
14 ELSEIF (score BETWEEN 71 AND 80) THEN SET score_grad='C' ;
15 ELSE set score_grad='D';
16 END IF;
17 return score_grad;
18 END $
19 /*重置結束符為;*/
20 DELIMITER ;
21 Query OK, 0 rows affected

執行結果

1 mysql> select fun_if(101),fun_if(100),fun_if(90),fun_if(80),fun_if(70);
2 +-------------+-------------+------------+------------+------------+
3 | fun_if(101) | fun_if(100) | fun_if(90) | fun_if(80) | fun_if(70) |
4 +-------------+-------------+------------+------------+------------+
5 | S           | A           | B          | C          | D          |
6 +-------------+-------------+------------+------------+------------+
7 1 row in set

CASE語句

CASE語句為多分支語句結構，該語句首先從WHEN後的VALUE中查找與CASE後的VALUE相等的值，如果查找到則執行該分支的內容，否則執行ELSE後的內容。CASE語句表示形式如下，類似C#中switch：

1 CASE expr
2 WHEN val1 THEN result1 or state1[;]（可選項，如果是語句需要加分號,結果值可以加）
3 WHEN val2 THEN result2 or state2
4 ...
5 ELSE resultn or staten
6 END [CASE] （可選項，在begin end之間需加case，select後就不需要）

在select中使用示例

 1 mysql> select studentname,case sex WHEN 0 THEN '女' WHEN 1 THEN '男' end as sex
 2 from students where classid<>0;
 3 +-------------+-----+
 4 | studentname | sex |
 5 +-------------+-----+
 6 | brand       | 男  |
 7 | helen       | 女  |
 8 | lyn         | 女  |
 9 | sol         | 男  |
10 | b1          | 男  |
11 | b2          | 男  |
12 | c1          | 女  |
13 | c2          | 女  |
14 | A           | 男  |
15 +-------------+-----+
16 9 rows in set

在函數或存儲過程中使用示例

 1 mysql> 
 2 /*如果存在函數func_test2，則刪除*/
 3 DROP FUNCTION IF EXISTS fun_case;
 4 /*聲明結束符為$*/
 5 DELIMITER $
 6 /*創建函數*/
 7 CREATE FUNCTION fun_case(sex INT)
 8 RETURNS VARCHAR(20)
 9 BEGIN
10 DECLARE sexStr VARCHAR(20) DEFAULT '';
11 CASE sex 
12 WHEN 0 then set sexStr='女';
13 WHEN 1 then set sexStr='男';
14 ELSE set sexStr='不確定';
15 END CASE;
16 return sexStr;
17 END $
18 /*重置結束符為;*/
19 DELIMITER ;
20 
21 Query OK, 0 rows affected

函數執行結果

 1 mysql> select studentname,fun_case(sex) from students where classid<>0;
 2 +-------------+---------------+
 3 | studentname | fun_case(sex) |
 4 +-------------+---------------+
 5 | brand       | 男            |
 6 | helen       | 女            |
 7 | lyn         | 女            |
 8 | sol         | 男            |
 9 | b1          | 男            |
10 | b2          | 男            |
11 | c1          | 女            |
12 | c2          | 女            |
13 | A           | 男            |
14 +-------------+---------------+
15 9 rows in set

循環語句while

循環語句while 類似於C#中的while循環，我們知道在C#的while 或者 for 語句中，經常有用到兩個關鍵語法：跳過當前循環（continue）和結束循環（break）。

同樣的，在MySQL中也有兩個語法對應跳過和結束循環。

1  ITERATE  loop_label;   --跳過當前循環

1 LEAVE  loop_label;   --結束循環

while 語法

1 [loop_label:]while condition do
2  --Todo:loop body
3 end while [loop_label];

loop_label：循環標籤，和iterate、leave結合用於在循環內部對循環進行控制：如：跳過本次循環、結束循環。

condition：循環條件，當滿足條件的時候，就會執行循環體，條件不成立的時候結束循環。

while示例

下面腳本程式碼演示了將students表中studentid在給定數值範圍內的數據存儲到另外一張表中。

 1 /*先清除studentCount表記錄*/
 2 truncate table studentcount;
 3 /*存儲過程如果存在則刪除*/
 4 DROP PROCEDURE IF EXISTS sp_while1;
 5 /*聲明結束符為$*/
 6 DELIMITER $
 7 /*創建存儲過程*/
 8 CREATE PROCEDURE sp_while1(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 1;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:WHILE idx<=varial_count DO
13       select studentname into uname from students where studentid = idx;
14       INSERT into studentCount values (idx,uname);
15       SET idx=idx+1;
16     END WHILE;
17   END $
18 /*結束符置為;*/
19 DELIMITER ;

調用存儲過程，給定數值範圍是10，所以這邊取出1~10的數據存儲到studentCount表中

 1 mysql> CALL sp_while1(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         2 | helen       |
10 |         3 | lyn         |
11 |         4 | sol         |
12 |         5 | b1          |
13 |         6 | b2          |
14 |         7 | c1          |
15 |         8 | c2          |
16 |         9 | lala        |
17 |        10 | A           |
18 +-----------+-------------+
19 10 rows in set

while示例：包含iterate/leave

前面我們說明過了，iterate 和 leave 分別代表跳過本次循環，類似於C#中的continue和break。我們在例子中測試下吧：

遇到studentname=lala時，結束循環，遇到偶數時候跳過單次循環。

 1 /*先清除studentCount表記錄*/
 2 truncate table studentcount;
 3 /*存儲過程如果存在則刪除*/
 4 DROP PROCEDURE IF EXISTS sp_while2;
 5 /*聲明結束符為$*/
 6 DELIMITER $
 7 /*創建存儲過程*/
 8 CREATE PROCEDURE sp_while2(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:WHILE idx<=varial_count DO      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname為lala的同學，結束循環*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx為偶數，則跳過本次循環*/
19       ELSEIF idx%2=0 THEN
20         ITERATE loop_label;
21       END IF;      
22       INSERT into studentCount values (idx,uname);      
23     END WHILE;
24   END $
25 /*結束符置為;*/
26 DELIMITER ;

調用存儲過程，輸出符合要求的數據：

 1 mysql> CALL sp_while2(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         3 | lyn         |
10 |         5 | b1          |
11 |         7 | c1          |
12 +-----------+-------------+
13 4 rows in set

循環語句repeat

repeat語法

1 [loop_label:]repeat
2 -- Todo loop body
3 until condition 
4 end repeat [loop_label];

可以對比下上面while的語法，while是先判斷條件是否成立再執行循環體，repeat循環更像是的do…while循環，就是循環始終都會先執行一次，然後再判斷結束循環的條件，不滿足結束條件，循環體繼續執行。

 1 /*先清除studentCount表記錄*/
 2 truncate table studentcount;
 3 /*存儲過程如果存在則刪除*/
 4 DROP PROCEDURE IF EXISTS sp_repeat;
 5 /*聲明結束符為$*/
 6 DELIMITER $
 7 /*創建存儲過程*/
 8 CREATE PROCEDURE sp_repeat(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:REPEAT      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname為lala的同學，結束循環*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx為偶數，則跳過本次循環*/
19       ELSEIF idx%2=0 THEN
20         ITERATE loop_label;
21       END IF;      
22       INSERT into studentCount values (idx,uname);  
23     UNTIL idx>varial_count   
24     END REPEAT;
25   END $
26 /*結束符置為;*/
27 DELIMITER ;

注意條件的變化，下面是調用存儲過程，輸出需要的數據：

 1 mysql> CALL sp_repeat(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         3 | lyn         |
10 |         5 | b1          |
11 |         7 | c1          |
12 +-----------+-------------+
13 4 rows in set

循環語句loop

loop語法

1 [loop_label:]loop
2 --Todo loop body
3 end loop [loop label];

loop不像while和repeat那樣有控制條件，條件不符合的時候會跳出。所以它實際上是會一直執行的，如果不主動中斷或者跳出的話，類似於一個死循環，需要在循環體中使用iterate或者leave來控制循環的執行。

 1 /*先清除studentCount表記錄*/
 2 truncate table studentcount;
 3 /*存儲過程如果存在則刪除*/
 4 DROP PROCEDURE IF EXISTS sp_loop;
 5 /*聲明結束符為$*/
 6 DELIMITER $
 7 /*創建存儲過程*/
 8 CREATE PROCEDURE sp_loop(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:LOOP      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname為lala的同學，結束循環*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx為偶數，則跳過本次循環*/
19       ELSEIF idx%2<>0 THEN
20         ITERATE loop_label;
21       /*這邊加一個終結計數跳出的條件*/
22       ELSEIF idx>varial_count THEN
23         LEAVE loop_label;
24       END IF;      
25       INSERT into studentCount values (idx,uname); 
26       
27     END LOOP;
28   END $
29 /*結束符置為;*/
30 DELIMITER ;

調用存儲過程，並輸出你需要的數據：

 1 mysql> CALL sp_loop(6);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         2 | helen       |
 9 |         4 | sol         |
10 |         6 | b2          |
11 +-----------+-------------+
12 3 rows in set

總結

1、了解了IF函數，它常用在SELECT語句中，類似於C#中的三元表達式。
2、IF條件表達式，類似於C#中的IF… ELSE…，多用於函數或存儲過程中的判斷選擇邏輯。
3、了解CASE語句的兩種用法，一種用在SELECT中使用，一種用在函數和存儲過程中。
4、了解了三種循環體的使用，while、repeat分別對應C#中的while 和 do while循環，loop類似於一個while(true)的死循環。
5、循環體都包含在begin end中，循環體的控制依靠leave和iterate，leave相當於break，即退出整個循環體，iterate類似於continue，即跳過本次循環。

Tags: CASE iterate leave loop MySQL MySQL全面瓦解 repeat While