数据库实验六：综合实验-水果商店进阶一

最新推荐文章于 2025-04-28 20:57:20 发布

riri1919

最新推荐文章于 2025-04-28 20:57:20 发布

阅读量692

点赞数 11

文章标签：数据库

本文链接：https://blog.csdn.net/riririch/article/details/147500893

版权

一、实验目的与要求：

综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、实验内容：

设计并完成以下实验，要求附上源码（非截图），测试效果截图

1.在订单详情表orderitems插入新订单时自动获得水果价格。

源码：

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

运行测试结果截图:

INSERT INTO orderitems(o_num,o_item,f_id,quantity) VALUES(50010,4,'t2',10);

在这里插入图片描述

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);

运行测试结果截图:
在这里插入图片描述

3. 设计实验完成以下三项功能

①　对总订单表orders修改已销售总订单（用存储过程）

源码：

CREATE PROCEDURE update_orders()
BEGIN
    DECLARE number INT;
    DECLARE new_price DECIMAL(10,2);
    DECLARE done INT DEFAULT(0);
    -- 定义游标，遍历所有订单，获取订单号和订单总价
    DECLARE order_cursor 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 order_cursor;
    WHILE(done = 0) DO
        -- 更新原价格
         FETCH  order_cursor into number,new_price;
         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;
    END WHILE;
    CLOSE order_cursor;
END;

运行测试结果截图：
在这里插入图片描述

②　在订单详情表orderitems新增订单项时同步修改总订单表orders相关属性值（用触发器）

源码：

CREATE TRIGGER oi_orders
AFTER INSERT ON orderitems
FOR EACH ROW
BEGIN
    -- 更新 original_price 和 pay 字段
    UPDATE orders
    SET 
        original_price = (
            SELECT IFNULL(SUM(quantity * item_price),0)
            FROM orderitems
            WHERE o_num = NEW.o_num
        ),
        pay = (
            SELECT IFNULL(SUM(quantity * item_price),0)
            FROM orderitems
            WHERE o_num = NEW.o_num
        ) * discount
    WHERE o_num = NEW.o_num;
    END;

运行测试结果截图:

INSERT INTO orderitems (o_num,o_item,f_id,quantity) VALUES(50012,5,'A004',10);

在这里插入图片描述

③　在订单详情表orderitems删除订单项时同步修改总订单表orders相关属性值（用触发器）

源码：

CREATE TRIGGER delete_oi_orders
AFTER DELETE ON orderitems
FOR EACH ROW
BEGIN
    -- 更新 original_price 和 pay 字段
    UPDATE orders
    SET 
        original_price = (
            SELECT IFNULL(SUM(quantity * item_price),0)
            FROM orderitems
            -- 在 DELETE 触发器中：只能用 OLD.xxx
            WHERE o_num = OLD.o_num
        ),
        pay = (
            SELECT IFNULL(SUM(quantity * item_price),0)
            FROM orderitems
            WHERE o_num = OLD.o_num
        ) * discount
    WHERE o_num = OLD.o_num;
    END;

运行测试结果截图:

DELETE FROM orderitems
WHERE o_num = 50012 AND o_item = 5;

在这里插入图片描述

④　在订单详情表orderitems修改订单项时同步修改总订单表orders相关属性值（用触发器）

源码：

CREATE TRIGGER update_oi_orders
AFTER UPDATE ON orderitems
FOR EACH ROW
BEGIN
   -- 更新 original_price 和 pay 字段
   UPDATE orders
   SET 
       original_price = (
           SELECT IFNULL(SUM(quantity * item_price),0)
           FROM orderitems
           WHERE o_num = NEW.o_num
       ),
       pay = (
           SELECT IFNULL(SUM(quantity * item_price),0)
           FROM orderitems
           WHERE o_num = NEW.o_num
       ) * discount
   WHERE o_num = NEW.o_num;
   END;

运行测试结果截图:

UPDATE orderitems
SET quantity = 8
WHERE o_num = 50012 AND o_item = 1;

在这里插入图片描述

三、实验小结

1．实验中遇到的问题及解决过程

触发器中使用 NEW / OLD 错误：
在 AFTER DELETE 触发器中错误使用了 NEW，导致提示 “there is no NEW row in ON DELETE trigger”，后更正为使用 OLD 后成功执行。
金额同步更新逻辑出错：
初始版本的金额更新语句未使用 IFNULL 包裹聚合函数 SUM()，在订单详情被全部删除后会导致返回 NULL，进而让 orders 表中的金额也变成空值。通过加上 IFNULL(…, 0) 解决了这个问题。