MySQL procedure loop stops after one iteration

Question

I am working on a MySQL procedure that creates a summary of one of my tables. The procedure retrieves a dataset and loops through it using a cursor. For each iteration of the loop, the procedure updates another table. The problem I'm having is the loop ends after a single iteration, event though I know that the query I'm running retrieves more than one row.

BEGIN
  # Variable declaration section omitted for brevity


  DECLARE cur CURSOR FOR SELECT
    t.result_id,
    t.athlete_id, t.`first`, t.middle, `last`, t.pref, t.birth,t.uss,
    t.club_id,t.code,t.club_name,
    t.meet_name,t.meet_id,t.`start`,t.`end`,
    MIN(t.time) as time,t.age,t.type
     FROM sometable t GROUP BY club_id ORDER BY time asc,t.start desc,club_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO result_id,athlete_id, first_name, middle, last_name, pref, birth,uss,
    club_id,club_code,club_name,
    meet_name,meet_id,start_date,end_date,
    result_time,age,type;

    IF done=1 THEN
      LEAVE read_loop;
    END IF;


    SET last_time = result_time;

    INSERT INTO toptimes(`result_id`,`club_id`,`agegroup`,`sex`,`distance`,`course`,`stroke`,`data`,`published`)
    VALUES(result_id,club_id,AgeGroupID,sex,distance,course,stroke,json,0);

  END LOOP read_loop;
  CLOSE cur;
END

I'm not clear what the problem is. When I run the select query manually, I get back several rows. Is there a problem running an insert statement inside the loop?

Looking at your code I can see no reason for using a loop when you could just INSERT .. SELECT the rows in one query. Is there some other code required that you have not shown us here? — David Soussan
– David Soussan, Commented Mar 14, 2015 at 6:10
Try moving DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; after LOOP. — shA.t
– shA.t, Commented Mar 14, 2015 at 7:10
Thanks @DavidSoussan, I changed my code to use INSERT .. SELECT and that worked. It also reduced the amount of time the procedure took to run. — Robbert
– Robbert, Commented Mar 15, 2015 at 6:38

Raushan Kuamr Jha · Accepted Answer · 2015-03-14 08:24:32Z

1

Your code chunk looks good to me.

How do you know that it's running only one iteration (i'm not seeing any print or select statement for debug purpose)? Are you getting any error while executing the stored procedure?

I tried to replicate the similar code with "sakila" database (mysql sample db). It's working perfectly. Please check this sql code sample, if it helps you.

DROP PROCEDURE IF EXISTS usp_select_dummy_data ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE usp_select_dummy_data()
BEGIN
-- Declare your variables
    Declare _var_actor_id int default 0;
    DECLARE _var_film_id int default 0;

-- Declare variable used for cursor and loop control
    DECLARE done int;
    DECLARE loop_counter INT DEFAULT 0;

-- Declare the cursor
     DECLARE cur CURSOR FOR
        SELECT
             actor_id, film_id
        FROM film_actor;

-- Declare handlers
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Open the cursor 
   OPEN cur ;

-- Start the loop
  read_loop: LOOP
    FETCH  cur
      INTO  _var_actor_id, _var_film_id ;

    -- break out of the loop if
    -- 1. if there is no rows or
    -- 2.  we've processed them all
     IF done = 1 THEN
       CLOSE cur ;
             LEAVE read_loop ;
      END IF;

    -- Count the number of times looped
       SET loop_counter = loop_counter + 1 ;

    END LOOP read_loop ;

-- print the loop count
 select loop_counter;
END

answered Mar 14, 2015 at 8:24

Raushan Kuamr Jha

4633 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robbert Over a year ago

No idea why my code wasn't looping through all the records. But it is good to know that an update is possible inside a cursor. I ended up using the suggestion in the comments about using insert select. that worked for me and took a lot less time to complete.

Heril Muratovic Over a year ago

@Raushan, loop_counter for me is 1. That's strange. Any solution here?

Collectives™ on Stack Overflow

MySQL procedure loop stops after one iteration

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related