数据库事务与并发控制

/*学生信息*/
CREATE TABLE stuInfo(
  SNO INT PRIMARY KEY,
  SName VARCHAR(50) NOT NULL,
  SClass VARCHAR(50)
);

/*校园一卡通信息*/
CREATE TABLE schoolCard(
  CNo  INT PRIMARY KEY,
  SNO  INT NOT NULL,
  cardDate DATETIME,
  remainingSum DECIMAL(7,2) DEFAULT 0 CHECK(remainingSum>=0),
  FOREIGN KEY (SNO) REFERENCES stuInfo(SNO)
);

/*食堂窗口信息*/
CREATE TABLE serviceWindows(
  serviceWNo INT PRIMARY KEY,
  serviceWName NVARCHAR(20) NOT NULL,
  serviceWAdmin NVARCHAR(10),
  serviceWATele CHAR(13)
);

/*食堂就餐消费记录表*/
CREATE TABLE foodConsume(
  CNO        INT,
  serviceWNO INT,
  consumeDT  DATETIME,
  amount     DECIMAL(5,2) CHECK( amount>0),
  PRIMARY KEY (CNO,serviceWNO,consumeDT),
  FOREIGN KEY (cno) REFERENCES schoolCard(CNO),
  FOREIGN KEY (serviceWNO) REFERENCES serviceWindows(serviceWNO)
);

CREATE INDEX idx_foodConsumeAmount ON foodConsume (amount DESC);

ALTER TABLE stuInfo  ADD MobilePhone CHAR(13);

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);

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 ;

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

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

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