数据库6——综合实验-水果商店进阶一

学习内容

一、实验目的与要求：
综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、实验内容：
设计并完成以下实验，要求附上源码（非截图），测试效果截图
1.在订单详情表orderitems插入新订单时自动获得水果价格。
源码：

DELIMITER //
CREATE TRIGGER get_price BEFORE INSERT ON orderitems FOR EACH ROW
BEGIN
    DECLARE g_price DECIMAL(8,2);
    SELECT f_price INTO g_price
    FROM fruits
    WHERE f_id=new.f_id;
    SET new.item_price=g_price;
END;
//
DELIMITER ;

--测试数据
INSERT INTO orderitems(o_num,o_item,f_id,quantity) VALUES (50010,4,'t2',10);

运行测试结果截图:
orderitems的数据如下图所示

2.在orders表(总订单)中新增三个属性original_price、discount、pay，说明如下：
①　属性名分别为original_price（原价格）、discount（折扣）、pay（应付款）数据类型都是decimal(10,2)；
②　“原价格”是表orderitems(订单详情)中同一订单的总金额，该属性要求非空，初值0；
③　“折扣”是当前折扣信息（后续根据VIP等级自动更新，本题用初值1计算），该属性要求非空，初值1；
④　“应付款”是打折后的价格，该属性要求非空，初值0。
源码：

alter table orders
    add original_price decimal(10,2) not null default(0),
    add discount decimal(10,2) not null default(1),
    add pay decimal(10,2) not null default(0);

运行测试结果截图:
orders表的数据如下图所示

设计实验完成以下三项功能
①　对总订单表orders修改已销售总订单
源码：

CREATE PROCEDURE modify_order()
BEGIN
    DECLARE number INT;
    DECLARE new_price DECIMAL(10,2);
    DECLARE done INT DEFAULT(0);
    DECLARE cursor1 CURSOR FOR SELECT o_num,sum(quantity*item_price) FROM orderitems GROUP BY o_num;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN cursor1;
    FETCH cursor1 INTO number,new_price;

    WHILE(done=0) DO
        UPDATE orders SET original_price=new_price WHERE orders.o_num=number;
        UPDATE orders SET pay=(new_price*orders.discount) WHERE orders.o_num=number;
        FETCH cursor1 INTO number,new_price;
    END WHILE;
    CLOSE cursor1;
END;

--调用
CALL modify_order();

运行测试结果截图:
orders的数据如下图所示

②　在订单详情表orderitems新增订单项时同步修改总订单表orders相关属性值
源码：

DELIMITER //
CREATE TRIGGER orderitem_order AFTER INSERT ON orderitems FOR EACH ROW
BEGIN
    UPDATE orders SET 
       original_price=new.quantity*new.item_price+original_price,
       pay=new.quantity*new.item_price*discount+pay
    WHERE orders.o_num=new.o_num;
END;
//
DELIMITER;

--测试数据
INSERT INTO orderitems(o_num,o_item,f_id,quantity) VALUES (50010,5,'t2',10);

运行测试结果截图:
orderitems表的数据如下图所示

orders表的数据如下图所示

③　在订单详情表orderitems删除订单项时同步修改总订单表orders相关属性值
源码：

DELIMITER //
CREATE TRIGGER deorderitem_order AFTER DELETE ON orderitems FOR EACH ROW
BEGIN
    UPDATE orders SET
        original_price=original_price-(old.quantity*old.item_price),
        pay=pay-old.quantity*old.item_price*discount
        WHERE old.o_num=orders.o_num;
END;
//
DELIMITER;

--测试数据
DELETE FROM orderitems WHERE o_num=50010 AND f_id='t2';

运行测试结果截图:
orderitems表的数据如下图所示

orders表的数据如下图所示

④　在订单详情表orderitems修改订单项时同步修改总订单表orders相关属性值
源码：

DELIMITER //
CREATE TRIGGER uporderitem_order AFTER UPDATE on orderitems FOR EACH ROW
BEGIN
    UPDATE orders SET 
        original_price=original_price-(old.quantity*old.item_price)+(new.quantity*new.item_price),
        pay=pay-(old.quantity*old.item_price*discount)+(new.quantity*new.item_price*discount)
        WHERE orders.o_num=new.o_num;
END;
//
DELIMITER;

--测试数据
UPDATE orderitems SET quantity=50 WHERE o_num=50010 and f_id='t2';

运行测试结果截图:
修改前
orderitrems表的数据如下图所示

orders表的数据如下图所示

修改后
orderitrems表的数据如下图所示

orders表的数据如下图所示

三、实验小结
1．实验中遇到的问题及解决过程
（1）触发器中查询水果价格时报错 Table ‘fruitshop.fruitshop’ doesn’t exist
原因分析：在触发器中错误地使用了 FROM fruitshop，其实这里应查询的是水果表 fruits，而不是数据库名。
解决方法：将触发器中 SQL 改为正确的：SELECT f_price INTO g_price FROM fruits WHERE f_id = NEW.f_id;

（2）向 orderitems 插入数据时报外键错误Cannot add or update a child row: a foreign key constraint fails…
原因分析：orderitems.o_num 是外键，引用 orders.o_num，但插入的数据中使用了一个在 orders 表中尚未存在的订单号（例如 50010）。
解决方法：确保在插入订单项前，先在 orders 表中插入该订单号的记录

2．实验中产生的错误及原因分析
（1） 字段为 NULL 报错，错误信息：Column ‘item_price’ cannot be null。
原因：触发器未成功赋值，或插入语句中未提供 item_price，而该字段被设为 NOT NULL。
解决方法：修复触发器逻辑确保能正确赋值，或检查相关数据是否齐全。

（2）字段引用错误
原因：在 UPDATE 中直接使用 new.price 而不是 new.item_price 等正确字段。
解决方法：仔细核查字段名，确保语句中引用字段与表定义一致。

学习感受

本次实验围绕订单管理系统，综合运用了SQL中的变量、游标、触发器、存储过程等高级功能，深入实践了数据库逻辑处理能力。在实验过程中，通过设计触发器实现了订单详情表 orderitems 与总订单表 orders 的数据自动同步，包括插入、修改、删除时对总价与应付金额的自动更新。同时，通过编写存储过程实现了批量更新历史订单的功能。
在实验中遇到了一些典型错误，如触发器中未正确获取商品价格导致字段为 NULL、外键约束失败以及语法细节（如 DELIMITER 的使用）问题。通过逐步调试和验证数据完整性、字段依赖关系等问题，得以顺利解决。
通过本实验，我不仅加深了对SQL语言中触发器和存储过程的理解，而且掌握了数据库中实现业务逻辑自动化的一种有效方式，提高了数据一致性控制的能力和调试分析复杂SQL问题的技巧。