Hi,
I'm trying to move data from an old schema to a new one and have written a stored proc to handle all of the necessary mapping and such. It appears to be working ok except that the outer cursor doesn't iterate correctly (the inner one does). It grabs the first record ... inserts it twice and then ends the loop. I'm using two cursors that are nested. Here is my code ... I've been working on it for a while and figured that maybe it just needed another set of eyes looking at it. I'm very new at this so it may be the result of sheer ignorance on my part. Whatever the case please advise.
Any constructive help would be very much appreciated:
Many Thanks
- VB Rookie
I'm trying to move data from an old schema to a new one and have written a stored proc to handle all of the necessary mapping and such. It appears to be working ok except that the outer cursor doesn't iterate correctly (the inner one does). It grabs the first record ... inserts it twice and then ends the loop. I'm using two cursors that are nested. Here is my code ... I've been working on it for a while and figured that maybe it just needed another set of eyes looking at it. I'm very new at this so it may be the result of sheer ignorance on my part. Whatever the case please advise.
Any constructive help would be very much appreciated:
Code:
CREATE PROCEDURE `usp_migrate_data`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE no_more_rows_1 INT DEFAULT 0;
DECLARE contribId, donationsMade INT;
DECLARE cur1 CURSOR FOR select * from contributors where contributorid in (9, 937, 1921, 2138, 2199, 2389, 64107, 65847, 66068, 66672, 133721, 133878, 133879, 134115, 136377, 136907, 136995, 137037, 137106, 137109, 137194, 137455, 137945, 137947, 137948, 137949, 137973, 137974, 138559, 138839, 140555);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows_1 = 1;
OPEN cur1;
loop1: LOOP
FETCH cur1 INTO [comma delimited var list here];
IF no_more_rows_1=1 THEN
CLOSE cur1;
LEAVE loop1;
END IF;
IF contribId IS NOT NULL THEN
-- some logic here
END IF;
SELECT COUNT(*) INTO donationsMade
FROM pledges
WHERE contributorID = contribId;
-- IF DONATIONS HAVE BEEN MADE INSERT INTO DONOR_DETAIL
IF donationsMade > 0 THEN
pledgRecs:BEGIN
-- PLEDGES VARIABLES
DECLARE no_more_rows_2 INT DEFAULT 0;
DECLARE PldgID INT;
DECLARE cur2 CURSOR FOR select * from pledges where contributorid = contribId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows_2 = 1;
-- GET DONATION DATA
OPEN cur2;
-- REPEAT
loop2: LOOP
FETCH cur2 INTO [comma delimited var list here];
IF no_more_rows_2=1 THEN
CLOSE cur2;
LEAVE loop2;
END IF;
IF PldgID IS NOT NULL THEN
-- some logic here
END IF;
END LOOP loop2;
END pledgRecs;
END IF;
END IF;
END LOOP loop1;
END;
Many Thanks
- VB Rookie