MySQL存储过程中临时表不返回结果集

Question

我有一个存储过程，它将具有排名的视图中的行插入到临时表中。

临时表是在运行插入值的游标循环之前创建的，并在循环完成后进行选择的。

但是，当我调用medianMessagesPerWeek();时我收到“错误代码：1329 无数据 - 提取、选择或处理零行”。

如果我将表创建为 MYISAM 表，我可以手动选择该表并确认数据已插入，但存储过程仍然不会给我任何信息。

我在这里错过了什么吗？

DELIMITER $$

USE `yongopal_metrics`$$

DROP PROCEDURE IF EXISTS `medianMessagesPerWeek`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `medianMessagesPerWeek`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tempJoinWeek, tempActiveWeek, rank INT DEFAULT 0;
    DECLARE joinWeek, activeWeek, memberNo, messages INT;
    DECLARE cur CURSOR FOR SELECT * FROM cohortMessagesPerMemberPerWeek;

    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    CREATE TEMPORARY TABLE medianMessagesPerWeek
    (
        joinWeek INT,
        activeWeek INT,
        memberNo INT,
        messages INT,
        rank INT
    ) ENGINE=MEMORY;    

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO joinWeek, activeWeek, memberNo, messages;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF tempJoinWeek = joinWeek AND tempActiveWeek = activeWeek THEN
            SET rank = rank + 1;
        ELSE
            SET tempJoinWeek = joinWeek;
            SET tempActiveWeek = activeWeek;
            SET rank = 1;
        END IF;
        INSERT INTO medianMessagesPerWeek VALUES (joinWeek, activeWeek, memberNo, messages, rank);
    END LOOP;

    CLOSE cur;

    SELECT * FROM medianMessagesPerWeek;
    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    END$$

DELIMITER ;

编辑

这是cohortMessagesPerMemberPerWeek的样子

DELIMITER $$

USE `yongopal_metrics`$$

DROP VIEW IF EXISTS `cohortMessagesPerMemberPerWeek`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cohortMessagesPerMemberPerWeek` AS 
SELECT
  WEEK(`m`.`regDatetime`,0) AS `joinWeek`,
  WEEK(`cd`.`sendDate`,0) AS `activeWeek`,
  `m`.`memberNo` AS `memberNo`,
  COUNT(0)       AS `messages`
FROM (`yongopal`.`chatData` `cd`
   JOIN `yongopal`.`members` `m`
     ON ((`cd`.`sender` = `m`.`memberNo`)))
GROUP BY WEEK(`m`.`regDatetime`,0),WEEK(`cd`.`sendDate`,0),`m`.`memberNo`
ORDER BY WEEK(`m`.`regDatetime`,0),WEEK(`cd`.`sendDate`,0)$$

DELIMITER ;

Answer 1

看起来您缺少 not found cur 光标的处理程序。当 fetch 语句不再返回任何行时（因此您已到达游标声明返回的数据集的末尾），必须将 done 布尔值设置为 true 。

尝试一下：

DELIMITER $

USE `yongopal_metrics`$

DROP PROCEDURE IF EXISTS `medianMessagesPerWeek`$

CREATE DEFINER=`root`@`localhost` PROCEDURE `medianMessagesPerWeek`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tempJoinWeek, tempActiveWeek, rank INT DEFAULT 0;
    DECLARE joinWeek, activeWeek, memberNo, messages INT;
    DECLARE cur CURSOR FOR SELECT * FROM cohortMessagesPerMemberPerWeek;

    declare continue handler for not found set done := true; 

    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    CREATE TEMPORARY TABLE medianMessagesPerWeek
    (
        joinWeek INT,
        activeWeek INT,
        memberNo INT,
        messages INT,
        rank INT
    ) ENGINE=MEMORY;    

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO joinWeek, activeWeek, memberNo, messages;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF tempJoinWeek = joinWeek AND tempActiveWeek = activeWeek THEN
            SET rank = rank + 1;
        ELSE
            SET tempJoinWeek = joinWeek;
            SET tempActiveWeek = activeWeek;
            SET rank = 1;
        END IF;
        INSERT INTO medianMessagesPerWeek VALUES (joinWeek, activeWeek, memberNo, messages, rank);
    END LOOP;

    CLOSE cur;

    SELECT * FROM medianMessagesPerWeek;
    DROP TEMPORARY TABLE IF EXISTS medianMessagesPerWeek;
    END$

DELIMITER ;