我在MySQL中创建了一个存储过程来遍历一组值,并对每个值执行相同的插入/更新操作。
INSERT/UPDATE操作保持不变,但在我编写的整个脚本中,需要循环的值发生了变化。下面是我正在尝试做的一个简化示例:
DELIMITER $$
CREATE PROCEDURE `log_feed_times`()
BEGIN
DECLARE `no_more_rows` BOOLEAN;
DECLARE `current_animal` INT DEFAULT 0;
DECLARE `animal` CURSOR FOR @query;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
OPEN `animal`;
chores: LOOP
FETCH `animal` INTO `current_animal`;
IF no_more_rows = TRUE THEN
LEAVE move_now;
END IF;
UPDATE farm_animal SET last_feed_time = now() WHERE id = `animal`;
END LOOP chores;
CLOSE `animal`;
END$$
DELIMITER ;
SET @query = "SELECT id FROM farm_animal WHERE name IN ('horse', cow')";
CALL `log_feed_times`;
SET @query = "SELECT id FROM farm_animal WHERE name IN ('pig', 'duck')";
CALL `log_feed_times`;到目前为止,我尝试这样做的结果是错误1064 (42000)。
发布于 2019-09-16 13:30:13
current_animal而不是animal。 UPDATE farm_animal SET last_feed_time = now() WHERE id = current_animal;要离开的循环应该是chores的
IF no_more_rows = TRUE THEN
LEAVE chores;
END IF;https://stackoverflow.com/questions/37038411
复制相似问题