mysql - 从游标中的选择插入 - MySQL

Question

我想知道是否有人可以帮助我解决这个 SP，我不知道为什么，但该过程只在 de Insert 语句中插入 select 的第一个结果(大约 20 行)。我做错了什么吗？

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_tine_tb_temp_month`$$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `db_sumarizados`.`sp_tine_tb_temp_month`()

    BEGIN
  DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b DATE;
  DECLARE cur CURSOR FOR SELECT SITE_NE_NAME, DATE FROM unl_tb_ecl_stat_hh WHERE DATE BETWEEN '2014-04-01' AND '2014-04-30' GROUP BY SITE_NE_NAME, DATE HAVING COUNT(*) < 24;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows := TRUE;

  CREATE TEMPORARY TABLE IF NOT EXISTS sitios_elegidos (
  SITE_NE_NAME CHAR(16), FECHA DATE, HORA TIME
  );

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO a, b;

    IF no_more_rows THEN
      LEAVE read_loop;
    END IF;

    INSERT INTO sitios_elegidos (SITE_NE_NAME, FECHA, HORA) SELECT SITE_NE_NAME, FECHA, HORA FROM time_intervals i LEFT JOIN tb_de_pruebas p ON i.fecha = p.DATE AND i.hora = p.TIME WHERE SITE_NE_NAME = a AND DATE = b;

  END LOOP;

  CLOSE cur;

 END$$

DELIMITER ;

Answer 1

SET no_more_rows = FALSE;

...之前...

END LOOP;

否则，如果您的 INSERT ... SELECT 找不到任何内容，则可能会过早地触发您的 CONTINUE 处理程序并将值设置为 TRUE在光标实际耗尽之前。