Try tagging all local variables with something like my_
DELIMITER @@
DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
-- Declare loop constructs --
DECLARE done INT DEFAULT FALSE;
-- Declare Person variables --
DECLARE my_person_id INT;
DECLARE my_era_username VARCHAR(100);
DECLARE my_last_name VARCHAR(50);
DECLARE my_first_name VARCHAR(50);
DECLARE my_email VARCHAR(100);
DECLARE my_email_primary VARCHAR(50);
DECLARE my_degree_id_1 INT;
DECLARE my_degree_id_2 INT;
DECLARE my_member_status INT;
DECLARE my_user_id INT;
DECLARE my_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 my_person_id, my_era_username, my_last_name, my_first_name,
my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
my_member_status;
-- Exit loop if finished --
IF done THEN
LEAVE read_loop;
END IF;
-- Create User --
SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
SET my_user_email = my_email_primary;
ELSE
SET my_user_email = my_email;
END IF;
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
VALUES (my_user_id, my_era_username, SHA1(RAND()), my_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 (my_user_id, my_first_name, my_last_name, my_phone_num,
my_member_status, my_weekly_com, my_monthly_com);
-- Add Degrees --
IF degree_id_1 IS NOT NULL THEN
INSERT INTO member_degrees_held
VALUES (my_user_id, my_degree_id_1);
END IF;
IF degree_id_2 IS NOT NULL THEN
INSERT INTO member_degrees_held
VALUES (my_user_id, my_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 = my_person_id;
END LOOP read_loop;
CLOSE member_cursor;
END; @@
DELIMITER ;
CALL import_members();
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 ???
So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.
This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.
Best Answer
Depends on the cursor type, as documented in
Concepts: Cursors
:As for memory use, all data access goes through the buffer pool, see Memory Manager Architecture. There is no 'close' nor 'deallocation' occurring. Data is brought in memory as needed, it may be referenced by multiple queries, and evicted only when more free memory is required.