Stored Routines 的變數與流程

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範例會有下列的結果：

標準體重會依照身高與性別而不同，所以會有類似下列這樣的表格：

下列是一個依照上列表格所完成的標準體重函式：

完成可以傳回標準體重的「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只會加總奇數：