当前位置：首页 > article >正文

存储过程触发器习题整理1

article 2025/3/24 14:08:46

在这里插入图片描述
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 销售表 s
    JOIN 商品表 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
BEGIN
    DECLARE 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)
)
BEGIN
    INSERT 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);