實作練習 : PHP + Stored Procedure + Stored Function + Trigger (三)

web - 6月 14, 2024

這個練習是從前面的練習延伸而來 : 


第一個練習在建立訂單時沒有檢查庫存；第二個練習加入「檢查庫存」，但是當庫存不足時，並不方便檢查，也不方便完成後續處理。

因此這篇文章要開始來探討，有哪些地方需要修改? 才能讓無法完成的訂購順利完成?

我們先看看原有資料表單 : 

-- 產品資料表
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Stock INT NOT NULL
);

-- 訂單資料表
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CustomerID INT NOT NULL,
TotalAmount DECIMAL(10, 2) NOT NULL
);

-- 訂單詳細資料表
CREATE TABLE OrderDetails (
OrderDetailID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 客戶資料表
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL
);

-- OrderLogs資料表
CREATE TABLE OrderLogs (
LogID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
LogDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
LogMessage VARCHAR(255),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

可以增加一個表單 BackOrder，用來存放庫存不足時的訂單資料 : 

CREATE TABLE BackOrder (
    OID INT AUTO_INCREMENT PRIMARY KEY,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    qty INT NOT NULL,
    Backorderdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), 
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

如果沒有資料，可以插入範例資料 : 
INSERT INTO Customers (CustomerName) VALUES ('John'), ('Mary'), ('Hellen'); 
INSERT INTO Products (ProductName, Price, Stock) VALUES ('Product A', 100.00, 50), ('Product B', 200.00, 30), ('Product C', 300.00, 20);

檢查一下，此時應該要有6個資料表 : Products、Customers、Orders、OrderDetails、OrderLogs、BackOrder，2個Trigger : UpdateStockAfterOrder、LogOrderAfterInsert，以及1個Stored Function CalculateOrderTotal。



方案一 

如果庫存不足時，就把訂單資訊都寫入BackOrder，當庫存補足時再把BackOrder寫入OrderDetails，並且刪除BackOrder上的資料。

如此一來，BackOrder表單有資料時，就表示庫存不足，就可以將庫存補足。

但是因為BackOrder表單中的OID是AUTO_INCREMENT，如果OID=1的資料被刪除，下一個加入的紀錄OID會是2，也就是被刪除的不能再使用。因此時間久了，OID會越來越大。

如果你過一陣子要reset這個數字的話，你可以使用以下語法 : 

ALTER TABLE example_table AUTO_INCREMENT = 新值;

例如當BackOrder表單已經沒有資料時，你可以用以下指令讓OID從1開始 :

ALTER TABLE example_table AUTO_INCREMENT = 1;


回到主題，先確定UpdateStockAfterOrder、CalculateOrderTotal與LogOrderAfterInsert都已經正確設置。

若無請參考這篇，但是之前CalculateOrderTotal 當訂單都無法成立時，會讓CalculateOrderTotal的v_Total為NULL，讓程序出現錯誤。

我們先修改CalculateOrderTotal為以下，如果為NULL，設為0  : 

DELIMITER //
CREATE FUNCTION CalculateOrderTotal(p_OrderID INT)
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE v_Total DECIMAL(10, 2);
    SELECT IFNULL(SUM(Quantity * Price), 0) INTO v_Total
    FROM OrderDetails
    WHERE OrderID = p_OrderID;
    RETURN v_Total;
END //
DELIMITER ;


然後我們再來修改CreateOrder這個Stored Procedure : 

DELIMITER //
CREATE PROCEDURE CreateOrder(
IN p_CustomerID  INT,
IN p_OrderDetails JSON )

BEGIN
     DECLARE v_OrderID INT;
     DECLARE v_TotalAmount DECIMAL(10, 2) DEFAULT 0.00;
     DECLARE v_ProductID INT;
     DECLARE v_Quantity INT;
     DECLARE v_Price DECIMAL(10, 2);
     DECLARE v_Stock INT;
     DECLARE v_Iterator INT DEFAULT 0;
     DECLARE v_ItemCount INT;    

     SET v_ItemCount = JSON_LENGTH(p_OrderDetails);
     INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
     SET v_OrderID = LAST_INSERT_ID(); 

     WHILE v_Iterator < v_ItemCount DO
          SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
          SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));
     
          -- 查詢產品價格和庫存
          SELECT Price, Stock INTO v_Price, v_Stock FROM Products WHERE ProductID = v_ProductID;     

          -- 檢查庫存是否足夠
          IF v_Stock IS NULL THEN
               INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Product ', v_ProductID, ' does not exist in inventory'));
          ELSEIF v_Stock < v_Quantity THEN
               -- 如果庫存不足夠，寫入log及BackOrder
               INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to order ', v_Quantity, ' but only ', v_Stock, ' available.'));
               INSERT INTO BackOrder (OrderID, ProductID, qty) VALUES (v_OrderID, v_ProductID, v_Quantity);
          ELSE 
               -- 如果庫存足夠，將產品加入訂單並更新總金額
               INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
          END IF;          

          SET v_Iterator = v_Iterator + 1;
     END WHILE;

     UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
     WHERE OrderID = v_OrderID;

