DECLARE CONTINUE HANDLER FOR NOT FOUND is not working

Hi 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 开发者_JAVA百科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. please some one help me..

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.

---

One of the reason could be that you might have used select into statements inside your cursor again, which sets your handler value to be 1 for those nested select statements inside the cursor loop. You can reset handler value to 0 after select statements used inside and move the handler condition just next to the FETCH command. Example:

...
BEGIN
DECLARE cursor_finished INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_finished = 1;
...
open table_cursor;
get_row: LOOP
  FETCH table_cursor INTO v_variable1, v_variable3;
  IF cursor_finished = 1 THEN 
    LEAVE get_row;
  END IF;
  ....
  Select id into v_id from sometable; -- this sets cursor_finished to 1.
  SET cursor_finished = 0; -- hence reset this value for cursor.
  ....
END

---

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;