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

標籤：roc   fetch   turn   return語句   leave   isl   returns   複雜   預設   

1.標籤語句

[begin_label:] BEGIN    [statement_list]END [end_label][begin_label:] LOOP    statement_listEND LOOP [end_label][begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label][begin_label:] WHILE search_condition DO    statement_listEND 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 CASEOr: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_listEND 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_listUNTIL search_conditionEND REPEAT [end_label]

執行個體：

mysql> delimiter //mysql> create procedure doiterate4(p1 int)    -> begin    -> set @x=0;    -> repeat    -> set @[email protected]+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_listEND 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 高階文法及流程式控制制