I have a simple database called & # 39; held_toys & # 39; (a held toy is a toy that is not yet for sale), which consists of a primary key and a foreign key for a toy. I have a second table called "toys_on_sale", which also consists of a primary key and a foreign key of a toy.
I have to use a cursor to scroll through all held_toys, insert unsold toys into the toys_on_sale table and delete toys that have already been sold from held_toys.
When debugging my cursor, I noticed that the infinite loop is repeated only once, unless the Toys in held_toys (after Toy ID) are in the toys_on_sale table. So the problem must be to have the following SELECT statement in 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 problem is:
#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;
However, I lack the knowledge of how to embed a SELECT like this in a CURSOR that can be ignored if it doesn't produce any results. Instead of getting the CURSOR to end the loop.
Any help is appreciated!