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

2023 iThome 鐵人賽

Software Development

救救我啊我救我！CRUD 工程師的惡補日記系列第 47 篇

15th鐵人賽 mysql

Chikuwa

2024-02-01 00:26:13

1139 瀏覽

分享至

認識了這麼多 SQL 指令，如果我們有經常要執行的工作，一直重複撰寫相同的指令，會相當不便。我們可以設計「預存程序」（Stored Procedure），將多組 SQL 操作封裝起來。如此一來，只要執行呼叫它的指令即可，得到簡化工作的好處。

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

一、測試資料

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

員工表，包含編號、名字與薪水，共三個欄位。

CREATE TABLE `employee` (
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `salary` INT
);

INSERT INTO `employee` (`emp_id`, `name`, `salary`)
VALUES
(206, "小黃", 50000),
(207, "小綠", 29000),
(208, "小黑", 35000);

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

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", 70000),
(207, "2023-03-09", 24000),
(207, "2023-08-18", 87940),
(208, "2023-07-11", 19800),
(208, "2023-12-04", 43000);

當我們想知道員工總銷售金額的排行榜，則範例語法如下。

SELECT `e`.`emp_id`, `e`.`name`, SUM(`total_sales`) AS `sum_sales`
FROM `sales` AS `s`
JOIN `employee` AS `e` ON  `s`.`emp_id` = `e`.`emp_id`
GROUP BY `e`.`emp_id`
ORDER BY `sum_sales` DESC;

查詢結果為：

emp_id	name	sum_sales
206	小黃	140000
207	小綠	111940
208	小黑	62800

二、建立預存程序

（一）建立與呼叫

基於上述查看銷售排行榜的情境，若要經常查詢，那麼撰寫指令會挺麻煩的。

下面的範例，是將這項操作封裝為預存程序。其實概念和使用其他程式語言時，會另外撰寫方法（method）或函數（function）是一樣的。

DELIMITER $$

CREATE PROCEDURE `get_employee_sales_ranking`()
BEGIN
    SELECT `e`.`emp_id`, `name`, SUM(`total_sales`) AS `sum_sales`
    FROM `sales` AS `s`
    JOIN `employee` AS `e` ON  `s`.`emp_id` = `e`.`emp_id`
    GROUP BY `e`.`emp_id`
    ORDER BY `sum_sales` DESC;
END$$

DELIMITER ;

上面使用了 CREATE PROCEDURE 語法，建立出叫做「get_employee_sales_ranking」的預存程序。

而 () 括弧符號是撰寫參數的地方，只是這個例子沒有參數。至於 BEGIN 與 END 定義了撰寫 SQL 指令的範圍。

執行預存程序時，使用 CALL 指令即可。

CALL `get_employee_sales_ranking`();

最後要說明比較特別的 DELIMITER 指令。當在 command line 執行指令時，若 MySQL 讀取到分號，將視為指令已經告一段落，會直接執行。然而建立預存程序的指令，要到 END 那一行才算結束。為了避免被誤認為語法錯誤，故使用 DELIMITER 指令，更改結尾符號為「$$」，之後再改回分號「;」。

（二）其他指令

使用 SHOW CREATE PROCEDURE 指令，可查看當初建立預存程序的定義。

SHOW CREATE PROCEDURE `get_employee_sales_ranking`;

使用 DROP PROCEDURE 指令，可刪除預存程序。

DROP PROCEDURE IF EXISTS `get_employee_sales_ranking`;

三、自定義變數

寫 SQL 時，跟平常寫程式一樣，都會用「變數」這個東西來儲存資料。我們需要先知道如何操作變數，才能進一步探索預存程序的更多使用方式。

（一）在預存程序中

以下的預存程序，是查詢 2023 年第 3 季度的員工銷售排行。

CREATE PROCEDURE `get_employee_sales_ranking_in_season`()
BEGIN
    DECLARE `year` INT DEFAULT 2023;
    DECLARE season INT DEFAULT 3;
    DECLARE startMonth INT;
    DECLARE endMonth INT;

    SET endMonth = season * 3;
    SET startMonth = endMonth - 2;

    SELECT `e`.`emp_id`, `name`, SUM(`total_sales`) AS `total_sales`
    FROM `sales` AS `s`
    JOIN `employee` AS `e` ON  `s`.`emp_id` = `e`.`emp_id`
    WHERE YEAR(`date`) = `year` AND
          (MONTH(`date`) BETWEEN startMonth AND endMonth)
    GROUP BY `e`.`emp_id`
    ORDER BY `total_sales` DESC;
