mysql 游标 保存多值_MySQL存储过程，处理多个游标和查询结果

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 ;

解决方案

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.