Problema na criação de uma função

[Rafael_Santos]

Olá Senhores(as),
estou com uma dificuldade em criar uma função que retornaria login válidos.
Os logins válidos são aqueles que possuem um nivel que possua vinculo com nivel 3.
Exemplo: existe o nivel 1 que possui vinculo com nivel 2...o nivel 2 possui vinculo com o nivel 3...o nivel 3 possui vinculo com o nivel 4 e o nivel 4 com o nivel 5. Mas as vezes alguns logins estão no nivel 1 que possuem vinculo com o nivel 4. Gostaria de separar aqueles que possuem somente vinculo com o nivel 3.
Quando realizo a pesquisa:

Selecionar tudo

SELECT f_encontra_login_valido(login)
FROM vw_banco_ideias_usuarios

Ocorre o seguinte erro: ORA-06503: PL/SQL: Função retornada sem valor
Segue a função que estou utilizando:

Selecionar tudo

CREATE OR REPLACE FUNCTION f_encontra_login_valido
(login_usuario IN VARCHAR2)
RETURN VARCHAR2
IS

 Nivel_Usuario NUMBER;
 Id_Unidade NUMBER;

CURSOR C1 IS
 SELECT cod_nivel
 FROM vw_apex_unid_organizac_tcu
 WHERE cod = (SELECT cod_lotado FROM vw_banco_ideias_usuarios WHERE UPPER(login) = UPPER(login_usuario));
BEGIN
 OPEN C1;
  LOOP
   FETCH C1 INTO Nivel_Usuario;
   EXIT WHEN C1%NOTFOUND;
IF (Nivel_Usuario = 1) THEN
 SELECT cod_unidade_superior 
 INTO Id_Unidade
 FROM vw_apex_unid_organizac_tcu 
 WHERE cod = (SELECT cod_lotado FROM vw_banco_ideias_usuarios WHERE UPPER(login) = UPPER(login_usuario));
 SELECT cod_nivel
 INTO nivel_usuario
 FROM vw_apex_unid_organizac_tcu
 WHERE cod = Id_Unidade;
  IF (Nivel_Usuario = 2) THEN
  SELECT cod_unidade_superior 
  INTO Id_Unidade
  FROM vw_apex_unid_organizac_tcu 
  WHERE cod = Id_Unidade;
  SELECT cod_nivel
  INTO nivel_usuario
  FROM vw_apex_unid_organizac_tcu
  WHERE cod = Id_Unidade;
   IF (Nivel_Usuario = 3) THEN
    RETURN login_usuario;
   END IF;
  END IF;
ELSIF (Nivel_Usuario = 2) THEN
 SELECT cod_unidade_superior 
 INTO Id_Unidade
 FROM vw_apex_unid_organizac_tcu 
 WHERE cod = (SELECT cod_lotado FROM vw_banco_ideias_usuarios WHERE UPPER(login) = UPPER(login_usuario));
 SELECT cod_nivel
 INTO nivel_usuario
 FROM vw_apex_unid_organizac_tcu
 WHERE cod = Id_Unidade;
  IF (Nivel_Usuario = 3) THEN
   RETURN login_usuario;
  END IF;
ELSIF (Nivel_Usuario = 3) THEN
 RETURN login_usuario;
END IF;
  END LOOP;
 CLOSE C1;
END;

[stcoutinho]

Olá Rafael,

Notei que a sua função utiliza muito comandos do tipo "SELECT .. INTO .. FROM .. WHERE ..".

Este tipo de comando pode retornar o erro NO_DATA_FOUND se o SELECT não encontrar dados que satisfaçam o filtro utilizado.

Por que você não utiliza cursores (como no começo do seu código) em lugar destes SELECT...INTO..? Com eles, você não se depararia com erros do tipo NO_DATA_FOUND.

Um outro ponto: pode ser que este erro esteja ocorrendo porque ocorre alguma situação que não é prevista por nenhum dos seus IFs. Ou seja: pode ser que ela não execute nenhum comando "RETURN login_usuario;".

Eu recomendaria adicionar estas últimas linhas no final de sua função:

Selecionar tudo

...
END LOOP;
CLOSE C1;
RETURN login_usuario;
EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'OCORREU ERRO - '||SQLCODE||' - MENSAGEM - '||SQLERRM);
END;

Qualquer dúvida, fique à vontade para continuar postando no forun.

Abraços,

Sergio Coutinho

[Rafael_Santos]

Obrigado Coutinho,
você tinha razão, dentro do if ele restringia alguns valores e não retornava.
Andei falando com meu orientador ele disse que toda função deve retornar valores, e eu estava evitando o retorno de alguns valores para a busca de toda tabela, e como se a tabela fosse de 1 a 5 e eu queria somente números pares, ao invés de retornar null nos pares e realizar um select com where coluna is not null eu simplesmente pulava os impares.
Quanto a utilização de cursor, eu preferi evitar para deixar minha aplicação mais rápida vez que ela esta começando a ficar lenta. Será que o uso demasiado de cursores deixa a busca lenta?

No momento estou de férias, mas quando voltar colocarei a função criada aqui...Abraços e Obrigado Coutinho!

[stcoutinho]

Rafael,

Se a sua função (que acessa tabelas) for chamada dentro de um SELECT que retorna muitas linhas, provavelmente ela deixará a querie de SELECT muito lenta, pois esta função será disparada a cada inha retornada de seu SELECT.

Não poderia lhe assegurar com certeza que os CURSORES vão deixar a função lenta. Creio que isso seria determinado pela a intensidade/frequencia com que esta função seria chamada.

Como lhe disse, se você chamar esta função como uma coluna DENTRO DE UM SELECT (ex: SELECT COLUNA_A, FUNCAO_XPTO(COLUNA_A),COLUNA_B FROM TBL_TABELA) com certeza haverá perda de performance, usando cursores ou não.

Posso estar falando bobagem, mas se você chamar esta função como uma coluna de um SELECT, tente verificar se ficaria mais rápido se de alguma forma incorporasse a lógica da função neste SELECT (ex: fazendo joins diretos com vw_apex_unid_organizac_tcu, vw_banco_ideias_usuarios, etc.

Abraços e boa sorte !

Sergio Coutinho
.