MySQL stored procedure, handling multiple cursors and query results

Question

How can I use two cursors in the same routine? If I remove the second cursor declaration and fetch loop everthing works fine. The routine is used for adding a friend in my webapp. It takes the id of the current user and the email of the friend we want to add as a friend, then it checks if the email has a corresponding user id and if no friend relation exists it will create one. Any other routine solution than this one would be great as well.

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

Here is a simple example of how to use two cursors in the same routine:

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 2

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

Answer 3

I have finally written a different 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 ;

I hope this is a better solution, it works fine anyway. Thanks for telling me not to use cursors when not necessary.