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

---

一、实验目的与要求：

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

二、实验内容

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

1. 修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。

源码：

CREATE PROCEDURE Update_price()
BEGIN
     declare ff_price decimal(8,2);
     declare ff_id CHAR(10);
     DECLARE done TINYINT DEFAULT 0;
     DECLARE Update_price CURSOR for SELECT f_price,f_id from fruits;
     DECLARE CONTINUE HANDLER FOR NOT found set done=1;
OPEN Update_price;
read_cur:LOOP
      FETCH Update_price into ff_price,ff_id;
      IF done THEN
           leave read_cur;
      END IF;
      UPDATE orderitems set item_price=ff_price
      where orderitems.f_id=ff_id;
      END loop read_cur;
CLOSE Update_price;
END

运行测试结果截图:

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

源码：

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

运行测试结果截图:
测试代码

INSERT into orderitems(o_num,o_item,f_id,quantity) VALUES(30009,2,'a1',5)

3. 在总的订单表orders中新增“原价格”、“折扣”“应付款”三个属性，三个属性要求如下：

①　属性名分别为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修改已销售总订单
源码：
源码:

create PROCEDURE Get_Or_price()
BEGIN
     declare oo_num int (11);
     declare oo_sum_price decimal(10,2);
     DECLARE done TINYINT DEFAULT 0;
     declare Get_Or_price 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 Get_Or_price;
read_cur:LOOP
     FETCH Get_Or_price into oo_num,oo_sum_price;
     IF done THEN LEAVE read_cur;
     End IF;
     UPDATE orders SET original_price=oo_sum_price
     where  orders.o_num=oo_num;
     UPDATE orders SET pay=(oo_sum_price*orders.discount)
     where  orders.o_num=oo_num;
     END loop read_cur;
CLOSE Get_Or_price;
END

运行测试结果截图:

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

CREATE TRIGGER `add_oorderitems` 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(30009,3,'a1',10)

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

CREATE TRIGGER `del_oorderitems` 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=30009 AND o_item=3;

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

CREATE TRIGGER `upd_oorderitems` 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;

运行测试结果截图:

三、实验小结

1．实验中遇到的问题及解决过程
不知道是第几次遇到读不懂题目的问题，原因可能是因为这是综合性的题目，然后自己对于数据库的认识也不够深入，所以有些问题刚开始思考的时候比较局限性，但是就同学之前密切讨论，抓着老师一直问，比如第二题那个自动查询，一开始没想到插入的时候这样插，后来问着问着就会了。

2．实验中产生的错误及原因分析
其实写到一半的时候发现自己的oderitems的表有些o_num是自己插入的，然后orders里面没有的，这就会导致这次实验有些数据不对，所以我就把这两个表的数据再重新对一遍，再重新开始整次实验。这深刻的告诉了我测试数据的时候选择数据的重要性

3．实验体会和收获。
这次是实验六，主要是在实验四和实验五的基础上，也就是游标和触发器的综合，实现了一些生活中常用的问题，让我对于游标和触发器的使用更加深刻的认识。

(小林碎碎念，今天穿了好看的橙色T恤，橙子汽水味道的，夏天也没那么讨厌了。