END //
DELIMITER ;

我們可以試試呼叫 
Call CreateOrder(1,'[{"ProductID": 1, "Quantity": 200}, {"ProductID": 2, "Quantity": 100}]');

如此一來，當庫存數量不足時，就會出現在BackOrder資料表如下 :



再來寫一個Stored Procedure，把BackOrder中的資料加入OrderDetails中。同樣的，要把資料加入OrderDetails中的時候，一樣要檢查庫存量，比BackOrder中的數量大，才能加入OrderDetails中。

但是要記住，加入OrderDetails中，已經有觸發器自動減去訂購量，因此ProcessBackOrders不需要再處理庫存數據。

以下是當庫存補足後，處理未完成的訂貨程序 ProcessBackOrder : 

DELIMITER //
CREATE PROCEDURE ProcessBackOrders()
BEGIN
    DECLARE v_BackOrderID INT;
    DECLARE v_OrderID INT;
    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;

    DECLARE done INT DEFAULT FALSE;
    DECLARE backOrderCursor CURSOR FOR
        SELECT OID, OrderID, ProductID, qty
        FROM BackOrder;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN backOrderCursor;

    read_loop: LOOP
        FETCH backOrderCursor INTO v_BackOrderID, v_OrderID, v_ProductID, v_Quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 查詢產品價格和庫存
        SELECT Price, Stock INTO v_Price, v_Stock
        FROM Products
        WHERE ProductID = v_ProductID;

        -- 檢查庫存是否足夠
        IF v_Stock >= v_Quantity THEN
            -- 將產品加入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
            
            -- 更新產品庫存 (這邊不用 因為有trigger會執行)
            -- UPDATE Products
            -- SET Stock = Stock - v_Quantity
            -- WHERE ProductID = v_ProductID;

            -- 從BackOrder表中刪除已處理的記錄
            DELETE FROM BackOrder
            WHERE OID = v_BackOrderID;
        ELSE
            -- 更新OrderLogs表，說明庫存仍然不足
            INSERT INTO OrderLogs (OrderID, LogMessage)
            VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to process backorder ', v_Quantity, ' but only ', v_Stock, ' available.'));
        END IF;
    END LOOP;

    CLOSE backOrderCursor;

    UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
    WHERE OrderID = v_OrderID;
 
END //
DELIMITER ;

我們來測試看看 ... 可以使用 Call ProcessBackOrders(); 來處理，然後看看 OrderDetails 如下 : 

Orders 如下 : 






方案二 

方案一的方式，只要訂單數量大於庫存就不處理，還是有點瑕疵。

我們可以在庫存不足時，就把所有的庫存先寫入訂單，然後不足的部分再寫入BackOrder，當庫存補足時再把BackOrder寫入OrderDetails，並且刪除BackOrder上的資料。 

