存储的函数计算出不正确的值 [英] Stored function computes incorrect value

问题描述

我有一个函数应该只返回一个元素的值，但这是从每个表返回的

I have a function that should return the value of only one element, but this is returning from every table

Function verify(idAluno INT) returns INT
BEGIN
    DECLARE teste INT;
    SELECT COUNT(status) into teste FROM listapresenca WHERE idAluno = idaluno;
    RETURN teste;
END

listapresenca中的值是

The values from the listapresenca is

idAluno status  horario
1       ENTROU  2018-09-02 21:46:25
0       NAO     2018-09-02 21:46:43
0       NAO     2018-09-02 21:46:45
0       NAO     2018-09-02 21:46:46
0       NAO     2018-09-02 21:46:48
0       NAO     2018-09-02 21:59:10

将变量idaluno = 1的结果应该为1，但返回值为6.

The result from putting the variable idaluno = 1 should be 1, but it is returning 6.

如果我在函数SELECT COUNT(status) FROM listapresenca WHERE idAluno = 1之外使用，则返回值为1

if I use outside the function SELECT COUNT(status) FROM listapresenca WHERE idAluno = 1, the return value is 1

推荐答案

问题是表中的列名称idAluno与函数中的参数名称idAluno之间存在冲突，因此您的子句有效地比较了相同的值，并将返回表中的所有数据(因此您的结果为6).尝试将参数名称更改为例如searchId:

The problem is that you have a clash between the column name idAluno in the table and the parameter name idAluno in the function, so your WHERE clause is effectively comparing the same values and will return all data from the table (hence your result of 6). Try changing the parameter name to e.g. searchId:

CREATE FUNCTION verify(SearchId INT) returns INT
BEGIN
    DECLARE teste INT;
    SELECT COUNT(status) into teste FROM listapresenca WHERE idAluno = SearchId;
    RETURN teste;
END

然后您可以使用以下方法进行测试:

Then you can test with:

SELECT verify(1), verify(0)

输出(用于您的数据)

verify(1)   verify(0)   
1           5

在 Resterester

这篇关于存储的函数计算出不正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！