員工午餐訂購系統是一個常見的小系統，應該包含[訂餐系統]、[訂單管理系統]、[員工部門管理系統]、[合作店家與菜單管理系統]，讓管理者可以維護員工、部門、合作店家與菜單，讓員工每天可以選擇餐廳與餐點，在中午前彙整所有訂單，每筆訂單要記錄訂購人、餐點、數量、價格與狀態，在月底可以統計每位員工的餐費，作為薪資扣款依據。
系統規模雖然小，但是已經具備線上電商的雛形。
這篇文章將使用 stored procedure、stored function、以及 trigger 來實作[訂餐系統]的程序，這是員工午餐訂購系統的核心作業。
[訂餐系統]需要考量以下幾個需求 :
(1) 員工訂餐後，合作店家必須能夠供應，這就牽涉到餐點庫存管理。
(2) 餐點並非無限量供應，這就牽涉到訂單確認的先後順序。
(3) 一筆訂單有多項餐點時，又有多種處理方式 ~ 部分餐點庫存不足時，是否先確認有庫存的餐點? 還是整筆訂單取消? 還是由員工選擇?
(4) 合作店家供餐時，是否允許任意分批出餐? 還是必須同筆項目一起出餐?
當然你可以很粗造的把需求簡化 : 無限量供餐、以一筆訂單出餐方式。這樣的話，系統就非常好寫，但是在實際使用時就會發生很多問題。
會發生什麼問題 ?
(A) 員工訂餐完成後，過了半小時告知 : 你訂的滷肉飯沒了，請另外再訂。
(B) 餐點送到後，負責人員發現無法核銷，因為訂了兩碗滷肉飯、燙青菜、貢丸湯只有送來一碗滷肉飯跟燙青菜，必須另外用紙寫起來 : 某訂單編號的一碗滷肉飯、貢丸湯尚未送到。
(C) 上述已送到的一碗滷肉飯該不該給員工領餐? 還是等該訂單全部到了再一次領餐? 結果系統無法處理這麼瑣碎的細節。
這麼多細節看起來，前面說員工午餐訂購系統是一個常見的小系統，其實還真不小。
所以這篇文章只先討論員工午餐訂購系統的核心作業~[訂餐系統]。
並且因為 InfinityFree 平台不提供給免費空間使用 stored procedure、stored function、以及 trigger ，因此這個實作只能在 XAMPP 本機上操作。
我們會使用 stored procedure、stored function、以及 trigger 來實作，因此先來看看這些是什麼?
什麼是觸發器 (Trigger) ?
觸發器 (Trigger) : 是一種自動執行的程式，它會在表格上執行指定的INSERT、UPDATE或DELETE操作時被觸發。觸發器可以用來自動執行一些維護數據一致性的任務，例如驗證數據、記錄變更日誌等。
關於 Trigger ，需要知道以下幾個重點 :
(1) 觸發器作用的資料表，不要於觸發器內對該資料表再使用UPDATE/INSERT/DELETE。
(2) 觸發器只能在那個特定的表上生效，無法跨表生效。
(3) 每個表格的同一操作只能有一個BEFORE或一個AFTER觸發器。
(4) 觸發器不能再觸發另一個觸發器。
(5) 觸發器中不能執行動態SQL（即使用PREPARE和EXECUTE語句）。
(6) 要避免在觸發器中執行耗時的操作，以免影響系統性能。
(7) InnoDB支援Transaction及行級鎖(Row-level Lock)，比其他儲存引擎能夠維持資料一致性。
(8) 觸發器執行時不會返回結果，只能執行 SQL 邏輯。
上面說觸發器中不能執行動態SQL，意思是 Trigger 裡要寫「固定 SQL」，不要用字串組 SQL。真的需要動態 SQL，應該放在 Stored Procedure（儲存程序）裡執行，而不是放在 Trigger 裡。因為 Trigger 的原則是不要有不可預期的處理，或執行耗時的操作。
詳細資訊 : 實作練習：MySQL Trigger 觸發器是什麼？如何使用？
什麼是Stored Procedure (預儲程序)? Stored Function (預儲函數)
Stored Procedure (預儲程序)：把一連串的SQL程序步驟儲存起來，最後透過 『call 預儲程序名稱;』來呼叫。
Stored Function (預儲函數)：或稱為使用者定義函數，跟預儲程序很像，不過最後會傳回值。呼叫方式不是 call，而是Select，例如：『select 預儲函數名稱(引數值);』或『select 預儲函數名稱();』。
在使用 Trigger、Stored Procedure、Stored Function 時，有些注意事項 :
(1)儲存程序（Stored Procedure）：
--可以呼叫其他的儲存程序。
--可以呼叫儲存函數。
--可以在觸發器內被呼叫 (註1)。
(2)儲存函數（Stored Function）：
--可以呼叫其他的儲存函數。
--不要再呼叫儲存程序 (註2)。
--可以在觸發器內被呼叫。
(3)觸發器（Trigger）：
--可以呼叫儲存程序 (註1)。
--可以呼叫儲存函數。
--不能包含或呼叫其他觸發器。
--不能使用Commit或是Rollback。
特別說明 :
註1 : Trigger 可以用簡單的 CALL 呼叫 Stored Procedure，但不能呼叫「會回傳資料給 client」或「使用 Dynamic SQL」的 Stored Procedure；Stored Procedure 可以透過 OUT 或 INOUT 參數回傳資料給 Trigger。
註2 : Stored Function（儲存函式）不是絕對不能呼叫 Stored Procedure，而是被呼叫的 Procedure 不能做某些事，例如不能回傳結果集、不能做交易控制等。
Stored Procedure 負責流程。
Stored Function 負責回傳值。
Trigger 負責簡單防呆。
不要把 Trigger 寫成主要流程控制中心，也不要把 Function 寫成隱藏版 Procedure。
簡單來說，Trigger（觸發器）主要是設計來做簡單、明確、可預期的資料檢查或自動處理。在實務上，Trigger 裡面應該盡量避免再呼叫複雜的 Stored Procedure（儲存程序）或 Stored Function（儲存函式），也要避免透過資料異動造成其他 Trigger 連鎖觸發，否則系統行為會變得不容易追蹤，除錯也會變困難。
同樣地，Stored Function（儲存函式）的主要目的，是根據輸入參數計算並回傳一個值。因此在實務設計上，Function 應該保持單純，盡量不要再呼叫 Stored Procedure，尤其不要把複雜商業流程、交易控制或大量資料異動放進 Function 裡。
詳細資訊 :
實作 : 練習使用Stored Procedure以及Stored Function
總整理與實作練習 : MySQL Trigger/Stored Function/Stored Procedure
使用 stored procedure/function、trigger 常見錯誤 :
1. 把所有邏輯都寫在 Trigger 裡
Trigger 應該用於自動補充與紀錄，不適合承擔主要業務流程，主要流程應該放在 Stored Procedure。
2. 訂單明細沒有保留價格快照 如果 order_items 只記錄 dish_id，日後餐點漲價，舊訂單金額可能會被錯誤計算，所以 order_items 應該保留： dish_name_snapshot unit_price_snapshot subtotal
3. 直接刪除訂單 訂單資料通常不建議直接刪除，應該改用狀態欄位，例如： pending confirmed cancelled served 這樣才能保留完整歷史紀錄。
4. Stored Function 做太多資料異動 Stored Function 適合計算與回傳結果，不適合拿來大量更新資料，大量流程操作應該交給 Stored Procedure。
舉幾個例子來說明 :
CREATE TABLE mystock (
stock_id INT AUTO_INCREMENT PRIMARY KEY,
item_id VARCHAR(10) NOT NULL,
myqty INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELIMITER $$
CREATE TRIGGER trg_mystock
BEFORE INSERT ON mystock
FOR EACH ROW
BEGIN
IF NEW.myqty < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存資料不合法：數量不可小於 0';
END IF;
END$$
DELIMITER ;
當我插入資料到 MyStock 時，如果插入負值，就會出現錯誤訊息如下圖 :
如果要從 MyStock 抓出庫存資料，可以建立一個 stored function 如下 :
DELIMITER $$
CREATE FUNCTION fn_get_myqty(
p_item_id VARCHAR(10)
)
RETURNS INT
READS SQL DATA
BEGIN
DECLARE v_myqty INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_myqty = 0;
SELECT myqty
INTO v_myqty
FROM mystock
WHERE item_id = p_item_id;
RETURN IFNULL(v_myqty, 0);
END$$
DELIMITER ;
當我們去抓庫存資料表時，SELECT fn_get-mystock ('1000'); 就可以抓到庫存資料，如下圖 :
要處理訂購，可以寫個 stored procedure 去處理 mystock，如下 :
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_order_mystock$$
CREATE PROCEDURE sp_order_mystock(
IN p_item_id VARCHAR(10),
IN p_order_qty INT
)
BEGIN
DECLARE v_stock_id INT DEFAULT 0;
DECLARE v_myqty INT DEFAULT 0;
DECLARE v_not_found TINYINT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_not_found = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT
0 AS success,
'系統錯誤，交易已取消' AS message;
END;
IF p_order_qty IS NULL OR p_order_qty <= 0 THEN
SELECT
0 AS success,
'訂購數量必須大於 0' AS message,
p_item_id AS item_id,
p_order_qty AS order_qty,
NULL AS remaining_qty;
ELSE
START TRANSACTION;
SELECT stock_id, myqty
INTO v_stock_id, v_myqty
FROM mystock
WHERE item_id = p_item_id
ORDER BY stock_id
LIMIT 1
FOR UPDATE;
IF v_not_found = 1 THEN
ROLLBACK;
SELECT
0 AS success,
'找不到此商品' AS message,
p_item_id AS item_id,
p_order_qty AS order_qty,
NULL AS remaining_qty;
ELSEIF v_myqty < p_order_qty THEN
ROLLBACK;
SELECT
0 AS success,
'庫存不足，無法訂購' AS message,
p_item_id AS item_id,
p_order_qty AS order_qty,
v_myqty AS remaining_qty;
ELSE
UPDATE mystock
SET myqty = myqty - p_order_qty
WHERE stock_id = v_stock_id;
COMMIT;
SELECT
1 AS success,
'訂購成功，庫存已扣除' AS message,
p_item_id AS item_id,
p_order_qty AS order_qty,
v_myqty - p_order_qty AS remaining_qty;
END IF;
END IF;
END$$
DELIMITER ;
當有訂單下來，就可以使用指令 : CALL sp_order_mystock('1000', 3);
就會從 mystock 去扣除訂單數量，如下圖 :
使用了 stored procedure、stored function 的好處就是，以後要處理同樣的事情，就可以重複使用，不需要再另外寫，只要維護好 stored procedure、stored function 即可。
[訂餐系統]的程序就展示如下 :
(1) 開始訂餐前先讓員工選擇訂單處理方式，如下圖 :
(2) 然後就開始從菜單上挑選餐點進入購物車，如下圖 :
(3) 由於員工選擇規則二，因此若選到缺貨的餐點就自動略過，先處理有庫存的餐點，如下圖 :
(4) 如果員工選擇規則三，當遇到缺庫存，就由員工確認要如何處理訂單，如下圖 :
(5) 若員工尚未確認下單，有庫存的餐點會保留五分鐘，如下圖 :
保留五分鐘內，如果員工確認 ~ 則有庫存的餐點訂單會成立，放棄沒有庫存的餐點。
(6) 如果員工選擇規則一，當遇到某餐點缺庫存，就整張訂單失敗，如下圖 :
上面可以看到在三種訂餐規則中，規則一只是用來凸顯規則二、三，因為應該沒人會選這個規則 : 只要一個餐點沒有庫存，沒有取消就不能繼續往後進行。
0 留言