這樣，也是BackOrder表單有資料時，就表示庫存不足，就可以將庫存補足。

CreateOrder這個Stored Procedure 修改如下 :

DELIMITER //
CREATE PROCEDURE CreateOrder(
IN p_CustomerID  INT,
IN p_OrderDetails JSON )
BEGIN
     DECLARE v_OrderID INT;
     DECLARE v_TotalAmount DECIMAL(10, 2) DEFAULT 0.00;
     DECLARE v_ProductID INT;
     DECLARE v_Quantity INT;
     DECLARE v_Price DECIMAL(10, 2);
     DECLARE v_Stock INT;
     DECLARE v_Iterator INT DEFAULT 0;
     DECLARE v_ItemCount INT;    

     SET v_ItemCount = JSON_LENGTH(p_OrderDetails);
     INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
     SET v_OrderID = LAST_INSERT_ID(); 

     WHILE v_Iterator < v_ItemCount DO
          SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
          SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));
     
          -- 查詢產品價格和庫存
          SELECT Price, Stock INTO v_Price, v_Stock FROM Products WHERE ProductID = v_ProductID;     

          -- 檢查庫存是否足夠
          IF v_Stock IS NULL THEN
               INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Product ', v_ProductID, ' does not exist in inventory'));
          ELSEIF v_Stock < v_Quantity THEN
               IF v_Stock > 0 THEN
                    INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Stock, v_Price);
               END IF;
               -- 記錄庫存不足訊息至 OrderLogs
               INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to order ', v_Quantity, ' but only ', v_Stock, ' available.'));
               -- 插入應訂購數量至 BackOrder
               INSERT INTO BackOrder (OrderID, ProductID, qty) VALUES (v_OrderID, v_ProductID, v_Quantity - v_Stock);
          ELSE -- 庫存足夠，將產品加入訂單明細
               INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
          END IF;          

          SET v_Iterator = v_Iterator + 1;
     END WHILE;

     UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
     WHERE OrderID = v_OrderID;

END //
DELIMITER ;

先看看Products表單如下 : 


現在產生訂單 
Call CreateOrder (1, '[{"ProductID": 1, "Quantity": 2}, {"ProductID": 4, "Quantity": 70}]');

Products表單變化如下 :



BackOrder表單如下 :




OrderDetails 表單如下 :





但是因為ProcessBackOrders在處理訂單時，如果庫存小於訂單數量，還是完全不處理，所以再更改如下 : 

當庫存不足時，先把全部庫存數量寫入訂單 orderdetails 差額再寫入backorder

DELIMITER //
CREATE PROCEDURE ProcessBackOrders2 ()
BEGIN
    DECLARE v_BackOrderID INT;
    DECLARE v_OrderID INT;

    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;

    DECLARE done INT DEFAULT FALSE;
    DECLARE backOrderCursor CURSOR FOR
        SELECT OID, OrderID, ProductID, qty
        FROM BackOrder;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN backOrderCursor;

    read_loop: LOOP
        FETCH backOrderCursor INTO v_BackOrderID, v_OrderID, v_ProductID, v_Quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 查詢產品價格和庫存
        SELECT Price, Stock INTO v_Price, v_Stock
        FROM Products
        WHERE ProductID = v_ProductID;

        -- 檢查庫存是否足夠
        IF v_Stock >= v_Quantity THEN
            -- 將產品加入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);

            -- 從BackOrder表中刪除已處理的記錄
            DELETE FROM BackOrder
            WHERE OID = v_BackOrderID;
        ELSE
            -- 如果庫存不足，將可用庫存數量寫入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Stock, v_Price);
            
            -- 更新BackOrder表，將剩餘差額寫回
            UPDATE BackOrder
            SET qty = qty - v_Stock
            WHERE OID = v_BackOrderID;

            -- 更新OrderLogs表，說明庫存仍然不足
            INSERT INTO OrderLogs (OrderID, LogMessage)
            VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Processed ', v_Stock, ' out of ', v_Quantity, '.'));
        END IF;
    END LOOP;

    CLOSE backOrderCursor;

    -- 計算訂單總金額
    UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
    WHERE OrderID = v_OrderID;

