CODEWITHSUNDEEP
mysqlcursorprocedure
Robbert
Robbert

Reputation: 6592

MySQL procedure loop stops after one iteration

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?

Upvotes: 1

Views: 3306

Answers (1)

Raushan Kuamr Jha
Raushan Kuamr Jha

Reputation: 463

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

Upvotes: 1

Related Questions