背景
說到流程控制語句,我們在程式文法中用的比較多,比如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,即跳過本次循環。
架構與思維·公衆号:撰稿者為bat、位元組的幾位高階研發/架構。不做廣告、不賣課、不要打賞,隻分享優質技術
碼字不易,歡迎關注,歡迎轉載
作者:翁智華
出處:https://www.cnblogs.com/wzh2010/
本文采用「CC BY 4.0」知識共享協定進行許可,轉載請注明作者及出處。