Aprenda PL/SQL

bug

Tem algumas coisas que estão praticamente enraizadas no mundo Oracle: me refiro a algumas práticas que quase todo mundo faz e que foi adotado como padrão. O texto abaixo não contém nenhuma nova feature do Oracle, nem é nenhuma novidade pra ninguém. Mas por incrível que pareça, eu vejo em quase todas empresas essa prática e isso normalmente gera problemas. Me refiro a utilização de um NVL pra fazer um filtro opcional na query. Abaixo está um exemplo:

SELECT *
FROM tabela
WHERE campo = NVL ( p_parametro1 , campo )

Quase todo programa tem um sistema de filtro como esse acima.
Se o usuário informar o p_parametro1, a query vai mostrar apenas as linhas cujo campo é igual ao p_parametro1.
Se o usuário não informar nada, cai no NVL, e ele faz join com a própria coluna, trazendo tudo.

Aqui está um exemplo real do que foi citado acima.
Primeiro, vamos criar uma BIND variável no SQL*Plus, chamada PARJOB, que será o parâmetro da nossa query:

SQL> var parjob VARCHAR2(100)
SQL> var
variable parjob
datatype VARCHAR2(100)
 
SQL>

Agora, vamos rodar uma query usando o sistema de NVL pra filtrar. Detalhe, o parâmetro está NULO!

SQL> SELECT *
  2  FROM emp
  3  WHERE job = NVL ( :parjob, job)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       17-Nov-1981   5000.00               10
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
14 rows selected
parjob
---------
 
SQL>

Repare que todas as 14 linhas da EMP vieram corretamente.
Caso a gente setar um valor pro parâmetro, ele deve filtrar.

SQL> begin
  2    :parjob := 'MANAGER';
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parjob
---------
MANAGER
 
SQL>

Se executarmos novamente a mesma query, vai funcionar o filtro:

SQL> SELECT *
  2  FROM emp
  3  WHERE job = NVL ( :parjob, job)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
parjob
---------
MANAGER
 
SQL>

Funcionou perfeitamente: Trouxe apenas as linhas MANAGER, conforme o filtro.

Afinal, qual é o problema disso?
Essa prática pode começar a gerar resultados errados, bugs e dor de cabeça sem fim.
Isso porque usar o NVL dessa forma só funciona quando nenhuma linha é NULL. E é nisso que as pessoas erram. Usam essa forma de fazer filtro sempre, sem consultar se o campo poder ter NULLs ou não.

Vamos fazer o mesmo exemplo para o campo MGR, que é um campo que contém nulos. Vamos criar outra variável BIND e vamos usar ela no filtro do gerente. (MGR)

SQL> var parmgr NUMBER;
SQL> var
variable parjob
datatype VARCHAR2(100)
 
variable parmgr
datatype NUMBER
 
SQL>

Pra testar, vamos setar um valor:

SQL> begin 
  2    :parmgr := 7839;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parmgr
---------
7839
 
SQL> 
SQL> 
SQL> SELECT *
  2  FROM emp
  3  WHERE mgr = NVL ( :parmgr, mgr)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
parmgr
---------
7839
 
SQL>

Até aqui funcionou! Trouxe apenas as linhas que o MGR é 7839.
Mas e se deixar nulo o parâmetro? Deve trazer todas linhas, certo?

SQL> begin
  2    :parmgr := NULL;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parmgr
---------
 
SQL> 
SQL> SELECT *
  2  FROM emp
  3  WHERE mgr = NVL ( :parmgr, mgr)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
13 rows selected
parmgr
---------
 
SQL>

Só veio 13 linhas? Onde está a linha do KING ? Faltando uma linha! Justamente aquela que está NULA!
Por isso essa prática é péssima. Você só pode usar NVL pra fazer filtro dessa forma quando tiver certeza absoluta que não existem valores NULL na coluna.

SOLUÇÃO PRÁTICA

Em vez de usar NVL, basta usar um simples OR:

SQL> SELECT *
  2  FROM emp
  3  WHERE ( :parmgr IS NULL OR mgr=:parmgr )
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       17-Nov-1981   5000.00               10
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
14 rows selected
parmgr
---------
 
SQL>

Dessa forma você não precisa se preocupar se existe NULL ou não no campo. Uma preocupação a menos.

Obviamente, não estamos fazendo testes relacionados ao plano de execução, visto que o resultado das duas queries é diferente: Uma não traz NULLS e outra traz. O uso do NVL certamente é mais performático quando usando num campo com índice, pois ele não precisa percorrer a tabela buscando os NULLS e utiliza o índice). Como são resultados diferentes, não temos porque comparar performance.

Caso você queria saber mais sobre campos NULL, comparação de NULLs, aqui tem alguns textos e experimentos realizados pela comunidade:
Comparação de campos com null
Null é igual a branco ?
Interessante – Campo NULL
Igualdade Desigual ??? alguém sabe porque NULL não é igual a NULL?

Comments are closed.