实验六:综合实验-水果商店进阶一
一、实验目的
综合运用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.实验中产生的错误及原因分析
原因分析:
定义变量必须放在游标之前,而我放在了游标后面,所以导致此错误。