MySQL 進階實戰：自定義函數、視圖、存儲過程、觸發器完整案例

MySQL 進階實戰：自定義函數、視圖、存儲過程、觸發器完整案例

以銷售業務資料庫 sales 為例，完整演示函數、視圖、存儲過程、觸發器、建表查詢的建立與測試，掌握 MySQL 程式化開發核心語法。

業務資料模型

-- 客戶表
CREATE TABLE Customer (
  CusNo CHAR(4) PRIMARY KEY,
  CusName VARCHAR(20),
  Address VARCHAR(50),
  Tel VARCHAR(20)
);

-- 產品表
CREATE TABLE Product (
  ProNo CHAR(5) PRIMARY KEY,
  ProName VARCHAR(50),
  Price DECIMAL(10,2),
  Stocks INT
);

-- 銷售表
CREATE TABLE ProOut (
  SaleDate DATE,
  CusNo CHAR(4),
  ProNo CHAR(5),
  Quantity INT,
  FOREIGN KEY (CusNo) REFERENCES Customer(CusNo),
  FOREIGN KEY (ProNo) REFERENCES Product(ProNo)
);

一、自定義函數：計算產品銷售金額

DELIMITER $$
CREATE FUNCTION sumMoney(pno VARCHAR(10))
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE total_qty INT DEFAULT 0;
  DECLARE unit_price DECIMAL(10,2) DEFAULT 0;
  
  SELECT SUM(po.Quantity) INTO total_qty
  FROM ProOut po JOIN Product pr ON po.ProNo = pr.ProNo
  WHERE pr.ProNo = pno;
  
  SELECT pr.Price INTO unit_price
  FROM Product pr WHERE pr.ProNo = pno;
  
  RETURN IFNULL(total_qty, 0) * IFNULL(unit_price, 0);
END$$
DELIMITER ;

-- 測試
SELECT sumMoney('P0001');

⚠️ 優化建議：單條 SQL 完成，避免多次查詢：

SELECT SUM(po.Quantity) * pr.Price
FROM ProOut po JOIN Product pr ON po.ProNo = pr.ProNo
WHERE pr.ProNo = pno;

二、視圖：產品銷售彙總

CREATE VIEW viewPro AS
SELECT 
  pr.ProNo,
  pr.ProName,
  IFNULL(SUM(po.Quantity), 0) AS total_qty,
  IFNULL(SUM(po.Quantity) * pr.Price, 0) AS total_amount
FROM Product pr
LEFT JOIN ProOut po ON pr.ProNo = po.ProNo
GROUP BY pr.ProNo, pr.ProName, pr.Price;

-- 使用
SELECT * FROM viewPro;

三、存儲過程：客戶採購統計

DELIMITER $$
CREATE PROCEDURE p_Pro(IN cno VARCHAR(10))
BEGIN
  SELECT po.ProNo AS '產品號', SUM(po.Quantity) AS '數量'
  FROM ProOut po
  WHERE po.CusNo = cno
  GROUP BY po.ProNo;
END$$
DELIMITER ;

-- 呼叫
CALL p_Pro('C004');

四、觸發器：銷售同步扣減庫存

❌ 錯誤版本 (AFTER + 修改 NEW)

CREATE TRIGGER t_Stocks AFTER INSERT ON ProOut
FOR EACH ROW
BEGIN
  DECLARE num INT;
  SELECT Stocks INTO num FROM Product WHERE ProNo = NEW.ProNo;
  IF num < NEW.Quantity THEN
    SET NEW.Quantity = num;  -- 錯誤：AFTER 觸發器中不能修改 NEW
  END IF;
  UPDATE Product SET Stocks = Stocks - NEW.Quantity WHERE ProNo = NEW.ProNo;
END;

報錯：Error 1362: Updating of NEW row is not allowed in after trigger

DELIMITER $$
CREATE TRIGGER t_Stocks BEFORE INSERT ON ProOut
FOR EACH ROW
BEGIN
  DECLARE num INT;
  SELECT Stocks INTO num FROM Product WHERE ProNo = NEW.ProNo;
  IF num < NEW.Quantity THEN
    SET NEW.Quantity = num;  -- BEFORE 可修改 NEW，影響後續 INSERT
  END IF;
  UPDATE Product SET Stocks = Stocks - NEW.Quantity WHERE ProNo = NEW.ProNo;
END$$
DELIMITER ;

-- 初始庫存 P0001: 800
INSERT INTO ProOut VALUES('2009-02-25', 'C002', 'P0001', 900);
-- 實際寫入 Quantity = 800 (被截斷至庫存上限)
-- 庫存變為 0

DROP TABLE IF EXISTS Cus;
CREATE TABLE Cus AS
SELECT po.ProNo, SUM(po.Quantity) AS total_qty
FROM ProOut po
WHERE po.CusNo = 'C003' AND YEAR(po.SaleDate) = 2008
GROUP BY po.ProNo;