MySQL Function returning NULL

Question

MySQL Function returning NULL

Navigation

#1 by (1 votes)

1

My function does not work for anything in this world

SQL looks like this:

- Function used to return the remaining space of a chosen folder. -

DELIMITER $$
CREATE FUNCTION tamanhoRestante (_idPasta INT)
RETURNS INT
BEGIN

    DECLARE lpasTamanho INT;      
    DECLARE lpasUsado INT;
    -- Aqui é verificado o tamanho TOTAL da pasta escolhida. --
    SELECT tamanho FROM pasta WHERE idPasta = _idPasta INTO lpasTamanho;
    -- E agora a verificação de todos os arquivos alocados nesta pasta. --
    IF(SELECT COUNT(*) FROM usuario JOIN usuario_has_pasta WHERE idUsuario = usuario_has_pasta.Usuario_idUsuario > 0) THEN
    SELECT SUM(arquivo.tamanho) FROM arquivo JOIN Pasta_has_Arquivo JOIN Pasta WHERE idArquivo = Arquivo_idArquivo AND Pasta_idPasta = _idPasta INTO lpasUsado;
    ELSE SET lpasUsado = 0;
    END IF;
    -- Agora faz-se o retorno do tamanho total da pasta subtraindo o total utilizado --
RETURN lpasTamanho - lpasUsado;

END
$$
DELIMITER ;

In this test, the% of total% of the selected folder, and lpasTotal = Tamanho is the sum of the size of all files linked to this folder (by% ter_%).

The lpasUsado in the test has size 100, and Pasta_has_Arquivo did the test with 0 and 50, but in both returns Null: /

Can anyone help me with this? I can not find the error.

mysql sql

asked by anonymous 07.11.2016 / 15:36

1 answer

How to use th: if No module named numpy

score 1 · Accepted Answer

Your select has some problems:

The INTO statement must be immediately after the field declaration in select ;
The idPasta field is with _ in join;
Prefer to use inner join ;

It would look like this after a few fixes:

-- Function utilizada para retornar o espaço restante de uma pasta escolhida. --
DELIMITER $$
CREATE FUNCTION tamanhoRestante (_idPasta INT)
RETURNS INT
BEGIN
  DECLARE lpasTamanho INT;
  DECLARE lpasUsado INT;

  -- Aqui é verificado o tamanho TOTAL da pasta escolhida. --
  SELECT tamanho
    INTO lpasTamanho
    FROM pasta
   WHERE idPasta = _idPasta;

  SET lpasUsado = 0;

  -- E agora a verificação de todos os arquivos alocados nesta pasta. --
  IF (EXISTS(SELECT 1 = 1
               FROM usuario u
               INNER JOIN usuario_has_pasta uhp ON u.idUsuario = uhp.Usuario_idUsuario)) THEN
  BEGIN
    SELECT SUM(COALESCE(arquivo.tamanho, 0))
      INTO lpasUsado
      FROM arquivo a
      LEFT JOIN Pasta_has_Arquivo pha ON a.idArquivo = pha.Arquivo_idArquivo
      LEFT JOIN Pasta p ON pha.Pasta_idPasta = p.idPasta;
  END;
  END IF;

  -- Agora faz-se o retorno do tamanho total da pasta subtraindo o total utilizado --
  RETURN lpasTamanho - lpasUsado;
END
$$
DELIMITER ;

The EXISTS statement checks whether the reference returns true, so it is necessary to put 1 = 1, which will only be executed if where is satisfied.