END //
DELIMITER ;

方案一還是方案二比較好? 其實不一定，但是可以讓系統有多一種處理庫存不足的選項。

方案一的缺點是如果一直有許多訂單都大於庫存，就會有好多訂單塞著，但是庫存卻沒有消除。方案二的缺點是如果庫存一直沒有補齊，需要時間等待的話，顧客未必願意先訂購部分產品，這樣一來就還要額外處理。方案一的優點就是庫存不足部分可以一次補齊，方案二的優點就是可以先消除庫存。

不管如何，我們這個練習只是提供一些不同的思考方向，以Stored Procedure + Stored Function + Trigger來進行操作，應用在實務時，可能還有很多狀況需要考慮。

例如 : 

(1) 使用鎖定 : 查詢和更新庫存時使用行級鎖定。
(2) 使用交易處理 : 可以避免部分完成的訂單處理導致資料不一致。
(3) 處理錯誤 : 應考慮如何處理錯誤情況，例如資料庫連線失敗、資料不一致等。可以使用 TRY...CATCH 結構來處理錯誤並回滾交易。
(4) 資料驗證 : 在插入訂單和訂單明細之前，驗證輸入資料的正確性和完整性，例如確保客戶ID有效、訂單明細格式正確等。


依照上面的考量，各方案可以再進行修改。


方案一修正版本

CreateOrder如下 : 

DELIMITER //
CREATE PROCEDURE CreateOrder(
    IN p_CustomerID INT,
    IN p_OrderDetails JSON 
)
BEGIN
    DECLARE v_OrderID INT;
    DECLARE v_TotalAmount DECIMAL(10, 2) DEFAULT 0.00;
    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;
    DECLARE v_Iterator INT DEFAULT 0;
    DECLARE v_ItemCount INT;

    -- 捕捉所有錯誤並回滾交易
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        -- 可以添加錯誤日誌記錄
        INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, 'Order processing failed due to an error.');
    END;

    -- 資料驗證
    IF p_CustomerID IS NULL OR p_OrderDetails IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID and Order Details cannot be NULL';
    END IF;

    -- 計算訂單明細項數量
    SET v_ItemCount = JSON_LENGTH(p_OrderDetails);

    -- 開始交易
    START TRANSACTION;

    -- 插入新訂單
    INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
    SET v_OrderID = LAST_INSERT_ID(); 

    -- 處理每一個訂單明細項
    WHILE v_Iterator < v_ItemCount DO
        SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
        SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));

        -- 查詢產品價格和庫存（使用行級鎖定）
        SELECT Price, Stock INTO v_Price, v_Stock FROM Products WHERE ProductID = v_ProductID FOR UPDATE;

        -- 檢查庫存是否足夠
        IF v_Stock IS NULL THEN
            -- 產品不存在
            INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Product ', v_ProductID, ' does not exist in inventory'));
        ELSEIF v_Stock < v_Quantity THEN
            -- 如果庫存不足，寫入log及BackOrder
            INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to order ', v_Quantity, ' but only ', v_Stock, ' available.'));
            INSERT INTO BackOrder (OrderID, ProductID, qty) VALUES (v_OrderID, v_ProductID, v_Quantity);

            -- 如果有部分庫存，先寫入可用的庫存
            IF v_Stock > 0 THEN
                INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Stock, v_Price);
                -- 減少產品庫存
                UPDATE Products SET Stock = 0 WHERE ProductID = v_ProductID;
            END IF;
        ELSE
            -- 如果庫存足夠，將產品加入訂單並更新總金額
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
            -- 減少產品庫存
            UPDATE Products SET Stock = Stock - v_Quantity WHERE ProductID = v_ProductID;
        END IF;

        SET v_Iterator = v_Iterator + 1;
    END WHILE;

    -- 更新訂單總金額
    UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
    WHERE OrderID = v_OrderID;

    -- 提交交易
    COMMIT;
