MySQL stored procedure, processing multiple cursors and query results

Question

How can I use two cursors in the same program? If I delete the second cursor declaration and the fetch loop, everything works fine. A routine is used to add a friend to my webapp. It accepts the current user ID and email address of the friend we want to add as a friend, then it checks if the email address has the corresponding user ID, and if there is no friend relationship, it will create it. Any other routine decision besides this would be great.

DROP PROCEDURE IF EXISTS addNewFriend; DELIMITER // CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80)) BEGIN DECLARE tempFriendId INT UNSIGNED DEFAULT 0; DECLARE tempId INT UNSIGNED DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id FROM users WHERE email = inFriendEmail; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; REPEAT FETCH cur INTO tempFriendId; UNTIL done = 1 END REPEAT; CLOSE cur; DECLARE cur CURSOR FOR SELECT user_id FROM users_friends WHERE user_id = tempFriendId OR friend_id = tempFriendId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; REPEAT FETCH cur INTO tempId; UNTIL done = 1 END REPEAT; CLOSE cur; IF tempFriendId != 0 AND tempId != 0 THEN INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, tempFriendId); END IF; SELECT tempFriendId as friendId; END // DELIMITER ; 

Answer 1

I finally wrote another function that does the same thing:

 DROP PROCEDURE IF EXISTS addNewFriend; DELIMITER // CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80)) BEGIN SET @tempFriendId = (SELECT id FROM users WHERE email = inFriendEmail); SET @tempUsersFriendsUserId = (SELECT user_id FROM users_friends WHERE user_id = inUserId AND friend_id = @tempFriendId); IF @tempFriendId IS NOT NULL AND @tempUsersFriendsUserId IS NULL THEN INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, @tempFriendId); END IF; SELECT @tempFriendId as friendId; END // DELIMITER ; 

Hope this is the best solution, it still works great. Thank you for telling me not to use cursors when this is not needed.

Answer 2

Here is a simple example of using two cursors in the same program:

 DELIMITER $$ CREATE PROCEDURE `books_routine`() BEGIN DECLARE rowCountDescription INT DEFAULT 0; DECLARE rowCountTitle INT DEFAULT 0; DECLARE updateDescription CURSOR FOR SELECT id FROM books WHERE description IS NULL OR CHAR_LENGTH(description) < 10; DECLARE updateTitle CURSOR FOR SELECT id FROM books WHERE title IS NULL OR CHAR_LENGTH(title) <= 10; OPEN updateDescription; BEGIN DECLARE exit_flag INT DEFAULT 0; DECLARE book_id INT(10); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1; updateDescriptionLoop: LOOP FETCH updateDescription INTO book_id; IF exit_flag THEN LEAVE updateDescriptionLoop; END IF; UPDATE books SET description = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.' WHERE books.id = book_id; SET rowCountDescription = rowCountDescription + 1; END LOOP; END; CLOSE updateDescription; OPEN updateTitle; BEGIN DECLARE exit_flag INT DEFAULT 0; DECLARE book_id INT(10); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1; updateTitleLoop: LOOP FETCH updateTitle INTO book_id; IF exit_flag THEN LEAVE updateTitleLoop; END IF; UPDATE books SET title = 'Lorem ipsum dolor sit amet' WHERE books.id = book_id; SET rowCountTitle = rowCountTitle + 1; END LOOP; END; CLOSE updateTitle; SELECT 'number of titles updated =', rowCountTitle, 'number of descriptions updated =', rowCountDescription; END 

Answer 3

I know that you have found a better solution, but I believe that the answer to your original question is that you need to set Done = 0; between two cursors, otherwise the second cursor will retrieve only one record before exiting the loop due to Done = 1 from the previous handler.

Answer 4

Instead of using cursors to check for records, you can use the EXISTS clause in the WHERE clause:

 INSERT INTO users_friends (user_id, friend_id) VALUES (inUserId, tempFriendId) WHERE EXISTS(SELECT NULL FROM users WHERE email = inFriendEmail) AND NOT EXISTS(SELECT NULL FROM users_friends WHERE user_id = tempFriendId AND friend_id = tempFriendId); 

I made the changes after I read Paul, commenting on the second request, and changed the logic, so the insert will not add duplicates. Ideally, this should be treated as a primary key, which is a composite key (including two or more columns), which will stop the need for code verification.

Answer 5

Wow, I don’t know what to say, please go read and learn sql a bit, do not be offended, but this is one of the worst SQL I have ever seen.

SQL is a set-based language, cursors are generally bad, there are situations when they are useful, but they are quite rare. Your use of cursors is completely inappropriate here.

Your logic in the second cursor is also erroneous, as it will select any entry that includes a friend, and not just the necessary friendship.

If you want to fix this, you can try giving the second cursor a different name, but it is preferable to start over.

Set a composite PK or a unique constraint for users_friends, then you don’t have to worry about checking relationships, and then try something like this.

 INSERT INTO users_friends SELECT @inUserId, users.user_id FROM users WHERE email = @inFriendEmail