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!