我在mysql中有一个存储过程，它的suposed应该可以工作，但是它不能。我放了一个名为logs的表来记录这些值，看看那里发生了什么：
DELETE FROM LOGS;
CALL fixRegistrationsGroups;
SELECT * FROM LOGS;这将在logs表中返回：
BEFORE LOOP registrationsIds NULL
registrationsIds NULL
nextRegistrationId NULL正如我在这个问题的标题中所说的，游标中的查询 registrationsWithSameGroupId 返回行，但在从游标获取时不返回行，这是它返回的行的示例：
countRepeated registrationsIds groupId
2 11,1017 6
4 33,35,3463,4363 7
2 32,54 10
7 10,39,40,41,47,48, 51存储过程（怎么了？）：
DELIMITER $$
USE `database-name`$$
DROP PROCEDURE IF EXISTS `fixRegistrationsGroups`$$
CREATE DEFINER=`root`@`%` PROCEDURE `fixRegistrationsGroups`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE participantRegistration INT(11);
DECLARE newAgreementSignatureId INT(11);
DECLARE countRepeated INT;
DECLARE registrationsIds VARCHAR(255);
DECLARE currentGroupId INT;
DECLARE nextRegistrationId INT(11);
DECLARE strLen INT DEFAULT 0;
DECLARE SubStrLen INT DEFAULT 0;
DECLARE registrationsWithSameGroupId CURSOR FOR
SELECT COUNT(groupId) AS countRepeated,
GROUP_CONCAT( registrations.id ) AS registrationsIds,
groupId
FROM registrations
GROUP BY groupId
HAVING countRepeated > 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN registrationsWithSameGroupId;
READ_LOOP: LOOP
FETCH NEXT FROM registrationsWithSameGroupId INTO countRepeated, registrationsIds, currentGroupId;
INSERT INTO LOGS(line) VALUES (CONCAT('BEFORE LOOP registrationsIds ',IF (registrationsIds IS NULL,'NULL',registrationsIds)) );
createAndAssignGroups: LOOP
SET strLen = LENGTH(registrationsIds);
INSERT INTO LOGS(line) VALUES (CONCAT('registrationsIds ',IF (registrationsIds IS NULL,'NULL',registrationsIds)) );
SET nextRegistrationId = SUBSTRING_INDEX(registrationsIds, ',', 1);
INSERT INTO LOGS(line) VALUES (CONCAT('nextRegistrationId ',IF (nextRegistrationId IS NULL,'NULL',nextRegistrationId)) );
INSERT INTO groups (NAME, administratorIdentificationNumber, numberOfParticipants, lastRegistrationDate, lastEventName, eventId)
SELECT
groups.name,
administratorIdentificationNumber,
numberOfParticipants,
lastRegistrationDate,
(SELECT eventName FROM registrations WHERE id = nextRegistrationId) AS lastEventName,
eventId
FROM groups WHERE id = currentGroupId;
UPDATE registrations SET groupId = (SELECT MAX(id) FROM groups) WHERE id = nextRegistrationId;
SET SubStrLen = LENGTH(SUBSTRING_INDEX(registrationsIds, ',', 1));
SET registrationsIds = MID(registrationsIds, SubStrLen, strLen);
IF registrationsIds IS NULL THEN
LEAVE createAndAssignGroups;
END IF;
END LOOP;
IF done THEN
LEAVE READ_LOOP;
END IF;
END LOOP;
CLOSE registrationsWithSameGroupId;
END$$
DELIMITER ;
2条答案
按热度按时间q3aa05251#
您需要检查continue处理程序是否在
FETCH.ep6jt1vc2#
我不认为您正在退出createandassigngroups:loop。假设您的select into游标起作用，我已经从您的结果集中创建了一个表
我修改了你的程序，将调试信息写入debug\表，注解掉了insert into groups和update REGISTRIONS代码，添加了一些代码来运行你的或我的代码，移动了错误位置的游标退出测试，'fixed'set registrationsids代码段和mycode中测试了空字符串而不是空字符串。所以这是修改后的程序。
当我运行它时，mycode的行为和预期的一样，你的代码进入一个无限循环