CODEWITHSUNDEEP
mysqlsqldatabase-cursor
Peter
Peter

Reputation: 16923

MySQL Cursor fetching only one row

My SQL:

CREATE PROCEDURE INV_MIN_PURCHASE_PRICE()
BEGIN
DECLARE done INT;
DECLARE current_inventory_ID INT;
DECLARE cur1 CURSOR FOR SELECT inventory_ID FROM _inventory;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET done = 0;
OPEN cur1;

REPEAT

    FETCH cur1 INTO current_inventory_ID;
    UPDATE _debug SET rows=rows+1;

UNTIL done
END REPEAT;

CLOSE cur1;

END;

When I call this procedure MySQL is fetching only one row (_debug rows are increased by 1). Why?? Is it a bug?

Upvotes: 9

Views: 15046

Answers (3)

nit
nit

Reputation: 11

IF done THEN
         LEAVE main_loop;
ELSE
         ITERATE main_loop;
END IF;

Upvotes: 1

user1987933
user1987933

Reputation: 161

I just had the same problem with a stored procedure in MySQL. It was supposed to get all records from a table that have a null value in a certain column, and then fill that value from another table. However, it stopped after one record:

CREATE PROCEDURE `updateRecord`()
    BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE recordId, newValue INT;
    DECLARE current_record CURSOR FOR SELECT id FROM A where b_id is null;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

    OPEN current_record;

    main_loop: LOOP
        FETCH current_record INTO recordId;

        IF done THEN
             LEAVE main_loop;
        END IF;

        -- fetching some value from table b
        select id into newValue from B where ...

       -- setting new value in record
       update A set b_id = newValue where id = recordId;    

      END LOOP;
    END

The answer is that the "handler for not found" is not only executed if the cursor returned no rows, but also if the select on table B returned no rows. My solution was to use a second variable that was set by the handler and reset the "done" variable after the select:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE, noBfound = TRUE;    

           [...]

           select id into newValue from B where ...

           IF (noBfound = TRUE) THEN 
               SET done = FALSE;
           END IF;

           [...]

Addition: It's obviously possible to do without a second variable by simply resetting the "done" after the select: 

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

           [...]

           select id into newValue from B where ...

           SET done = FALSE;

           [...]

Upvotes: 16

davidethell
davidethell

Reputation: 12018

Have you tried a LOOP instead of the REPEAT construct? Seems to work better in MySQL. Replace the REPEAT to END REPEAT section with:

inv_loop: LOOP
    FETCH cur1 INTO current_inventory_ID;
    IF done = 1 THEN
        LEAVE inv_loop;
    END IF;
    UPDATE _debug SET rows=rows+1;
END LOOP;

Upvotes: 0

Related Questions