mysql - 为什么使用游标的MySQL触发器只获取一个值

Question

我在表 AcademicYearTermLevel 上执行 INSERT 后创建了一个触发器，该触发器使用从另一个查询获取的游标值。我想在 while 循环中使用每个游标值并将行插入表 SubjectYearTermLevel。

DROP TRIGGER IF EXISTS after_academicyearterm_insert
DELIMITER $$
CREATE TRIGGER after_academicyearterm_insert
AFTER INSERT 
ON AcademicYearTerm
FOR EACH ROW
BEGIN

  DECLARE temp INT;
  DECLARE subj_done, form_done BOOLEAN DEFAULT FALSE;
  DECLARE s_id INT;
  DECLARE curSubject CURSOR FOR SELECT subject_id FROM Subject;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET subj_done = TRUE;

  SET temp = 1;

  OPEN curSubject;
  cur_subject_loop:   LOOP
  FETCH FROM curSubject INTO s_id;

    IF subj_done THEN
    CLOSE curSubject;
    LEAVE cur_subject_loop;
    END IF;

    WHILE temp <= 6 DO
      INSERT INTO SubjectYearTermLevel VALUES (NULL, s_id, NEW.yearTerm_id, temp);
      SET temp = temp + 1;
    END WHILE


  END LOOP cur_subject_loop;
END$$
DELIMITER ;

但是问题是游标似乎只从 SELECT 查询中获取一个值

Answer 1

愚蠢的错误，我在 FETCH 之外SET temp = 1;，因此在第一个游标值之后，while 循环从未运行。

  -- SET temp = 1;

  OPEN curSubject;
  cur_subject_loop:   LOOP
  FETCH FROM curSubject INTO s_id;
    SET temp = 1;

    IF subj_done THEN
    CLOSE curSubject;
    LEAVE cur_subject_loop;
    END IF;

    1_to_6_temp: WHILE temp <= 6 DO
      INSERT INTO SubjectYearTermLevel VALUES (NULL, s_id, NEW.yearTerm_id, temp, -1);
      SET temp = temp + 1;
    END WHILE 1_to_6_temp;
  -- CLOSE curSubject;
  END LOOP cur_subject_loop;