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

一、实验目的

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

二、实验内容

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

源码：

CREATE PROCEDURE reprice()

BEGIN
	DECLARE ref_price decimal(6,2);
	DECLARE ref_id CHAR(10);
	DECLARE done TINYINT DEFAULT 0;
	DECLARE reprice CURSOR FOR SELECT f_price,f_id from fruits;
	DECLARE CONTINUE HANDLER FOR NOT found SET done=1;

OPEN reprice;
#开始循环
read_loop:LOOP
	FETCH reprice INTO ref_price,ref_id;
	IF done THEN
		LEAVE read_loop;
	END IF;
	UPDATE orderitems SET item_price=ref_price WHERE orderitems.f_id=ref_id;
	
	END LOOP read_loop;

CLOSE reprice;
END

运行测试结果截图:

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

源码：

#创建触发器
CREATE TRIGGER get_price BEFORE INSERT ON orderitems FOR EACH ROW
BEGIN
  DECLARE g_price DECIMAL(6,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(30012,1,'b2',16)

运行测试结果截图：

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 DEFINER=`root`@`localhost` PROCEDURE `modify_order`()
BEGIN
	DECLARE number INT;
	DECLARE new_price decimal(10,2);
  DECLARE done TINYINT DEFAULT 0;
  DECLARE modify_order 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 modify_order;
read_loop:LOOP
	FETCH modify_order into number,new_price;
	IF done THEN LEAVE read_loop;
	END IF;
	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 LOOP read_loop;
CLOSE modify_order;
END

运行测试结果截图：

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

CREATE TRIGGER oitems_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(30010,4,'b2',5)

运行测试结果截图：


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

CREATE TRIGGER de_oitems_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=30010 AND o_item=4;

运行测试结果截图：


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

CREATE TRIGGER re_oitems_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=10 WHERE o_num=30010 AND o_item=3;

运行测试结果截图：

三、实验小结

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

一开始输入测试用例时出现问题：

提示属性item_price不能为空：

但属性item_price应该由触发器自动补全才对，后来意识到属性f_id应为表中已存在的值。

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

原因分析：
定义变量必须放在游标之前，而我放在了游标后面，所以导致此错误。