BEGIN DECLARE count INT; SELECT COUNT(*) into count from reserve where r_time=NEW.r_time AND d_id=NEW.id; SELECT renshu into @num from work where d_id=NEW.id AND work_start = CONCAT(DATE_FORMAT(NEW.r_time, '%W%H:%i'))); IF (SELECT work_start FROM work WHERE work_start = CONCAT(DATE_FORMAT(r_time, '%W%H:%i'))) THEN INSERT INTO reserve (r_id, p_id, d_id, r_time, r_status,r_out_num ) VALUES (New.r_id, New.p_id, New.d_id, New.r_time, '已预约', null); ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Data does not meet the requirements'; else IF count<=@num then update reserve set New.r_out_num=@num-count doctor_id = NEW.doctor_id AND CONCAT(DATE_FORMAT(NEW.r_time, '%W%H:%i'))); else SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Appointment limit exceeded'; END IF; END有错
时间: 2024-03-28 12:38:03 浏览: 96
sql.rar_exec select_select_sql select from exec_普通sql大全
根据你提供的代码,我发现有一些错误,具体如下:
1. 在第三行 SELECT renshu into @num from work ... 中,应该加上 WHERE 条件来限定查询的范围。
2. 在第四行的 IF 语句中,应该使用 EXISTS 子句来判断表 work 中是否存在符合条件的数据。
3. 在第五行的 INSERT INTO 语句中,应该将 doctor_id = NEW.doctor_id AND CONCAT(DATE_FORMAT(NEW.r_time, '%W%H:%i')) 改为 SET doctor_id = NEW.doctor_id, r_out_num = null。
4. 在第七行的 ELSE IF 语句中,应该使用 UPDATE 语句来更新已有的预约记录。
5. 在第九行的 END IF 后面应该加上一个分号。
修改后的代码如下:
BEGIN
DECLARE count INT;
SELECT COUNT(*) into count from reserve where r_time=NEW.r_time AND d_id=NEW.id;
SELECT renshu into @num from work where d_id=NEW.id AND work_start = CONCAT(DATE_FORMAT(NEW.r_time, '%W%H:%i')) LIMIT 1;
IF EXISTS (SELECT work_start FROM work WHERE work_start = CONCAT(DATE_FORMAT(NEW.r_time, '%W%H:%i'))) THEN
INSERT INTO reserve (r_id, p_id, d_id, r_time, r_status, r_out_num) VALUES (New.r_id, New.p_id, New.d_id, New.r_time, '已预约', null);
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Data does not meet the requirements';
END IF;
IF count <= @num THEN
UPDATE reserve SET r_out_num = @num - count, doctor_id = NEW.doctor_id WHERE r_time = NEW.r_time AND d_id = NEW.id;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Appointment limit exceeded';
END IF;
END;
阅读全文