MySQL入门 (十三) : Stored Routines 的变数与流程

1 宣告与使用变数

在Stored routines中,除了可以宣告需要的参数外,如果需要处理比较复杂的资料,你也可以宣告「区域变数、local variables」。 下列是宣告区域变数的语法与位置:

mysql_14_snap_01

下列是几种宣告区域变数的范例:

mysql_14_snap_02

宣告需要的区域变数后,你就可以在stored routines中使用它们,需要指定变数值的话,可以使用下列两种语法:

mysql_14_snap_03

下列是宣告与使用「SET」叙述指定变数的范例:

mysql_14_snap_04

下列的范例使用「SELECT」叙述,把查询叙述回传的资料指定给变数:

mysql_14_snap_05

在Stored routines中宣告区域变数,一定要放在「BEGIN」与「END」区块中:

mysql_14_snap_06

在一个Stored routines中,除了基本的「BEGIN」与「END」区块,也可以再使用「BEGIN」与「END」设定一个区块,每一个区块都可以宣告需要的区域变数:

mysql_14_snap_07

在「BEGIN」与「END」区块中宣告的区域变数,只有在宣告的区块中有效,这也是它称为区域变数的原因:

mysql_14_snap_08

如果你使用一个已经被清除的区域变数,在建立stored routines时不会有问题,不过使用的时候就会发生错误:

mysql_14_snap_09

在同一个区块宣告变数时,不可以使用同样的变数名称;不过你可以在内层区块中,使用外层区块已经使用过的变数名称,可是要特别注意它们的有效范围:

mysql_14_snap_10

注:在撰写stored routines时,如果在多个区块中宣告变数,应该还是使用不同的变数名称会好一些。

2 判断

建立与使用stored routines可以帮你一次执行许多叙述,简化资料库的操作;除了这个好处外,stored routines还提供许多判断的语法,让你可以执行需要的判断,再根据判断的结果执行不同的工作。

2.1 IF

MySQL在stored routines中提供「IF」叙述,你可以在「IF」叙述中设定判断的条件,与条件成立时要执行的工作。 下列是「IF」叙述的语法:

mysql_14_snap_11

下列的procedure范例接收一个表示体重的整数参数,它会使用这个参数来判断体重是否太重,如果超过100公斤的话,就会显示「You are heavy!」:

mysql_14_snap_12

呼叫上列的「test_weight」procedure范例会有下列的结果:

mysql_14_snap_13

如果你希望体重超过100公斤时,显示「You are heavy!」,体重没有超过100公斤时,显示「Good!」。 这样的需求可以在「IF」叙述中使用「ELSEIF」判断其它需要的条件:

mysql_14_snap_14

呼叫上列的「test_weight2」procedure范例会有下列的结果:

mysql_14_snap_15

你可以依照需求在「IF」叙述中使用多个「ELSEIF」来判断不同的条件,也可以使用一个「ELSE」来处理所有条件都不成立时要执行的工作:

mysql_14_snap_16

呼叫上列的「test_weight3」procedure范例会有下列的结果:

mysql_14_snap_17

标准体重会依照身高与性别而不同,所以会有类似下列这样的表格:

身高范围 性别 标准体重
160~164 58
52
165~169 60
56
170~174 64
60

下列是一个依照上列表格所完成的标准体重函式:

mysql_14_snap_18

完成可以传回标准体重的「std_weight」函式以后,就可以用在下列这个判断体重的procedure中:

mysql_14_snap_19

2.2 CASE

在stored routines中还可以使用「CASE」叙述执行条件判断的工作。 「CASE」叙述有两种语法,第一种语法跟「IF」叙述是很类似的:

mysql_14_snap_20

以判断体重的需求来说,使用「CASE」叙述同样可以完成:

mysql_14_snap_21

「CASE」叙述还可以使用下列这种语法:

mysql_14_snap_22

这样的语法很适合使用在类似「ENUM」资料型态的判断,例如下列这个判断季节的procedure:

mysql_14_snap_23

使用这种「CASE」语法来执行判断工作时,要特别注意错误资料的处理:

mysql_14_snap_24

你应该加入「ELSE」来预防错误资料造成的问题:

mysql_14_snap_25

3 回圈

在stored routines中如果需要执行一个工作多次的时候,就可以使用「回圈、loops」,搭配使用判断与回圈,把一些固定又繁复的工作撰写成stored routines储存起来,可以大幅度简化资料库的操作。

3.1 WHILE

下列是可以用来执行一个工作多次的「WHILE」回圈语法:

mysql_14_snap_26

你必须依照需求设定「WHILE」回圈语法中的判断条件,由它来控制回圈是否继续执行:

mysql_14_snap_27

下列的「summary_while」范例可以为你从1开始加总到参数指定的数字:

mysql_14_snap_28

3.2 REPEAT

下列是可以用来执行一个工作多次的「REPEAT」回圈语法:

mysql_14_snap_29

你必须依照需求在「REPEAT」回圈语法中的「UNTIL」设定判断条件,由它来控制回圈是否继续执行:

mysql_14_snap_30

下列的「summary_repeat」范例可以为你从1开始加总到参数指定的数字:

mysql_14_snap_31

3.3 LOOP

下列是可以用来执行一个工作多次的「LOOP」回圈语法:

mysql_14_snap_32

如果只是单纯的使用「LOOP」回圈的话,只要进入回圈后,就会不断重复执行回圈中的叙述,永远不会停止:

mysql_14_snap_33

4 标签

在使用「BEGIN-END」、「WHILE」、「REPEAT」与「LOOP」四种区块时,都可以为它们设定「标签、label」:

mysql_14_snap_34

标签是由你自己为这些区块取的名字,下列使用「LOOP」回圈来说明标签的设定规则,这个规则同样适用在其它三种区块:

mysql_14_snap_35

在一般的状况下,通常不需要为区块设定标签。 如果为了控制sotred routines的执行流程,才会设定区块的标签。 设定标签以后,就可以搭配使用「LEAVE」叙述来控制流程,下列是「LEAVE」叙述在「LOOP」回圈中的效果:

mysql_14_snap_36

「LEAVE」叙述在其它三种区块中有同样的效果:

mysql_14_snap_37

搭配使用「LEAVE」叙述来控制流程,就可以控制「LOOP」回圈在需要的时候离开。 下列的「summary_loop」范例可以为你从1开始加总到参数指定的数字:

mysql_14_snap_38

设定标签以后,也可以搭配使用「ITERATE」叙述来控制流程,下列是「ITERATE」叙述在「LOOP」回圈中的效果:

mysql_14_snap_39

「ITERATE」叙述不可以使用在「BEGIN-END」区块中,不过它在其它两种区块中有同样的效果:

mysql_14_snap_40

下列的「summary_iterate」范例可以为你从1开始加总到参数指定的数字,不过额外使用「ITERATE」叙述控制,让这个function只会加总奇数:

mysql_14_snap_41

 

翻译自: http://www.codedata.com.tw/database/mysql-tutorial-14-stored-routines-variable-flow/

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十) : Views

MySQL入门 (十一) : Prepared Statements

MySQL入门 (十二) : Stored Routines 入门

MySQL入门 (十四) : Stored Routines 进阶

 

本文: MySQL入门 (十三) : Stored Routines 的变数与流程

 

Loading

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.