I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person
records, munge it a little bit, and insert it into a few tables on my end. My cursor seems to only be iterating through once, though – here is the code.
DELIMITER @@
DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
-- Declare loop constructs --
DECLARE done INT DEFAULT FALSE;
-- Declare Person variables --
DECLARE person_id INT;
DECLARE era_username VARCHAR(100);
DECLARE last_name VARCHAR(50);
DECLARE first_name VARCHAR(50);
DECLARE email VARCHAR(100);
DECLARE email_primary VARCHAR(50);
DECLARE degree_id_1 INT;
DECLARE degree_id_2 INT;
DECLARE member_status INT;
DECLARE user_id INT;
DECLARE user_email VARCHAR(100);
-- Declare Cursor --
DECLARE member_cursor CURSOR FOR
SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
FROM z_data_person
WHERE member_status IS NOT NULL;
-- Declare Continue Handler --
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN member_cursor;
read_loop: LOOP
-- Fetch data from cursor --
FETCH member_cursor
INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;
-- Exit loop if finished --
IF done THEN
LEAVE read_loop;
END IF;
-- Create User --
SET user_id = SELECT (MAX(uid) + 1) FROM users;
IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
SET user_email = email_primary;
ELSE
SET user_email = email;
END IF;
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());
-- Create Member --
INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);
-- Add Degrees --
IF degree_id_1 IS NOT NULL THEN
INSERT INTO member_degrees_held
VALUES (user_id, degree_id_1);
END IF;
IF degree_id_2 IS NOT NULL THEN
INSERT INTO member_degrees_held
VALUES (user_id, degree_id_2);
END IF;
-- Add Areas of Expertise --
INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
SELECT (SELECT MAX(uid) FROM users), z.specialty_id
FROM z_map_person_specialty AS z
WHERE z.person_id = person_id;
END LOOP read_loop;
CLOSE member_cursor;
END; @@
DELIMITER ;
CALL import_members();
It's as if the SELECT
statement used to populate the cursor is returning no results. I've tested that independently, and it DOES return results correctly. I've tried putting a SELECT person_id
directly below the first FETCH
, and the returns NULL
. So…although this query should return results, they're not making it into the cursor's result set. Am I missing something?
Best Answer
Try tagging all local variables with something like my_
In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).
Sidenote : Where are weekly_com and monthly_com coming from ???