mysql - 减少mysql预订时间的存储过程

Question

我有一个图书馆座位预订系统，我正在编写一个存储过程，以根据一定的规则减少学生的小时数，并且应该每天早上 7 点重复。学生可以预订的小时数取决于他或她的声誉。但除了声誉之外，还应考虑当天之前预留的小时数，并从学生当天可以预留的小时数中减少。例如:声誉=5 的学生每天可以预留 8 小时，但如果该学生当天预留 2 小时，则剩余小时数将为 8-2=6。当学生没有任何预订时，存储过程按预期工作，但当学生当天有预订时，它会将小时数设置为 0，这是不应该发生的。我无法指出问题，也没有调试器可供使用。

这是我的代码:

CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateHours`()
BEGIN

DECLARE v_finished1 INTEGER DEFAULT 0;

DECLARE v_email1 varchar(255) DEFAULT "";
DECLARE v_reputation int(11) default 0;

DECLARE v_email2 varchar(255) DEFAULT "";
DECLARE v_stime varchar(255) DEFAULT "";
DECLARE v_etime varchar(255) DEFAULT "";
DECLARE v_priority tinyint(1) DEFAULT 0;
DECLARE v_hours double DEFAULT 0;
DECLARE d_stime datetime;
DECLARE d_etime datetime;
DECLARE checkDate bool DEFAULT false;

DECLARE student cursor for select Email, reputation from students;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished1 = 1;

OPEN student;

get_email1: LOOP

set v_hours=0;
FETCH student INTO v_email1, v_reputation;
IF v_finished1 = 1 THEN 
LEAVE get_email1;
END IF;
block2:
begin

DECLARE v_finished2 INTEGER DEFAULT 0;
DECLARE reservation cursor for select student_email, stime, etime, priority from reservations where str_to_date(stime,'%H:%i:%s %d/%m/%Y') between CURRENT_TIMESTAMP and (CURRENT_TIMESTAMP + interval 1 DAY);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished2 = 1;

OPEN reservation;
get_reservation: LOOP
FETCH reservation INTO v_email2, v_stime, v_etime, v_priority;
IF v_finished2 = 1 THEN 
LEAVE get_reservation;
END IF;

set d_stime = date_format(str_to_date(v_stime,'%H:%i:%s %d/%m/%Y'),'%H:%i:%s %d/%m/%Y');
set d_etime = date_format(str_to_date(v_etime,'%H:%i:%s %d/%m/%Y'),'%H:%i:%s %d/%m/%Y');
if (v_email2 = v_email1 and v_priority = 0) then
set v_hours = v_hours + TIMESTAMPDIFF(HOUR, d_stime, d_etime);
end if;
END LOOP get_reservation; 
CLOSE reservation; 
end block2;
If (v_reputation= 5) then
  update students set ReservationHrsLeft = (8-v_hours) where Email = v_email1;

elseif (v_reputation= 4) then
 if 6>=v_hours then
  update students set ReservationHrsLeft = (6-v_hours) where Email = v_email1;
 else
  update students set ReservationHrsLeft = 0 where Email = v_email1;
  end if;
elseif (v_reputation= 3) then
 if 4>=v_hours then
  update students set ReservationHrsLeft = (4-v_hours) where Email = v_email1;
 else
  update students set ReservationHrsLeft = 0 where Email = v_email1;
  end if;   
elseif (v_reputation= 2) then
 if 2>=v_hours then
  update students set ReservationHrsLeft = (2-v_hours) where Email = v_email1;
 else
  update students set ReservationHrsLeft = 0 where Email = v_email1;
  end if;
elseif v_reputation= 1 then
update students set ReservationHrsLeft = 1 where Email = v_email1;
END if;
END LOOP get_email1;

CLOSE student;
END

Answer 1

问题出在 d_stime 和 d_etime 上。我不应该使用 date_format 函数。