END

上面分別宣告了年度、季度、開始月份與結束月份，共四個變數。

宣告變數的語法為 DECLARE 變數名稱資料型態 DEFAULT 預設值;。其中資料型態和設計資料表欄位時是一樣的。

接著透過 SET 變數名稱 = 值; 語法，能將值賦予給變數，此處透過季度推算出月份期間。最後將這兩個月份參數用於查詢。

（二）在腳本中

所謂的腳本，是一到多組 SQL 指令的集合。比方說本文第一節建立測試資料的那些指令，可以合稱為一個腳本。

由於在腳本中呼叫預存程序時是可以傳入參數的，因此我們需要了解如何在腳本中使用變數。

在腳本中宣告的方式不太一樣，其語法為 SET @變數名稱 = 值;。變數名稱前會加上 @ 符號，且不必定義資料型態。

SET @name := "Vincent";
SET @weight = 63.9;

賦值時，使用 := 或 = 兩種寫法都可以。

我們也能透過查詢語法，將結果中的欄位值賦予給變數。以下的範例是查詢員工資料表，將總人數和總薪資分別賦予給兩個變數。

SELECT COUNT(*), SUM(`salary`) INTO @headcount, @total_salary FROM `employee`;

當想查看變數的值，使用 SELECT @變數名稱 語法即可。

SELECT @headcount, @total_salary;

以上的自定義變數均屬於會話級別（session），只在自身當前對 MySQL 的連線有效。也就是說，當關閉 Workbench 工具，或離開 command line，這些變數會隨之消失。

四、預存程序的參數

使用預存程序時，可以傳入參數，也能回傳結果給呼叫它的地方。

（一）IN 與 OUT 參數

以下的預存程序，是計算某員工在特定年月的傭金（假設訂為銷售金額的 5%）。這裡定義了三個傳入參數，及一個回傳值。

CREATE PROCEDURE `clac_commissions_from_year_month`(
    IN empId INT,
    IN `year` INT,
    IN `month` INT,
    OUT commissions INT
)
BEGIN
    DECLARE sum_sales INT;

    SELECT SUM(`total_sales`) INTO sum_sales
    FROM `sales`
    WHERE `emp_id` = empId AND
          YEAR(`date`) = `year` AND
          MONTH(`date`) = `month`;

    SET commissions = IFNULL(sum_sales, 0) * 0.05;
END

在預存程序名稱後方的括弧中，透過 IN 語法分別定義了員工編號、年度與月份，共三個傳入參數。

接著透過 OUT 語法，定義傭金的回傳參數。事實上，預存程序可定義多個 OUT 參數，意即能有多個回傳值。

在腳本中呼叫預存程序時，除了在 IN 參數的位置傳入值，也會在 OUT 參數的位置寫上要以哪個變數來接收。以下賦予給叫做 result 的自定義變數。

CALL clac_commissions_from_year_month(206, 2023, 1, @result);
SELECT @result; -- 3500

當然，該變數亦能在腳本中事先建立好，此時預存程序將會覆寫該變數的值，看起來就像「傳址呼叫」（called by reference）一樣。

SET @result = 0;
SELECT @result; -- 0

CALL clac_commissions_from_year_month(206, 2023, 1, @result);
SELECT @result;  -- 3500

（二）INOUT 參數

除了使用 IN 與 OUT 語法來定義參數，也能選擇 INOUT，使參數同時具有輸入與輸出的性質。

以下的預存程序，是傳入薪水與總銷售額兩個參數，以 5% 銷售額為傭金，計算最終薪水。

CREATE PROCEDURE `clac_final_salary`(
	INOUT salary INT,
    IN sum_sales INT
)
BEGIN
	SET salary = salary + sum_sales * 0.05;
END

其中「salary」參數被設為 INOUT。呼叫預存程序時，在腳本中應事先定義好變數，再傳入該位置。待其被覆寫後，該變數本身又成為回傳值。

SELECT `salary` INTO @total_salary FROM `employee` WHERE `emp_id` = 206;
SELECT @total_salary; -- 50000

CALL clac_final_salary(@total_salary, 140000);
SELECT @total_salary; -- 57000

從這個情境來看，薪資加上傭金才等於最終薪資。那麼，一開始傳入預存程序的薪資變數，就只是暫時的值而已，並非最後要的。因此筆者認為使用 INOUT 的好處，是能減少參數的宣告。

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