MySQL存储过程,处理多个游标和查询结果 [英] MySQL stored procedure, handling multiple cursors and query results
问题描述
如何在同一例程中使用两个游标?如果我删除了第二个游标声明并获取了循环,一切都将正常工作.该例程用于在我的webapp中添加朋友.它使用当前用户的ID和我们要添加的朋友的电子邮件作为朋友,然后检查电子邮件是否具有相应的用户ID,如果不存在朋友关系,它将创建一个.除此以外的任何其他常规解决方案也都很好.
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.
这篇关于MySQL存储过程,处理多个游标和查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!