DELIMITER $$
USE `health`$$
DROP PROCEDURE IF EXISTS `repairDiseaseData`$$
CREATE DEFINER=`root`@`%` PROCEDURE `repairDiseaseData`()
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE isSeeDoctor INT;
DECLARE dId INT;
DECLARE seeDoctorMsg LONGTEXT;
DECLARE rs CURSOR FOR
SELECT id, is_see_doctor, see_doctor_msg
FROM t_disease
WHERE
(
(is_see_doctor = 0 OR is_see_doctor IS NULL)
AND see_doctor_msg IS NOT NULL AND see_doctor_msg != ''
)
OR (is_see_doctor = 1 AND (see_doctor_msg IS NULL OR see_doctor_msg = ''));
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO dId, isSeeDoctor, seeDoctorMsg;
REPEAT
IF NOT Done THEN
IF seeDoctorMsg is null or seeDoctorMsg = ''
THEN
UPDATE t_disease SET is_see_doctor = 0 WHERE id = dId;
ELSE
UPDATE t_disease SET is_see_doctor = 1 WHERE id = dId;
END IF;
END IF;
FETCH NEXT FROM rs INTO dId, isSeeDoctor, seeDoctorMsg;
UNTIL Done END REPEAT;
CLOSE rs;
END$$
DELIMITER ;
CALL repairDiseaseData();
mysql存储过程循环修改每一条数据
最新推荐文章于 2023-11-07 16:32:30 发布