数据库作业

基于周一（4月25日）的题目需求，新增一个表，保存学生在各个窗口的消费次数及消费金额：

Create table WConsume(

  CNO        int,

  serviceWNO int,

  num        int,

  amount     decimal(5,2) check( amount>0),

  primary key(CNO,serviceWNO),

  Foreign key (cno) references schoolCard(CNO),

  Foreign key (serviceWNO) references serviceWindows(serviceWNO)

);

/*简易测试数据，运行到系统中，方便测试*/

insert into stuinfo(sno,sname,sclass) values(20001,'张三','软件工程201');

insert into stuinfo(sno,sname,sclass) values(20002,'李四','软件工程202');

insert into stuinfo(sno,sname,sclass) values(20003,'王五','软件工程203');

insert into stuinfo(sno,sname,sclass) values(20004,'赵六','软件工程201');

insert into stuinfo(sno,sname,sclass) values(20005,'钱七','软件工程202');

insert into schoolCard(cno,sno,remainingSum) values(20001,20001,100);

insert into schoolCard(cno,sno,remainingSum) values(20002,20002,300);

insert into schoolCard(cno,sno,remainingSum) values(20003,20003,300);

insert into schoolCard(cno,sno,remainingSum) values(20004,20004,300);

insert into schoolCard(cno,sno,remainingSum) values(20005,20005,300);

insert into serviceWindows(serviceWno,ServiceWname) values(1,'1号窗口');

insert into serviceWindows(serviceWno,ServiceWname) values(2,'2号窗口');

insert into serviceWindows(serviceWno,ServiceWname) values(3,'3号窗口');

insert into serviceWindows(serviceWno,ServiceWname) values(4,'4号窗口');

insert into WConsume(cno,serviceWno,num,amount) values(20001,1,10,200);

编写存储过程p_wconsume，参数为卡号、窗口号、消费金额，实现窗口消费的记录功能，规则要求考虑如下：

1、消费需要检查卡上的余额是否足够；

2、余额需要减去消费金额

3、如果该卡是第一次出现在该窗口消费：则插入相关记录

   如果该卡在该窗口已消费过，则更新消费次数和消费总金额

4、成功或错误都得输出相应信息

5、要求进行异常处理，同时将上面2个步骤进行事务处理，要么全部成功，要么全部失败

充分测试：

call p_wconsume(20010,1,20); #是否能捕获到错误？

call p_wconsume(20001,2,30); #运行2次，每次运行后查看schoolcard、WConsume

call p_wconsume(20001,1,30); #运行2次，每次运行后查看schoolcard、WConsume

/*参考答案*/

drop procedure if exists p_wconsume;

delimiter @@

create procedure p_wconsume(

  _cno int,

  _serviceWno int,

  _amount decimal(5,2)

)

begin

  declare iremainingSum int;

  declare exit handler for SQLEXCEPTION /*遗漏错误处理扣15*/

  BEGIN

    signal sqlstate '45000' set message_text='消费不成功';

    rollback; /*遗漏rollback扣5分，看错误处理是exit还是continue，rollback位置不同*/

  END;

/*处理余额是否足够，更严谨的做法是增加判断iremainingSum是否为空 （10分）*/

set iremainingSum = (select remainingSum from schoolcard where cno=_cno);

if _amount > iremainingSum then

          signal sqlstate '45000' set message_text='余额不足'; #可以用其他方式处理错误信息

end if;

/*处理事务（事务语句10分，包括后面的commit）*/

start transaction;

update schoolcard set remainingSum=remainingSum-_amount where cno=_cno; /*不正确扣10分*/

        if exists(select * from WConsume where cno=_cno and servicewno=_serviceWno) then /*可用其他判断方式，缺少扣10分*/

  /*如果卡在当前窗口已有消费记录，执行update操作，20分*/

          update WConsume set num=num+1,amount=amount+_amount where cno=_cno and servicewno=_serviceWno;

else

  /*如果卡在当前窗口没有消费记录，则执行insert操作，20分*/

  insert into WConsume(cno,serviceWno,num,amount) values(_cno,_serviceWno,1,_amount);

end if;

commit;

end@@

delimiter ;