Mysql:不允许从函数返回结果集 [英] Mysql : Not allowed to return a result set from a function

问题描述

我已经编写了一个函数，但出现此错误不允许从函数返回结果集

I have write one function but getting this error Not allowed to return a result set from a function

DELIMITER $$

CREATE FUNCTION getTestFunction
(
    p_ParentID int,
    p_ListName nvarchar(50),
    p_Type nvarchar(50),
    p_Count int 
)
RETURNS nvarchar(2000)
BEGIN
    DECLARE p_KeyValue nvarchar(2000);
    DECLARE p_ListValue nvarchar(2000);
    DECLARE p_TextValue nvarchar(2000);
    DECLARE p_ReturnValue nvarchar(2000);
    DECLARE p_Key nvarchar(2000);
    
    IF p_ParentID = 0 THEN
        IF p_Count = 0 THEN
            SET p_ReturnValue = '';
        ELSE
            SET p_ReturnValue = p_ListName;
        END IF;
    ELSE
    
            SELECT  p_KeyValue = ListName + '.' + Value
                
                FROM ListsTable
                WHERE EntryID = p_ParentID  LIMIT 1 ;
    RETURN p_ReturnValue;
            If p_Type = 'ParentKey' Or (p_Type = 'ParentList' AND p_Count > 0) THEN
                SET p_ReturnValue = p_KeyValue;
            ELSE 
                IF p_Type = 'ParentList' THEN
                    SET p_ReturnValue = p_ListValue;
                ELSE
                    SET p_ReturnValue = p_TextValue;
                END IF;
            END IF;

            IF p_Count > 0 THEN
                If p_Count = 1 AND p_Type = 'ParentList' THEN
                    SET p_ReturnValue = p_ReturnValue + ':' + p_ListName;
                ELSE
                    SET p_ReturnValue = p_ReturnValue + '.' + p_ListName;
                END IF;
            END IF;
        
    END IF; 
    RETURN p_ReturnValue;
END$$
DELIMITER ;

推荐答案

您想将查询的结果分配给一个变量，但实际上您只是selecting.这就是 MySQL 抱怨的原因.

You want to assign the result of a query to a variable, but in fact you're just selecting. That's why MySQL's complaining.

你必须改变这个

            SELECT  p_KeyValue = ListName + '.' + Value
            FROM ListsTable
            WHERE EntryID = p_ParentID  LIMIT 1 ;

到

            SELECT CONCAT(ListName, '.', `Value`)
            INTO p_KeyValue
            FROM ListsTable
            WHERE EntryID = p_ParentID  LIMIT 1 ;

你应该添加一个ORDER BY.没有 ORDER BY 的 LIMIT 没有意义，因为在关系数据库中没有保证的顺序.

And you should add an ORDER BY. A LIMIT without ORDER BY doesn't make sense, since there's no guaranteed order in a relational database.

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