SELECT ... INTO variables set CONTINUE HANDLER FOR NOT FOUND to 1(TRUE)

Question

SELECT ... INTO variables set CONTINUE HANDLER FOR NOT FOUND to 1(TRUE). This was discovered in 5.6.37 version. As example see next code.

DECLARE t_teil_sachnr, t_teil_alt CHAR(7);
DECLARE v_optm_id_sachnr, v_optm_id_alt INTEGER DEFAULT NULL;

DECLARE curs_done INTEGER DEFAULT FALSE;
DECLARE curs_Teilen CURSOR FOR SELECT teil_sachnr, teil_alt FROM my_table WHERE teil_alt <> '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET curs_done = TRUE;

#
OPEN curs_Teilen;

# 
loop_fill: LOOP
  FETCH curs_Teilen INTO t_teil_sachnr, t_teil_alt;
  IF curs_done THEN LEAVE loop_fill; END IF;

  SELECT id INTO v_optm_id_sachnr
    FROM optm_teil_alt_list
    WHERE teil_alt_list = '0000334'; # this SELECT script for exampl only

END LOOP loop_fill;
CLOSE curs_Teilen;

score 0 · Accepted Answer · answered Mar 24 '22 at 19:24

It is enough to check the value of the parameter that the cursor returns. The parameter must be reliable, more precisely, always contain a value, and not contain NULL

Instead of this check

IF curs_done THEN LEAVE loop_fill; END IF;

do this check

IF ISNULL(t_teil_sachnr) THEN LEAVE loop_fill; END IF;

SELECT ... INTO variables set CONTINUE HANDLER FOR NOT FOUND to 1(TRUE)

1 Answers1