END //
DELIMITER ;


ProcessBackOrder如下 : 

DELIMITER //
CREATE PROCEDURE ProcessBackOrders()
BEGIN
    DECLARE v_BackOrderID INT;
    DECLARE v_OrderID INT;
    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;

    DECLARE done INT DEFAULT FALSE;
    DECLARE backOrderCursor CURSOR FOR
        SELECT OID, OrderID, ProductID, qty
        FROM BackOrder;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 捕捉所有錯誤並回滾交易
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        -- 可以添加錯誤日誌記錄
        INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, 'Processing backorders failed due to an error.');
    END;

    -- 開始交易
    START TRANSACTION;

    OPEN backOrderCursor;

    read_loop: LOOP
        FETCH backOrderCursor INTO v_BackOrderID, v_OrderID, v_ProductID, v_Quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 查詢產品價格和庫存（使用行級鎖定）
        SELECT Price, Stock INTO v_Price, v_Stock
        FROM Products
        WHERE ProductID = v_ProductID
        FOR UPDATE;

        -- 檢查庫存是否足夠
        IF v_Stock >= v_Quantity THEN
            -- 將產品加入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);

            -- 從BackOrder表中刪除已處理的記錄
            DELETE FROM BackOrder
            WHERE OID = v_BackOrderID;
        ELSE
            -- 更新OrderLogs表，說明庫存仍然不足
            INSERT INTO OrderLogs (OrderID, LogMessage)
            VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to process backorder ', v_Quantity, ' but only ', v_Stock, ' available.'));
        END IF;
    END LOOP;

    CLOSE backOrderCursor;

    -- 更新訂單總金額
    UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
    WHERE OrderID = v_OrderID;

    -- 提交交易
    COMMIT;

END //
DELIMITER ;

方案二修正版本

CreateOrder如下 : 

DELIMITER //
CREATE PROCEDURE CreateOrder(
    IN p_CustomerID INT,
    IN p_OrderDetails JSON 
)
BEGIN
    DECLARE v_OrderID INT;
    DECLARE v_TotalAmount DECIMAL(10, 2) DEFAULT 0.00;
    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;
    DECLARE v_Iterator INT DEFAULT 0;
    DECLARE v_ItemCount INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        -- 捕捉所有錯誤並回滾交易
        ROLLBACK;
        -- 可以添加錯誤日誌記錄
        INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, 'Order processing failed due to an error.');
    END;

    -- 資料驗證
    IF p_CustomerID IS NULL OR p_OrderDetails IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID and Order Details cannot be NULL';
    END IF;

    -- 計算訂單明細項數量
    SET v_ItemCount = JSON_LENGTH(p_OrderDetails);

    -- 開始交易
    START TRANSACTION;

    -- 插入新訂單
    INSERT INTO Orders (CustomerID, TotalAmount) VALUES (p_CustomerID, 0.00);
    SET v_OrderID = LAST_INSERT_ID();

    -- 處理每一個訂單明細項
    WHILE v_Iterator < v_ItemCount DO
        SET v_ProductID = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].ProductID')));
        SET v_Quantity = JSON_UNQUOTE(JSON_EXTRACT(p_OrderDetails, CONCAT('$[', v_Iterator, '].Quantity')));

        -- 查詢產品價格和庫存（使用行級鎖定）
        SELECT Price, Stock INTO v_Price, v_Stock FROM Products WHERE ProductID = v_ProductID FOR UPDATE;

        -- 檢查庫存是否足夠
        IF v_Stock IS NULL THEN
            -- 產品不存在
            INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Product ', v_ProductID, ' does not exist in inventory'));
        ELSEIF v_Stock < v_Quantity THEN
            -- 如果庫存不足，寫入log及BackOrder
            INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Tried to order ', v_Quantity, ' but only ', v_Stock, ' available.'));
            INSERT INTO BackOrder (OrderID, ProductID, qty) VALUES (v_OrderID, v_ProductID, v_Quantity);

            -- 如果有部分庫存，先寫入可用的庫存
            IF v_Stock > 0 THEN
                INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Stock, v_Price);
                -- 減少產品庫存
                UPDATE Products SET Stock = 0 WHERE ProductID = v_ProductID;
            END IF;
        ELSE
            -- 如果庫存足夠，將產品加入訂單並更新總金額
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
            -- 減少產品庫存
            UPDATE Products SET Stock = Stock - v_Quantity WHERE ProductID = v_ProductID;
        END IF;

        SET v_Iterator = v_Iterator + 1;
    END WHILE;

    -- 更新訂單總金額
    SET v_TotalAmount = CalculateOrderTotal(v_OrderID);
    UPDATE Orders SET TotalAmount = v_TotalAmount WHERE OrderID = v_OrderID;

    -- 提交交易
    COMMIT;

