MySQL MySQL存储过程，处理多个游标和查询结果

MySQL stored procedure, handling multiple cursors and query results

提问by tirithen

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.

如何在同一个例程中使用两个游标？如果我删除第二个游标声明并获取循环，一切正常。该例程用于在我的 web 应用程序中添加朋友。它获取当前用户的 id 和我们要添加为朋友的朋友的电子邮件，然后检查电子邮件是否有相应的用户 id，如果不存在朋友关系，则创建一个。除了这个之外，任何其他常规解决方案也很好。

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 ;

采纳答案by tirithen

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.

我希望这是一个更好的解决方案，无论如何它都可以正常工作。感谢您告诉我在不必要时不要使用游标。

回答by Octavian Vladu

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

回答by Gary

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.

我知道您找到了更好的解决方案，但我相信您最初问题的答案是您需要 SET Done=0; 在两个游标之间，否则第二个游标将在退出循环之前仅获取一条记录，因为前一个处理程序的 Done=1。

回答by OMG Ponies

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

您可以在 WHERE 子句中使用 EXISTS 子句，而不是使用游标来检查记录是否存在：

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 an alteration after reading Paul's comments about the second query, and reversed the logic so the insert won't add duplicates. Ideally this should be handled as a primary key being a compound key (including two or more columns), which would stop the need for checking in code.

在阅读了 Paul 关于第二个查询的评论后，我进行了更改，并颠倒了逻辑，因此插入不会添加重复项。理想情况下，这应该作为主键作为复合键（包括两列或更多列）来处理，这将停止检查代码的需要。

回答by Paul Creasey

Wow, i don't know what to say, please go and read about and learn sql a little, no offense but this is amongst the worst SQL i've ever seem.

哇，我不知道该说什么，请去阅读并学习一点 sql，无意冒犯，但这是我见过的最糟糕的 SQL 之一。

SQL is a set based language, cursors, in general, are bad, there are situations when they are usefull, but they are fairly rare. Your use of cursors here is totally inappropriate.

SQL 是一种基于集合的语言，游标通常很糟糕，在某些情况下它们很有用，但它们很少见。你在这里使用游标是完全不合适的。

Your logic in the second cursor is also flawed since it will select any record which inludes the friend, not just the required friendship.

您在第二个光标中的逻辑也有缺陷，因为它会选择包含朋友的任何记录，而不仅仅是所需的友谊。

If you wanted to fix it you could try giving the second cursor a differant name, but preferably start over.

如果你想修复它，你可以尝试给第二个光标一个不同的名字，但最好重新开始。

Set a compound PK or unique constraint on users_friends, then you don't have to worry about checking for a relationship, then try something like this.

在users_friends上设置一个复合PK或者唯一约束，那么你就不必担心检查关系了，然后尝试这样的事情。

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