現在要來做個實作練習 : 建立 Trigger 可以自動減去訂單數量，用來維護庫存量；然後建立 Stored Procedure 用來建立訂單，並建立 Trigger 用來記錄建立訂單的log；建立 Stored Function 用來計算訂單總額，並以php呼叫Stored Procedure 以介面來輸入訂單資料。
要達成以上的需求，我們可以怎麼做呢?
(1) 建立 MySQL 資料庫 :
CREATE DATABASE OrderSystem
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
然後進入該資料庫 : USE OrderSystem;
(2) 建立各資料表格
-- 接著建立商品資料表格Products :
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Stock INT NOT NULL
);
-- 建立訂單資料表格Orders :
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
);
-- 建立訂單詳細資料表格OrderDetails :
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)
);
-- 建立客戶資料表格Customers :
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL
);
-- 插入範例資料
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);
(3) 建立各Trigger/Stored Procedure/Stored Function
-- 建立 Trigger 自動減去訂單數量並維護庫存量
DELIMITER //
CREATE TRIGGER UpdateStockAfterOrder
AFTER INSERT ON OrderDetails
FOR EACH ROW
BEGIN
UPDATE Products
SET Stock = Stock - NEW.Quantity
WHERE ProductID = NEW.ProductID;
END //
DELIMITER ;
-- 建立 Stored Procedure 用來建立訂單
為了簡化輸入介面，OrderDetails使用JSON格式
也就是像這樣 [{"ProductID": 1, "Quantity": 2}, {"ProductID": 2, "Quantity": 1}]
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_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')));
SET v_Price = (SELECT Price FROM Products WHERE ProductID = v_ProductID);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, Price) VALUES (v_OrderID, v_ProductID, v_Quantity, v_Price);
SET v_TotalAmount = v_TotalAmount + (v_Quantity * v_Price);
SET v_Iterator = v_Iterator + 1;
END WHILE;
UPDATE Orders
SET TotalAmount = v_TotalAmount
WHERE OrderID = v_OrderID;
END //
DELIMITER ;
-- 建立 Trigger 用來記錄訂單的 log
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)
);
DELIMITER //
CREATE TRIGGER LogOrderAfterInsert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
INSERT INTO OrderLogs (OrderID, LogMessage) VALUES (NEW.OrderID, CONCAT('Order created with ID: ', NEW.OrderID));
END //
DELIMITER ;
-- 建立 Stored Function 用來計算訂單總額
DELIMITER //
CREATE FUNCTION CalculateOrderTotal(p_OrderID INT)
RETURNS DECIMAL(10, 2)
BEGIN
DECLARE v_Total DECIMAL(10, 2);
SELECT SUM(Quantity * Price) INTO v_Total
FROM OrderDetails
WHERE OrderID = p_OrderID;
RETURN v_Total;
END //
DELIMITER ;
(4) 建立PHP程式
使用 PHP 呼叫 Stored Procedure 並輸入訂單資料的介面
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "OrderSystem";
// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢查連線
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$customerID = $_POST["customerID"];
$orderDetails = $_POST["orderDetails"]; // JSON 格式的訂單詳細資料
// 準備和執行 Stored Procedure
$stmt = $conn->prepare("CALL CreateOrder(?, ?)");
$stmt->bind_param("ss", $customerID, $orderDetails);
if ($stmt->execute()) {
echo "Order created successfully!";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
}
$conn->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>Create Order</title>
</head>
<body>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
Customer ID: <input type="text" name="customerID" required><P>
Order Details (JSON format): <textarea rows="10" cols="50" name="orderDetails" required></textarea><P>
<input type="submit" value="Create Order">
</form>
</body>
</html>
呼叫該php如下 :
輸入資料如下 :
CustomerID : 1
Order Details : [{"ProductID": 1, "Quantity": 2}, {"ProductID": 2, "Quantity": 1}]
檢查資料表的結果 :
有發現以上存在哪些問題嗎?
雖然看起來都正常運作，但是有兩個問題，是哪兩個呢?
問題#1 : 上面的TRIGGER UpdateStockAfterOrder，庫存量直接減去訂單數量，沒有檢查庫存量是否大於訂單數量，如果庫存量不足時，系統就會出錯。
因此TRIGGER UpdateStockAfterOrder必須修改。
問題#2 : CreateOrder沒有呼叫CalculateOrderTotal來計算訂單總額，因此需要修改。
再來，如果我們想知道各客戶的訂單細節，應該怎麼下MySQL指令呢?
(1) 請給我MySQL指令，找出名稱中有John的客戶，他訂了那些產品? 請列出產品名稱。
(2) 請給我MySQL指令，列出所有訂單各產品的訂購總數量。
詢問ChatGPT之後，給了以下的答案 :
經過驗證，完全正確 ^^
SELECT p.ProductName
FROM customers c
JOIN orders o ON c.CustomerID = o.CustomerID
JOIN orderdetails od ON o.OrderID = od.OrderID
JOIN products p ON od.ProductID = p.ProductID
WHERE c.CustomerName LIKE '%John%';
SELECT p.ProductName, SUM(od.Quantity) AS TotalQuantity
FROM orderdetails od
JOIN products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;
0 留言