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

翁智華發表於2021-01-22
MySql程式設計

背景

說到流程控制語句，我們在程式語法中用的比較多，比如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，即跳過本次迴圈。

相關文章