MySQL 说:#1415 - 不允许从函数返回结果集 [英] MySQL said: #1415 - Not allowed to return a result set from a function

问题描述

在 MySQL 的 PHPMyAdmin 中创建存储函数时

While creating stored function in MySQL's PHPMyAdmin

MySQL 说:#1415 - 不允许从函数返回结果集

MySQL said: #1415 - Not allowed to return a result set from a function

BEGIN
    SET @pAvg = 0; 
    SET @p1 = 0;
    SET @p2 = 0;
    SET @p3 = 0;
    SET @p4 = 0;
    IF river > 1 THEN
        SELECT @P1 := Precipitation FROM abd_2013 where id = 1;
        SELECT @P2 := Precipitation FROM abd_2014 where id = 1;
        SELECT @P3 := Precipitation FROM abd_2015 where id = 1;
        SELECT @P5 := Precipitation FROM abd_2016 where id = 1;
        SELECT @pAvg := (@p1 + @p2 + @p3 + @p4)/4;
     ELSEIF river < 1 THEN SELECT @P5;
     END IF;
     RETURN river;
END

如果我在存储过程中做同样的事情，一切都会好的，但是每当我选择 FUNCTION 时，它都会发出警告添加 RETURN，并在写入 return 后引发错误 #1415

Everything going fine if I do the same in Stored Procedure but whenever I opt FUNCTION it gives warning add RETURN and after writing return it raises error #1415

MySQL 说:#1415 错误

推荐答案

本例中的错误信息说明了最重要的部分:

The error message in this case tells the most important part:

不允许从函数返回结果集

Not allowed to return a result set from a function

此行为与 MySQL 手册中关于存储过程和函数的记录一致:

This behaviour is consistent with what's documented in MySQL manual on stored procedures and functions:

对于在函数定义时可以确定返回结果集的语句，会出现不允许从函数返回结果集的错误(ER_SP_NO_RETSET).

For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET).

您使用返回结果集的选择语句为 @Pn 变量赋值，这在函数中是不允许的.您必须从代码中删除这些语句.RETURN river 返回结果值，而不是结果集合.

You assign values to your @Pn variables using select statements that return a resultset and this is not allowed in a function. You have to remove these statements from your code. RETURN river returns a result value, but not a result set.

我还担心您使用跨连接共享的会话变量(定义为@variable_name 的变量)，因此可能在连接内同时多次调用同一函数可能会相互干扰.

I'm also worried that you use session variables (variables defined as @variable_name) which are shared across a connection, so potentially multiple call to the same function at the same time within a connection may interfere with each other.

存储的函数应该只返回一个值作为它的输出在 return 语句中.其他任何事情都被视为副作用.如果您希望 MySQL 脚本填充多个变量，则必须使用存储过程，而不能使用存储函数.

A stored function is only supposed to return a single value as its output in with the return statement. Anything else is considered as a side effect. If you want your MySQL script to populate multiple variables, then you must use a stored procedure, you cannot use a stored function.

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