数据库——水果商店进阶

?智能2112杨阳

一、目的与要求：

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

二、内容：

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

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

源码：

delimiter //

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(30003,2,'b3',60);//

运行测试结果截图:

?

1. 在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);//

运行测试结果截图:

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

• 对总订单表orders修改已销售总订单

源码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `modify_order`()

begin

?????? declare number int;

?????? declare new_price decimal(10,2);

? ?declare done int 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 orderitems_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+pay where new.o_num=orders.o_num;

insert into orderitems(o_num,o_item,f_id,quantity). values(30004,2,'b1',5);

运行测试结果截图:

?

?

?

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

源码：

create trigger deleteorderitems_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=30004 and o_item=4;

运行测试结果截图:

?

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

源码：

create trigger updateorderitems_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;

运行测试结果截图:

?

三、小结

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

问题：测试样例是测试失败

解决过程：确保主键唯一的情况下测试样例

2.? 产生的错误及原因分析

错误：插入游标失败

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

3．体会和收获。

本次博客综合之前所学知识来完善水果商店功能，让我对mysql的应用更加得心应手，对mysql编程有了新的理解和认识，能运用mysql解决一些实际问题，总的来说收获满满。