[数据库实验六]综合实验

目录

一、实验目的与要求

二、实验内容

三、实验小结

---

实验数据参考：[数据库实验一]数据库和表-CSDN博客

一、实验目的与要求

综合运用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);

运行测试结果截图:orderitems表格如下

2.在orders表(总订单)中新增三个属性original_price、discount、pay，说明如下：

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

源码：

mysql> 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表(总订单)如下

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

• 对总订单表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;

    WHILE(done=0)DO

        FETCH cursor1 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 cursor1;

END;

测试代码：

 CALL modify_order();

1. 运行测试结果截图:orders表中 应付款=原价*折扣

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

源码：

CREATE TRIGGER orderitem_order AFTER INSERT ON orderitems FOR EACH ROW

    UPDATE orders SET original_price=new.quantity*new.item_price+original_price,

    pay=new.quantity*NEW.item_price*discount+pay

    WHERE new.o_num=orders.o_num;

测试代码：

 INSERT INTO orderitems(o_num,o_item,f_id,quantity)VALUES(50010,5,'x0',10);

运行测试结果截图:

（1）.Orderitems表格结果如下：

（2）.Orders表格结果如下：

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

源码：

CREATE TRIGGER deorderitem_order AFTER DELETE ON orderitems FOR EACH ROW

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;

测试代码：

 DELETE FROM orderitems WHERE o_num=50010 AND f_id='x0';

运行测试结果截图:orders表格中数据同步改变

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

源码：

CREATE TRIGGER uporderitem_order AFTER UPDATE on orderitems for EACH ROW

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 new.o_num=orders.o_num;

测试代码：

UPDATE orderitems SET quantity=50 WHERE o_num=50010 and f_id='t2';

运行测试结果截图:

（1）.Orderitems表格结果如下：

（2）.Orders表格结果如下：

三、实验小结

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

问题：在存储过程中使用LOOP无法正常运行

解决过程：改为while循环

2.实验中产生的错误及原因分析

错误：

原因分析：在创建游标时缺乏group by语句