MySQL函数 - 错误代码：1415不允许从函数返回结果集 [英] MySQL Function - Error Code: 1415 Not allowed to return a result set from a function

问题描述

我不想返回结果集，我也不知道我在这里做错了什么。
MySQL 5.5

$ $ $ $ $ $ $ $ $


CREATE FUNCTION CheckAccount（
i_username varchar（50）
）RETURNS integer

BEGIN

DECLARE v_validUserId int;
DECLARE v_validMembership int;
DECLARE o_Status integer;

SELECT vvalidUserId = u.UserId
FROM用户u
其中u.Username = i_username;

IF（v_validUserId IS NULL）THEN
SET o_Status = 2; - 无效的用户名
ELSE
SET o_Status = 1; - 好的
END IF;


（o_Status！= 2）THEN
SELECT v_validMembership = 1
FROM用户u
在um.UserId = u.userId上加入UserMemberships um
JOIN会员资格m.MembershipId = um.MembershipId
WHERE um.MembershipExpireDateTime> CURDATE（）
和u.UserId = v_validUserId;

IF（v_validMembership IS NULL）THEN
SET o_Status = 3; - 会员无效
END IF;
END IF;

RETURN o_status;

END $$
DELIMITER;

任何帮助都将不胜感激！

解决方案

我不确定您是否可以通过这种方式分配变量，请尝试为您的选择使用 INTO 语句。例如：

  SELECT 
 u.UserId INTO vvalidUserId 
 FROM 
用户u 
 WHERE 
 u.Username = i_username; 
  

I am not trying to return a result set and I have no idea what I'm doing wrong here. MySQL 5.5

delimiter $$

CREATE FUNCTION CheckAccount(
    i_username varchar(50)
) RETURNS integer

BEGIN

    DECLARE v_validUserId int;
    DECLARE v_validMembership int;
    DECLARE o_Status integer;

    SELECT vvalidUserId = u.UserId
    FROM Users u
    WHERE u.Username = i_username;

    IF( v_validUserId IS NULL ) THEN
        SET o_Status = 2; -- Invalid username
    ELSE
        SET o_Status = 1; -- Good
    END IF;


    IF(o_Status != 2 ) THEN
            SELECT v_validMembership = 1
            FROM Users u
            JOIN UserMemberships um on um.UserId = u.userId
            JOIN Memberships m on m.MembershipId = um.MembershipId
            WHERE um.MembershipExpireDateTime > CURDATE()
            AND u.UserId = v_validUserId;

            IF( v_validMembership IS NULL ) THEN 
                SET o_Status = 3; -- Invalid membership
            END IF;
    END IF;

    RETURN o_status;

END $$
DELIMITER ;

Any help will be greatly appreciated!

解决方案

I'm not sure if you can assign variables that way, try use INTO statement for your selects. For example:

SELECT 
    u.UserId INTO vvalidUserId 
FROM 
    Users u
WHERE 
    u.Username = i_username;

这篇关于MySQL函数 - 错误代码：1415不允许从函数返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！