https://www.flickr.com/photos/8058098@N07/2718571627/
在預存程序中複雜又冗長的 SQL 邏輯中，免不了要搭配流程控制，才能完成需求。本文將在預存程序中，實作 3 種選擇敘述與 3 種迴圈敘述。並搭配測試資料，確認執行結果。
此篇內容轉載自本人在 iThome 的文章。
一、測試資料
以下是本文使用的測試資料。
員工表，包含員工編號、名字、生日與性別，共四個欄位。
銷售紀錄表，包含員工編號、銷售日期與金額，共三個欄位。
二、IF 選擇
以下的預存程序，是根據銷售金額計算傭金。基本為 2%，達 10 萬為 3%，達 15 萬為 4%，達 20 萬為 5%。
IF ... ELSEIF ... ELSE 敘述，會由上往下依序判斷。當遇到第一個符合的條件，便會執行對應的邏輯，隨後離開這組 IF。
SELECT SUM(`total_sales`) INTO @sum_sales FROM `sales` WHERE `emp_id` = 206; SELECT @sum_sales; -- 110000 CALL `clac_commissions`(@sum_sales, @commissions); SELECT @commissions; -- 3300
三、CASE 選擇
使用 CASE 語法時有兩種選擇。一種是類似上述的多重 IF，根據不同條件執行不同的邏輯。另一種是給予一個變數，根據它是什麼值，決定要做的事。
CASE 會由上往下依序判斷，遇到第一個符合的條件，便會執行對應的邏輯，隨後離開這組 CASE。
（一）條件式
以下的預存程序，是根據傳入的出生年月日，計算是否達到指定年齡。
此處在 WHEN 語法後方撰寫條件式，並在 ELSE 語法後方，定義預設的邏輯。與上述的 IF 選擇敘述，是可以互相改寫的。
以下腳本為計算編號為 208 的員工，是否滿 18 歲或 25 歲。
SELECT `birthday` INTO @birthday FROM `employee` WHERE `emp_id` = 208; SELECT @birthday; -- 2000-12-19 CALL `is_full_age`(@birthday, 18, @result); SELECT @result; -- 1 CALL `is_full_age`(@birthday, 25, @result); SELECT @result; -- 0
（二）定值
以下的預存程序，是將傳入的性別文字進行轉換，使其結果為「男」、「女」或「-」其中一個。
此處在 CASE 語法後方提供變數，在 WHEN 語法後方寫上定值。而 ELSE 語法定義了當變數均不符合這些定值時，應該執行的預設邏輯。
以下腳本為查看編號為 207 的員工，其性別文字的轉換結果。
SELECT `gender` INTO @gender FROM `employee` WHERE `emp_id` = 207; SELECT @gender; -- Male CALL `format_gender_wording`(@gender); SELECT @gender; -- 男
四、WHILE DO 迴圈
以下的預存程序，是根據傳入的年度，統計出公司該年 4 個季度的總銷售金額。並將結果插入到另外的資料表中。
此處在 WHILE 與 DO 語法之間，撰寫條件式。當符合條件，便會執行迴圈中的邏輯。我們使用「season」變數，控制迴圈是否繼續執行，並於內部的邏輯，調整該變數的值。
以下腳本是統計 2023 年各季的銷售金額。
CALL `seasonal_sales_report_snapshot`(2023);
最後新資料表的內容如下。
| year | season | total_sales |
|---|---|---|
| 2023 | 1 | 94000 |
| 2023 | 2 | 0 |
| 2023 | 3 | 147740 |
| 2023 | 4 | 43000 |
五、REPEAT UNTIL 迴圈
這一種迴圈的特色是，必定會先執行一次內部的邏輯，之後再根據條件式，判斷是否要繼續執行下一次。
以下的預存程序，是根據傳入的存款金額、年利率以及存款月數，計算最後的本利和（每月複利一次）。
使用 REPEAT 來宣告迴圈，當內部的邏輯執行完，會根據 UNTIL 語法後方的條件式，決定是否繼續循環。
以下腳本為計算存款 35 萬，年利率 1.5%，存期 18 個月的本利和。
SET @money = 350000; CALL `clac_principle_and_interest`(@money, 0.015, 18); SELECT @money; -- 357960
六、LOOP 迴圈
這一種迴圈，本身沒有條件式來判斷是否要繼續執行。而是要在內部邏輯自行判斷是否要離開迴圈。
以下的預存程序，是計算偶數的總和，範圍為 1 到傳入的指定值。
使用 LOOP 來宣告迴圈。並可透過 ITERATE 語法，立即進行下一次循環。透過 LEAVE 語法，可離開迴圈。
此種迴圈需要加上「標籤」（label），用途是在巢狀迴圈中，搭配 ITERATE 和 LEAVE 語法，直接指定要在哪一個迴圈開始新循環，或離開迴圈。示意用法如下。
以下腳本為計算 1 ~ 13 所有偶數的和。
CALL `sum_even`(13, @result); SELECT @result; -- 42
本文介紹的三種迴圈，理論上是可以互相改寫的，而筆者提供的範例也較為單純。若迴圈能夠走訪查詢結果中的資料，那將會有更多應用。下一篇介紹「游標」（cursor），幫助我們做這件事。
留言
張貼留言