在預存程序中複雜又冗長的 SQL 邏輯中，免不了要搭配流程控制，才能完成需求。本文將在預存程序中，實作 3 種選擇敘述與 3 種迴圈敘述。並搭配測試資料，確認執行結果。

此篇亦轉載到個人部落格。

一、測試資料

以下是本文的測試資料。

員工表，包含員工編號、名字、生日與性別，共四個欄位。

CREATE TABLE `employee` (
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `birthday` DATE,
    `gender` NVARCHAR(10)
);

INSERT INTO `employee` (`emp_id`, `name`, `birthday`, `gender`)
VALUES
(206, "小黃", "1998-10-08", "F"),
(207, "小綠", "1985-09-16", "Male"),
(208, "小黑", "2000-12-19", "男");

銷售紀錄表，包含員工編號、銷售日期與金額，共三個欄位。

CREATE TABLE `sales` (
    `emp_id` INT,
    `date` DATE,
    `total_sales` INT
);

INSERT INTO `sales` (`emp_id`, `date`, `total_sales`)
VALUES
(206, "2023-01-23", 70000),
(206, "2023-09-21", 40000),
(207, "2023-03-09", 24000),
(207, "2023-08-18", 87940),
(208, "2023-07-11", 19800),
(208, "2023-12-04", 43000);

二、IF 選擇

以下的預存程序，是根據銷售金額計算傭金。基本為 2%，達 10 萬為 3%，達 15 萬為 4%，達 20 萬為 5%。

CREATE PROCEDURE `clac_commissions`(
    IN total_sales INT,
    OUT commissions DOUBLE
)
BEGIN
    IF total_sales >= 200000 THEN
        SET commissions = total_sales * 0.05;
    ELSEIF total_sales >= 150000 THEN
        SET commissions = total_sales * 0.04;
    ELSEIF total_sales >= 100000 THEN
        SET commissions = total_sales * 0.03;
    ELSE
        SET commissions = total_sales * 0.02;
    END IF;
END

IF ... ELSEIF ... ELSE 敘述，會由上往下依序判斷。當遇到第一個符合的條件，便會執行對應的邏輯，隨後離開這組 IF。

以下腳本為計算編號為 206 的員工的傭金。

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。

（一）條件式

以下的預存程序，是根據傳入的出生年月日，計算是否達到指定年齡。

CREATE PROCEDURE `is_full_age`(
    IN birthday DATE,
    IN age INT,
    OUT result BOOLEAN
)
BEGIN
    DECLARE now DATE DEFAULT CURDATE();
    DECLARE curYear INT DEFAULT YEAR(now);
    DECLARE curMonth INT DEFAULT MONTH(now);
    DECLARE curDay INT DEFAULT DAY(now);

    CASE
        WHEN curYear - YEAR(birthday) > age THEN
            SET result = true;
        WHEN curYear - YEAR(birthday) < age THEN
            SET result = false;
        WHEN MONTH(birthday) > curMonth  THEN
            SET result = true;
        WHEN MONTH(birthday) < curMonth  THEN
            SET result = false;
        ELSE
            SET result = DAY(birthday) >= curDay;
    END CASE;
END

此處在 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

（二）定值

以下的預存程序，是將傳入的性別文字進行轉換，使其結果為「男」、「女」或「-」其中一個。

CREATE PROCEDURE `format_gender_wording`(
    INOUT gender NVARCHAR(10)
)
BEGIN
    CASE gender
        WHEN "M" THEN
            SET gender = "男";
        WHEN "Male" THEN
            SET gender = "男";
        WHEN "男" THEN
            SET gender = "男";
        WHEN "F" THEN
            SET gender = "女";
        WHEN "Female" THEN
            SET gender = "女";
        WHEN "女" THEN
            SET gender = "女";
        ELSE
            SET gender = "-";
    END CASE;
END

此處在 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 個季度的總銷售金額。並將結果插入到另外的資料表中。

