MySQL-5.7 高階文法及流程控制

本文介紹MySQL-5.7 高階文法及流程控制語句的寫法

1.标簽語句

[begin_label:] BEGIN
    [statement_list]
END [end_label]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

标簽label可以加在begin...end語句以及loop，repeat和while語句上
語句中通過iterate和leave來控制流程，iterate表示傳回指定标簽位置，leave表示跳出标簽

執行個體：

mysql> delimiter //
mysql> create procedure doiterate(in p1 int,out p2 int)
    -> begin
    -> label1:loop
    -> set p1 = p1 + 1;
    -> if p1 < 10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set p2=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call doiterate(1,@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> call doiterate(5,@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

2.Declare語句

文法：

DECLARE var_name [, var_name] ...  type [DEFAULT value]

Declare語句通常聲明本地變量、遊标、條件或者handler
Declare語句隻允許出現在begin...end語句中而且必須出現在第一行
Declare的順序也有要求，通常是先聲明本地變量，再是遊标，然後是條件和handler
使用default指定變量的預設值，如果沒有指定預設值則為NULL
聲明的變量和被引用的資料表中的字段要區分開

存儲過程中的變量

本地變量可以通過declare語句聲明
聲明後的變量可以通過select...into var_list進行指派，或者通過set語句指派，或者通過定義遊标并使用fetch...into var_list指派

mysql> delimiter //
mysql> create procedure sp1(v_sid int)
    -> begin
    -> declare xname varchar(64) default 'bob';
    -> declare xgender int;
    -> select sname,gender into xname,xgender from students where sid=v_sid;
    -> select xname,xgender;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> 
mysql> call sp1(1);
+--------+---------+
| xname  | xgender |
+--------+---------+
| Andrew |       1 |
+--------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3.流程控制語句

（1）case語句

在存儲過程或函數中表明複雜的條件選擇語句。

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

說明：

case_value與when_value依次做相等對比，如果相等則執行對應的後面的SQL語句，否則接着對比；
當search_condition滿足true/1的結果時，則執行對應的SQL語句，否則執行else對應的SQL語句；

mysql> delimiter //
mysql> create procedure exp_case(v_sid int)
    -> begin
    -> declare v int default 1;
    -> select gender into v from students where sid=v_sid;
    -> case v
    -> when 0 then update students set gender=1 where sid=v_sid;
    -> when 1 then update students set gender=0 where sid=v_sid;
    -> else
    -> update students set gender=-1 where sid=v_sid;
    -> end case;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 1      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

mysql> call exp_case(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 0      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

另一種寫法：
mysql> delimiter //
mysql> create procedure exp_case2(v_sid int)
    -> begin
    -> declare v int default 1;
    -> select gender into v from students where sid=v_sid;
    -> case
    -> when v=0 then update students set gender=1 where sid=v_sid;
    -> when v=1 then update students set gender=0 where sid=v_sid;
    -> else
    -> update students set gender=-1 where sid=v_sid;
    -> end case;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call exp_case2(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | 1      |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

（2）IF語句

在存儲過程或函數中表明基礎的條件選擇語句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

如果search_condition滿足true/1的條件，則執行對應的SQL語句，否則再判斷elseif中的search_condition，都不滿足則執行else中的SQL語句；
statement_list中可以包含一個或多個SQL語句

mysql> delimiter //
mysql> create function SimpleCompare(n int,m int)
    -> returns varchar(20)
    -> begin
    -> declare s varchar(20);
    -> if n > m then set s = '>';
    -> elseif n = m then set s = '=';
    -> else set s = '<';
    -> end if;
    -> set s = concat(n,'',s,'',m);
    -> return s;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> select SimpleCompare(5,6);
+--------------------+
| SimpleCompare(5,6) |
+--------------------+
| 5<6                |
+--------------------+
1 row in set (0.00 sec)

mysql> select SimpleCompare(15,34);
+----------------------+
| SimpleCompare(15,34) |
+----------------------+
| 15<34                |
+----------------------+
1 row in set (0.00 sec)

mysql> select SimpleCompare(78,78);
+----------------------+
| SimpleCompare(78,78) |
+----------------------+
| 78=78                |
+----------------------+
1 row in set (0.00 sec)

IF嵌套：

mysql> delimiter //
mysql> create function verboseCompare(n int,m int)
    -> returns varchar(50)
    -> begin
    -> declare s varchar(50);
    -> if n = m then set s = 'equals';
    -> else
    -> if n > m then set s = 'greater';
    -> else set s = 'less';
    -> end if;
    -> set s = concat('is',s,'than');
    -> end if;
    -> set s = concat(n,'',s,'',m,'.');
    -> return s;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select verboseCompare(4,5);
+---------------------+
| verboseCompare(4,5) |
+---------------------+
| 4islessthan5.       |
+---------------------+
1 row in set (0.00 sec)

（3）iterate語句

僅出現在loop，repeat，while循環語句中，表示重新開始此循環。

ITERATE label

（4）leave語句

表明指定标簽的流程控制語句塊，通常用在begin...end，以及loop，repeat，while循環彙總；

LEAVE label

mysql> delimiter //
mysql> create procedure doiterate2(in p1 int,out p2 int)
    -> begin
    -> label1:loop
    -> set p1=p1+1;
    -> if p1 < 10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set p2=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

（5）loop語句

在存儲過程或函數中表達循環執行的一種方式；

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

mysql> delimiter //
mysql> create procedure doiterate3(p1 int)
    -> begin
    -> label1:loop
    -> set p1=p1+1;
    -> if p1<10 then iterate label1;end if;
    -> leave label1;
    -> end loop label1;
    -> set @x=p1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

（6）repeat語句

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

mysql> delimiter //
mysql> create procedure doiterate4(p1 int)
    -> begin
    -> set @x=0;
    -> repeat
    -> set @x=@x+1;
    -> until @x>p1 end repeat;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call doiterate4(1000);
Query OK, 0 rows affected (0.02 sec)

mysql> select @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

（7）while語句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

當search_condition傳回true時，循環執行SQL語句，直到search_condition為false；

mysql> delimiter //
mysql> create procedure dowhile()
    -> begin
    -> declare v1 int default 5;
    -> while v1>0 do
    -> update students set gender=-1 where sid=v1;
    -> set v1=v1-1;
    -> end while;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call dowhile();
Query OK, 1 row affected (0.63 sec)

mysql> select * from students;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | -1     |       1 |
|   2 | Andy   | -1     |       1 |
|   3 | Bob    | -1     |       1 |
|   4 | Ruth   | -1     |       2 |
|   5 | Mike   | -1     |       2 |
|   6 | John   | 0      |       3 |
|   7 | Cindy  | 1      |       3 |
|   8 | Susan  | 1      |       3 |
+-----+--------+--------+---------+
8 rows in set (0.00 sec)

（8）return語句

在函數中，用來終結函數的執行并将指定值傳回給調用者；

RETURN expr

在函數中必須有至少一個return語句，當有多個return語句時則表明函數有多種退出方式；

mysql> delimiter //
mysql> create function doreturn()
    -> returns int
    -> begin
    -> select gender into @a from students where sid = 1;
    -> if @a=1 then return 1;
    -> elseif @a=0 then return 0;
    -> else return 999;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select doreturn();
+------------+
| doreturn() |
+------------+
|        999 |
+------------+
1 row in set (0.00 sec)

mysql> select * from students where sid = 1;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | -1     |       1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)

MySQL-5.7 高階文法及流程控制

1.标簽語句

2.Declare語句

3.流程控制語句

繼續閱讀

C# break、continue、return

Linux shell腳本 for while 循環循環for循環及while循環的應用

Neo4j Cypher CASE語句

Oracle通過case when條件判斷和instr字元串查找修改查詢結果

QT+opencv ROI區域圖像疊加&初級圖像混合 label裡輸出Mat圖檔要做的變換

QComboBox、QFrame、QLabel

（C語言）可變參數清單

STM32程式中if和#if的差別

MyBatis——動态SQL、if、where、set、foreach、sql片段一、動态SQL

【Java基礎】Java的循環結構：for循環、while循環、do-while循環

QT控件之标簽

C#學習之break、continue和return

mysql5.7官網直譯SQL語句優化--嵌套連接配接的優化

[SwitchCase]C語言程式設計switch case的使用

施耐德UnityXLpro結構指令CASE的使用和了解施耐德UnityXLpro結構指令CASE的使用和了解

代碼的可用性，可讀性的探索