Question

1 宣告与使用变数

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

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

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

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

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

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

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

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

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

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

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

2 判断

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

2.1 IF

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

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

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

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

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

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

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

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

身高范围	性别	标准体重
160~164	男	58

| 165~169 | 男 | 60 |
女 |56 |
| 170~174 | 男 | 64 |
女 |60 |

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

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

2.2 CASE

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

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

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

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

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

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

3 回圈

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

3.1 WHILE

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

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

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

3.2 REPEAT

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

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

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

3.3 LOOP

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

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

4 标签

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

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

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

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

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

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

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

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