【MySQL】使用選擇與迴圈敘述進行流程控制

https://www.flickr.com/photos/8058098@N07/2718571627/

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

此篇內容轉載自本人在 iThome 的文章。

一、測試資料

以下是本文使用的測試資料。

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

+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", "男");

view raw create-and-insert-employee.sql hosted with ❤ by GitHub

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

+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);

view raw create-and-insert-sales.sql hosted with ❤ by GitHub

二、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

view raw proc-clac-commissions.sql hosted with ❤ by GitHub

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。

（一）條件式

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

	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

view raw proc-is-full-age.sql hosted with ❤ by GitHub

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

view raw proc-format-gender-wording.sql hosted with ❤ by GitHub

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

view raw proc-create-seasonal-sales-report-snapshot.sql hosted with ❤ by GitHub

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

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

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

	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

view raw proc-clac-principle-and-interest.sql hosted with ❤ by GitHub

使用 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

view raw proc-sum-even.sql hosted with ❤ by GitHub

使用 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;

view raw nested-loop-iterate-and-leave-concept.sql hosted with ❤ by GitHub

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

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

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

上一篇：【MySQL】設計預存程序以封裝常用操作

下一篇：【MySQL】使用游標（cursor）走訪查詢結果中的資料

新手工程師的程式教室

搜尋此網誌