在 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
发布评论