END //
DELIMITER ;

ProcessBackOrder如下 : 

DELIMITER //
CREATE PROCEDURE ProcessBackOrders2 ()
BEGIN
    DECLARE v_BackOrderID INT;
    DECLARE v_OrderID INT;
    DECLARE v_ProductID INT;
    DECLARE v_Quantity INT;
    DECLARE v_Price DECIMAL(10, 2);
    DECLARE v_Stock INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE backOrderCursor CURSOR FOR
        SELECT OID, OrderID, ProductID, qty
        FROM BackOrder;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        -- 捕捉所有錯誤並回滾交易
        ROLLBACK;
        -- 可以添加錯誤日誌記錄
        INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (v_OrderID, 'Processing backorders failed due to an error.');
    END;

    -- 開始交易
    START TRANSACTION;

    OPEN backOrderCursor;

    read_loop: LOOP
        FETCH backOrderCursor INTO v_BackOrderID, v_OrderID, v_ProductID, v_Quantity;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 查詢產品價格和庫存（使用行級鎖定）
        SELECT Price, Stock INTO v_Price, v_Stock
        FROM Products
        WHERE ProductID = v_ProductID
        FOR UPDATE;

        -- 檢查庫存是否足夠
        IF v_Stock >= v_Quantity THEN
            -- 將產品加入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);

            -- 從BackOrder表中刪除已處理的記錄
            DELETE FROM BackOrder
            WHERE OID = v_BackOrderID;
        ELSE
            -- 如果庫存不足，將可用庫存數量寫入訂單詳細資料表
            INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price)
            VALUES (v_OrderID, v_ProductID, v_Stock, v_Price);
            
            -- 更新BackOrder表，將剩餘差額寫回
            UPDATE BackOrder
            SET qty = qty - v_Stock
            WHERE OID = v_BackOrderID;

            -- 更新OrderLogs表，說明庫存仍然不足
            INSERT INTO OrderLogs (OrderID, LogMessage)
            VALUES (v_OrderID, CONCAT('Not enough inventory for product ', v_ProductID, '. Processed ', v_Stock, ' out of ', v_Quantity, '.'));
        END IF;
    END LOOP;

    CLOSE backOrderCursor;

    -- 計算訂單總金額
    UPDATE Orders SET TotalAmount = CalculateOrderTotal(v_OrderID)
    WHERE OrderID = v_OrderID;

    -- 提交交易
    COMMIT;
END //
DELIMITER ;





[後記]

因為 OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 這個欄位設定，在使用「當時時間」時候，發現時間不正確該怎麼辦? 

以下語法可以知道現在的時區 : 
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

如果不正確，可以使用以下語法修改 :
SET time_zone = "+08:00";
SET GLOBAL time_zone = "+08:00";
SET SESSION time_zone = "+08:00";







標籤 :

張貼留言

0 留言