46、{blank}設有商品表(商品號，商品名，單價)和銷售表(銷售單據號，商品號，銷售時間，銷售數量，銷售單價)。其中，商品號代表一類商品，商品號、單價、銷售數量和銷售單價均為整型。請編寫查詢某年某商品的銷售總毛利的存儲過程，毛利=銷售數量x(銷售單價-單價)。要求商品號和年份為輸入參數總手利用輸出參數返回，
CREATE TABLE 商品表 (商品號 INT PRIMARY KEY AUTO_INCREMENT, 商品名 VARCHAR(100) NOT NULL UNIQUE, 單價 INT NOT NULL);CREATE TABLE 銷售表 (銷售單據號 INT PRIMARY KEY AUTO_INCREMENT,商品號 INT NOT NULL,銷售時間 DATE NOT NULL,銷售數量 INT NOT NULL,銷售單價 INT NOT NULL,FOREIGN KEY (商品號) REFERENCES 商品表(商品號)
);
INSERT INTO 商品表 (商品名, 單價) VALUES('手機',2000),
('電腦',5000), ('耳機',500);
INSERT INTO 銷售表 (商品號, 銷售時間, 銷售數量, 銷售單價) VALUES(1, '2023-01-01',10,2200),(1, '2023-02-01',15,2300),(2, '2023-03-01',5,5500),(3, '2023-04-01',20,600),(1, '2024-01-01',8,2100);DELIMITER
$$ CREATE PROCEDURE 計算銷售總毛利 (
IN 商品號 INT, IN 年份 INT, OUT 總毛利 INT)
BEGIN -- 聲明變量
DECLARE 毛利 INT DEFAULT 0;
-- 查詢符合條件的銷售記錄
SELECT SUM(銷售數量 * (銷售單價 - 單價)) INTO 毛利
FROM 銷售表 sJOIN 商品表 c ON s.商品號 = c.商品號
WHERE s.商品號 = 商品號 AND YEAR(s.銷售時間) = 年份;
-- 將結果賦值給輸出參數
SET 總毛利 = 毛利;
END
$$ DELIMITER ;CALL 計算銷售總毛利(1,2023,@總毛利);
SELECT @總毛利;
46、mysql 設某數據庫中有商品表(商品號，商品名，進貨價格)，商品號為主碼;銷售表(商品號，銷售時間，銷售數量，銷售價格，本次利潤)，商品號和銷售時間為主碼，銷售價格為本次銷售商品的單價。現要求每當在銷售表中插入前4列數據時(假設一次只插入一行數據)，系統自動計算本次銷售產生的利潤，并將該利潤賦給銷售表的第5列"本次利潤"。請編寫實現上述功能的后觸發型觸發器代碼。 建表名屬性名用英文，造幾條數據，寫存儲過程
以下是實現自動計算銷售利潤的MySQL觸發器及建表語句、示例數據和存儲過程的完整代碼：
一、建表語句
-- 創建商品表
CREATE TABLE product (product_id VARCHAR(10) PRIMARY KEY,product_name VARCHAR(50),purchase_price DECIMAL(10,2)
);-- 創建銷售表
CREATE TABLE sales (product_id VARCHAR(10),sale_time DATETIME,quantity INT,sale_price DECIMAL(10,2),profit DECIMAL(10,2),PRIMARY KEY (product_id, sale_time),FOREIGN KEY (product_id) REFERENCES product(product_id)
);
二、觸發器實現
DELIMITER //
CREATE TRIGGER calculate_profit_trigger
BEFORE INSERT ON sales
FOR EACH ROW
BEGINDECLARE v_purchase_price DECIMAL(10,2);-- 獲取商品進貨價格SELECT purchase_price INTO v_purchase_price FROM product WHERE product_id = NEW.product_id;-- 計算利潤并賦值SET NEW.profit = (NEW.sale_price - v_purchase_price) * NEW.quantity;
END //
DELIMITER ;
關鍵說明：
- 使用
BEFORE INSERT觸發器在插入前計算利潤，直接修改NEW.profit值 - 通過
SELECT...INTO從商品表關聯獲取進貨價格 - 利潤計算公式：
(銷售單價 - 進貨單價) × 銷售數量
三、存儲過程
DELIMITER //
CREATE PROCEDURE InsertSaleRecord(IN p_product_id VARCHAR(10),IN p_sale_time DATETIME,IN p_quantity INT,IN p_sale_price DECIMAL(10,2)
)
BEGININSERT INTO sales (product_id, sale_time, quantity, sale_price)VALUES (p_product_id, p_sale_time, p_quantity, p_sale_price);
END //
DELIMITER ;
四、示例數據
-- 插入商品數據
INSERT INTO product (product_id, product_name, purchase_price) VALUES
('P001', 'Laptop', 500.00),
('P002', 'Phone', 300.00),
('P003', 'Tablet', 200.00);-- 通過存儲過程插入銷售記錄
CALL InsertSaleRecord('P001', '2025-03-22 09:00:00', 2, 600.00);
CALL InsertSaleRecord('P002', '2025-03-22 10:30:00', 3, 400.00);
五、驗證結果
執行以下查詢驗證自動計算的利潤：
SELECT * FROM sales;
預期輸出：
| product_id | sale_time | quantity | sale_price | profit |
|---|---|---|---|---|
| P001 | 2025-03-22 09:00:00 | 2 | 600.00 | 200.00 |
| P002 | 2025-03-22 10:30:00 | 3 | 400.00 | 300.00 |
六、注意事項
- 需要確保商品表中已存在對應商品記錄，否則觸發器會因找不到進貨價格而報錯
- 銷售時間需精確到秒以避免主鍵沖突
- 若需要處理批量插入，需調整觸發器邏輯（當前設計支持單行插入）
以上方案通過觸發器自動完成利潤計算，保證數據一致性的同時減少人工干預，符合數據庫設計規范。
由小藝AI生成<xiaoyi.huawei.com>
