Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows

Question

I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' which is also just comprised of a primary key and a Toy's foreign key.

I'm required to use a cursor to loop through all held_toys, inserting Toys which are not yet on sale into the toys_on_sale table, and deleting any Toys from held_toys which are already on sale.

While debugging my cursor, I noticed that the LOOP will only iterate once unless the Toys in held_toys are found (by Toy ID) in the toys_on_sale table. Thus the issue must come down to having the following SELECT statement within my LOOP:

DROP PROCEDURE IF EXISTS sp_Toys_On_Sale
$$
CREATE PROCEDURE `sp_Toys_On_Sale`(inToyIds text)
begin
DECLARE finished INTEGER DEFAULT 0; 
DECLARE heldId BIGINT;
DECLARE heldToyId BIGINT;
DECLARE existingSaleId BIGINT;
DECLARE curHeldToys
 CURSOR FOR
 select ht.ID, ht.TOY_ID
 from held_toys as ht
 where find_in_set(ht.TOY_ID,inToyIds);
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
 OPEN curHeldToys;
 getHeldToys: LOOP
 SET existingSaleId = 0;
 FETCH curHeldToys INTO heldId, heldToyId;
 IF finished = 1 THEN
 LEAVE getHeldToys;
 END IF;
 #LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
 select
 tos.ID
 from toys_on_sale tos
 where tos.TOY_ID = heldToyId INTO existingSaleId;
 IF existingSaleId > 0 THEN
 DELETE FROM held_toys WHERE ID = heldId;
 ELSE
 INSERT INTO toys_on_sale (TOY_ID) VALUES (heldToyId);
 END IF;
 DELETE FROM held_toys WHERE ID = heldId;
 END LOOP getHeldToys;
 CLOSE curHeldToys;
end
$$

I know the issue is in:

#LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
 select
 tos.ID
 from toys_on_sale tos
 where tos.TOY_ID = heldToyId INTO existingSaleId;

But I lack the knowledge of how to embed a SELECT such as this into a CURSOR which can be ignored if it returns zero results. Instead of causing the CURSOR to cease looping..

Any help is appreciated!

• Rick James: That proc can probably be written without a cursor. Maybe one or two DELETEs and INSERTs that act on the entire table at once.

Answer 1

I did some research and found official guidance on this situation. The issue results from the SELECT INTO query as suspected, this is because it is within the scope of the first BEGIN and END statement which has the following HANDLER declared:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Thus, when the SELECT INTO does not find any rows, finished is set to 1 and this is the condition that causes the loop to cease. Thus, to fix this issue the SELECT INTO must be scoped into its own BEGIN and END statement to allow for another HANDLER to be declared to essentially ignore NOT FOUND conditions. This is what that should look like:

BEGIN 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;
 select
 tos.ID
 from toys_on_sale tos
 where tos.TOY_ID = heldToyId INTO existingSaleId;
END;

Now, if the SELECT INTO statement finds no rows it will simple set finished to 0 which does nothing as the loop only terminates if finished is 1. Luckily, our existingSaleId declared variable in the other BEGIN and END statement is still within the scope of this SELECT INTO statement and we can obtain those Ids as expected.