CREATE PROCEDURE `create_seasonal_sales_report_snapshot`(
    IN targetYear INT
)
BEGIN
    DECLARE season INT DEFAULT 1;
    DECLARE sum_sales INT DEFAULT 0;

    -- 建立季度銷售資料表
	CREATE TABLE IF NOT EXISTS `seasonal_sales_report_snapshot`(
        `year` INT,
        `season` INT,
        `total_sales` INT,
        PRIMARY KEY(`year`, `season`)
    );

    -- 清除該年度已有資料
    DELETE FROM `seasonal_sales_report_snapshot`
    WHERE `year` = targetYear;

    -- 執行迴圈，進行統計
    WHILE season <= 4 DO
        SELECT SUM(`total_sales`) INTO sum_sales
        FROM `sales`
        WHERE YEAR(`date`) = targetYear AND
              (MONTH(`date`) BETWEEN (season - 1) * 3 + 1 AND season * 3);

        INSERT INTO `seasonal_sales_report_snapshot`
        VALUES(targetYear, season, IFNULL(sum_sales, 0));

        SET season = season + 1;
        SET sum_sales = 0;
    END WHILE;
END

此處在 WHILE 與 DO 語法之間，撰寫條件式。當符合條件，便會執行迴圈中的邏輯。我們使用「season」變數，控制迴圈是否繼續執行，並於內部的邏輯，調整該變數的值。

以下腳本是統計 2023 年各季的銷售金額。

CALL `seasonal_sales_report_snapshot`(2023);

最後新資料表的內容如下。

五、REPEAT UNTIL 迴圈

這一種迴圈的特色是，必定會先執行一次內部的邏輯，之後再根據條件式，判斷是否要繼續執行下一次。

以下的預存程序，是根據傳入的存款金額、年利率以及存款月數，計算最後的本利和（每月複利一次）。

CREATE PROCEDURE `clac_principle_and_interest`(
    INOUT money INT,
    IN rateOfYear DOUBLE,
    IN numOfMonth INT
)
BEGIN
    DECLARE rateOfMonth DOUBLE DEFAULT rateOfYear / 12;
    REPEAT
        SET money = ROUND(money * (1 + rateOfMonth), 0);
        SET numOfMonth = numOfMonth - 1;
    UNTIL numOfMonth <= 0
    END REPEAT;
END

使用 REPEAT 來宣告迴圈，當內部的邏輯執行完，會根據 UNTIL 語法後方的條件式，決定是否繼續循環。

以下腳本為計算存款 35 萬，年利率 1.5%，存期 18 個月的本利和。

SET @money = 350000;
CALL `clac_principle_and_interest`(@money, 0.015, 18);
SELECT @money; -- 357960

六、LOOP 迴圈

這一種迴圈，本身沒有條件式來判斷是否要繼續執行。而是要在內部邏輯自行判斷是否要離開迴圈。

以下的預存程序，是計算偶數的總和，範圍為 1 到傳入的指定值。

CREATE PROCEDURE `sum_even`(
    IN n INT,
    OUT total INT
)
BEGIN
    -- 變數經初始化後才能使用
    SET total = 0;

    do_sum: LOOP
        -- 若傳入非正數，或遞減到 0 時，就離開迴圈
        IF n <= 0 THEN
            LEAVE do_sum;
        END IF;

        -- 若傳入奇數，減 1 後重新循環
        IF MOD(n, 2) = 1 THEN
            SET n = n - 1;
            ITERATE do_sum;
        END IF;

        SET total = total + n;
        SET n = n - 2;
    END LOOP do_sum;
END

使用 LOOP 來宣告迴圈。並可透過 ITERATE 語法，立即進行下一次循環。透過 LEAVE 語法，可離開迴圈。

此種迴圈需要加上「標籤」（label），用途是在巢狀迴圈中，搭配 ITERATE 和 LEAVE 語法，直接指定要在哪一個迴圈開始新循環，或離開迴圈。示意用法如下。

a: LOOP
    b: LOOP
        IF ... THEN
            -- 外部迴圈新循環
            ITERATE a;
        ELSEIF ... THEN
            -- 離開外部迴圈
            LEAVE a;
        END IF;
    END LOOP b
END LOOP a;

以下腳本為計算 1 ~ 13 所有偶數的和。

CALL `sum_even`(13, @result);
SELECT @result; -- 42

本文介紹的三種迴圈，理論上是可以互相改寫的，而筆者提供的範例也較為單純。若迴圈能夠走訪查詢結果中的資料，那將會有更多應用。下一篇介紹「游標」（cursor），幫助我們做這件事。

今日文章到此結束！
最後宣傳一下自己的部落格，我是「新手工程師的程式教室」的作者，主要發表後端相關文章，請多指教