使用SELECT结果集通过MySQL存储过程运行UPDATE查询 [英] Using SELECT resultset to run UPDATE query with MySQL Stored Procedures

问题描述

我试图了解MySQL存储过程，我想检查用户登录凭据是否有效，如果有效，请更新用户的在线状态:

I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status:

-- DROP PROCEDURE IF EXISTS checkUser;
DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    SELECT id, name FROM users WHERE email = in_email AND password = in_password LIMIT 1;
    -- If result is 1, UPDATE users SET online = 1 WHERE id = "result_id";
END //
DELIMITER ;

如何根据结果集的行数== 1或id不为空来做出if语句?

How Can I make this if-statement based on the resultsets number of rows == 1 or id IS NOT NULL?

推荐答案

DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
    DECLARE tempId INT DEFAULT 0;
    DECLARE tempName VARCHAR(50) DEFAULT NULL;
    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR 
        SELECT id, name FROM users WHERE email = in_email AND password = in_password;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    REPEAT
        FETCH cur INTO tempId, tempName;
        UPDATE users SET online = 1 WHERE id = tempId;
    UNTIL done  = 1 END REPEAT;
    CLOSE cur;

    SELECT tempName;
END //
DELIMITER ;

注意:我尚未对此进行测试. MySQL可能不喜欢针对当前为其打开了游标的表的UPDATE.

NB: I have not tested this. It's possible that MySQL doesn't like UPDATE against a table it currently has a cursor open for.

PS:您应该重新考虑如何存储密码.

PS: You should reconsider how you're storing passwords.

重新评论RETURN与OUT与结果集:

RETURN仅在存储的函数中使用，而不在存储的过程中使用.当您要在另一个SQL表达式中调用例程时，将使用存储的函数.

RETURN is used only in stored functions, not stored procedures. Stored functions are used when you want to call the routine within another SQL expression.

SELECT LCASE( checkUserFunc(?, ?) );

您可以使用OUT参数，但是必须首先声明一个用户变量才能作为该参数传递.然后，您必须选择该用户变量以获取其值.

You can use an OUT parameter, but you have to declare a user variable first to pass as that parameter. And then you have to select that user variable to get its value anyway.

SET @outparam = null;
CALL checkUser(?, ?, @outparam);
SELECT @outparam;

从存储过程返回结果集时，最简单的方法是使用SELECT查询.

When returning result sets from a stored procedure, it's easiest to use a SELECT query.

这篇关于使用SELECT结果集通过MySQL存储过程运行UPDATE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！