mysqlstored-procedures

DECLARE CONTINUE HANDLER FOR NOT FOUND is not working


I have the following stored procedure

DROP PROCEDURE IF EXISTS `p25`$$

CREATE DEFINER=`root`@`%` PROCEDURE `p25`()
BEGIN
DECLARE b BOOLEAN;
DECLARE a VARCHAR(10);

DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = TRUE;

OPEN cur_1;
lbl:LOOP

IF b = TRUE THEN 
    LEAVE lbl;
END IF;
IF NOT b= TRUE THEN 
        FETCH cur_1 INTO a; 
END IF;

END LOOP;
CLOSE cur_1;
END$$

the select statements actually returns 5 rows but the above procedure after one row is fetched it is not looping through other rows. and the b value is never set to true and it is going to infinite loop because of that.

Is there any problem with my code?

I got the solution for this, thanks for helping me. i think the way i am checking the values is wrong. So now, i want to fetch the values from some other table depending on the row value fetched. how can i print the result like ||current row value || the values fetched from other table|| as a result for all the rows fetched.


Solution

  • Try to change -

    IF NOT b= TRUE THEN 
      FETCH cur_1 INTO a;
    

    with -

    IF b IS NULL THEN 
      FETCH cur_1 INTO a; 
    

    Try this code -

    DECLARE b INT DEFAULT 0;
    DECLARE a VARCHAR(10);
    
    DECLARE cur_1 CURSOR FOR SELECT t FROM sample_table;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    
    OPEN cur_1;
    
    REPEAT
      FETCH cur_1 INTO a;
      IF NOT b THEN
        -- do something:
        ...
        ...
        ...
      END IF;
    UNTIL b END REPEAT;
    
    CLOSE cur_1;