jeudi 18 décembre 2014

MySQL Stored Procedure Cursor Loop Stops After 1 Iteration


Moving this question from Stack Overflow to DBA.


I created a stored procedure to update missing data in a table, using the MySQL documentation as a guide. It works, however it seems to stop after one iteration. I can call the procedure again, and it will run through once successfully again then stop. Here is a simplified (and seemingly redundant) example of my tables.


Table departments



+----+-----+-----+----------+
|ntid|fname|lname|department|
|----|-----|-----|----------|
| | | | |
| | | | |
| | | | |
| | | | |
+---------------------------+


Table statistics



+----------+----+-----+-----+----------+
|otherstuff|user|fname|lname|department|
|----------|----|-----|-----|----------|
| | | | | |
| | | | | |
| | | | | |
| | | | | |
+--------------------------------------+


When statistics gets updated daily, sometimes it has rows where fname, lname, and department are NULL due to departments only being updated weekly. So I created a stored procedure to be called when the departments table is updated to fix any NULL values in statistics.


Stored Procedure:



DELIMITER $$

CREATE PROCEDURE `update_statistics_users`()
BEGIN
#declare variables
DECLARE stat_ntid VARCHAR(10);
DECLARE done INT DEFAULT 0;
DECLARE fName, lName, dept VARCHAR(100);

#declare cursor
DECLARE cur1 CURSOR FOR
SELECT DISTINCT(user)
FROM statistics
WHERE fname IS NULL;

#declare handle
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

#open cursor
OPEN cur1;

the_loop: LOOP
#get the values of each column into variables
FETCH cur1 INTO stat_ntid;
IF done=1 THEN
LEAVE the_loop;
END IF;

#select user info into variables
SELECT first_name, last_name, department
INTO fName, lName, dept
FROM departments
WHERE ntid=stat_ntid
ORDER BY date DESC
LIMIT 1;

UPDATE statistics
SET
fname=fName, lname=lName, department=dept
WHERE user=stat_ntid AND first_name IS NULL;

END LOOP the_loop;
CLOSE cur1;
SET done = 0;
END


So the point behind it is to only return one of each user name that has a NULL first/last name into the cursor. Each iteration will find that username (or ntid) in departments and update all rows with that user name in statistics with the first, last, and department found.


My results are that it updates all rows with the first user name found, then stops with a Success.


I tried finding others with this issue, but every answer I have found so far I have tried with the same results.





Aucun commentaire:

Enregistrer un commentaire