Neste artigo vou abordar as melhores práticas de programação em Pl/SQL. Não existe forma correta ou errada de se programar, desde que os requisitos sejam atendidos. O que existe são práticas que podemos utilizar para minimizar erros, aumentar o entendimento do código, melhorar o desempenho e a aplicação, aumentar a produtividade, diminuir possíveis vulnerabilidades e permitir a continuidade do trabalho.

1. Utilizando a tabela DUAL

Émuito comum vermos em blocos Pl/SQL a utilização de query na tabela dual para execução de funções:

Declare

V_data date;
V_usuario varchar2(30);
V_result    varchar2(2);
V_param   varchar2(2);

begin
Select sysdate
  Into v_data
  From dual;

Select user
  Into v_usuario
  From dual;

Select decode(v_param,'SP','BR','EX')
  Into v_result
  From dual;

End;

Evite este tipo de coisa, fazer query na tabela dual para execução de uma função gera um parse e um execute desnecessário no banco de dados.  A melhor forma de executar uma função é chamá-la diretamente:

Declare

V_data date;
V_usuario varchar2(30);
V_result    varchar2(2);
V_param   varchar2(2);

Begin

    V_data := sysdate;
    V_ususario := user;

    If(v_param = 'SP')then
     V_result := 'BR';

    Else

      V_result := 'EX';
    End if;

End;

2. Contando registros

Outro erro comum é a utilização ordenação em query de contagem de registros.

      Select count(*)
       From tb_movimento
      Order by id_movimento;

Não há sentido de utilizar um order by em contagem de registros, o resultado da contagem é o mesmo independente da ordenação. Quando encontrado um comando de ordenação, o banco de dados utiliza a tablespace temporária para ordenar os dados, tornando a query mais lenta:

       Select count(*)
        From tb_movimento

Nunca, mas nunca, faça estruturas condicionais utilizando a tabela dual:

declare

v_a boolean;
v_b number := 1;
v_c number := 2;

begin

select true
  into v_a
  from dual
  where v_b > v_c
  
exception when no_data_found then

v_a := false;

end;  

Forma correta:

declare

v_a boolean;
v_b number := 1;
v_c number := 2;

begin

if(v_b > v_c)then

  v_a := true;

  else
  v_a := false;

end if;


end;

3. Tratamentos de exceções necessários e desnecessários

Émuito comum esquecer de tratar as exceções ocorridas no processo. Mas tratar exceções desnecessárias também torna o código confuso e muitas vezes ineficiente. Certamente você já viu isso:

    Declare

  V_result varchar2(2);
  V_param  varchar2(2);

Begin

  Select decode(v_param, 'SP', 'BR', 'EX') Into v_result From dual;

Exception
  when no_data_found then
    Raise_applcation_error(-20001,
                           'Nenhum registro encontrado na tabela dual');
  When too_many_rows then
    Raise_applcation_error(-20002,
                           'Mais de um registro encontrado na tabela dual');
End;

Utilizar tratamento de exceções na tabela dual é muito preciosismo, comandos DML na tabela dual só podem ser executados pelo usuário SYS. Logo, concluímos que é quase impossível de fazer um delete ou insert nesta tabela. Caso isso ocorra é mais fácil resolver o problema na raiz.

Tratar exceções que estruturalmente não ocorrerão:

create table tb_tecnico(id_tecnico number(8) primary key,
                               nome       varchar2(40));

create or replace fnc_cad_nome_tecnico(p_id_tecnico in tb_tecnico.id_tecnico%type)
return tb_tecnico.nome%type
is
v_nome tb_tecnico.nome%type;
begin

  select t.nome
    into v_nome
    from tb_tecnico t
   where t.id_tecnico = p_id_tecnico;
   
     return v_nome;

exception when no_data_found then
    raise_application_error(-20001,'Tecnico não encontrado');
          when too_many_rows then
    raise_application_error(-20002,'Mais de um técnico encontrado');
end fnc_cad_nome_tecnico;       

Neste caso acima, o tratamento de too_many_rows é desnecessário, pois o campo base para retorno da consulta é a chave primária da tabela, que nunca irá se repetir. Logo, é impossível de retornar mais que um registro.  

No caso abaixo simulo um erro de valor muito grande para precisão da variável:

declare

v_a number(6) := 999999;
v_b number(8) := 99999999;
v_total  number(6);

e_long_value exception;
pragma exception_init(e_long_value,-06502);


begin
  
  v_total := v_a + v_b;
  
  exception when e_long_value then
  
  v_total := 0;
  
end;

Na verdade, a forma mais simples de resolver este problema seria aumentar a precisão da variável total para number(9). Tomando como regra que uma variável totalizadora deve ter sua precisão igual ou maior que a precisão maior variável somada + 1.

declare

v_a number(6) := 999999;
v_b number(8) := 99999999;
v_total  number(9); 


begin
  
  v_total := v_a + v_b;
  
end;

4. Declaração de parâmetros e variáveis

Para parâmetros ou variáveis que representam colunas de tabelas, prefira declarar seu type utilizando a herança do mesmo tipo da tabela que representa:

Declare

  v_qtd_movimento tb_movimento.qtd_movimento%type;
  v_local_estoque    tb_local_estoque.local_estoque%type;

begin

   select m.qtd_movimento,l.local_estoque
      into v_qtd_movimento, v_local_estoque
     from tb_movimento m
  where m.id_movimento = 1
    and m.id_local_estoque = l.id_local_estoque;

End;

Create or replace Function fnc_fin_vlr_titulo(p_id_titulo in tb_titulo.id_titulo%type)
Return tb_titulo.vlr_titulo%type
Is
Vlr_titulo tb_titulo.vlr_titulo%type;
Begin


Select vlr_titulo
    Into vlr_titulo
    From tb_titulo
Where id_titulo = p_id_titulo;

  Return vlr_titulo;

End fnc_fin_vlr_titulo;

Para representar todas as colunas de uma tabela é interessante utilizar a herança de linha:

Declare

V_movimento tb_movimento%rowtype;
V_pessoa    tb_pessoa%rowtype;

Begin

    Select m.*
       Into v_movimento
      From tb_movimento m
    Where m.id_movimento = 1;

   Select p.*
     Into v_pessoa
   From tb_pessoa p
 Where p.id_pessoa = 32;
   
End;


create or replace function fnc_titulo(p_id_titulo in tb_titulo.id_titulo%type)
return tb_titulo%rowtype
is
v_titulo tb_titulo%rowtype;
is
begin

   select t.*
     into v_titulo
     from tb_titulo t
    where t.id_titulo = p_id_titulo;

   return v_titulo;

end fnc_titulo;

Deste modo, caso ocorra uma alteração na estrutura da tabela, as variáveis também herdaram as alterações.

5. Crie prefixos para os objetos

É de muita ajuda a utilização de prefixos para identificar a qual módulo o objeto pertence, e qual o seu tipo:

Para nomenclatura de um sinônimo, pode-se utilizar o mesmo nome do objeto que ele representa ou informar um novo nome a ele.

create or replace synonym tb_veiculo for teste.tb_veiculo;

Ou

create or replace synonym tb_veiculo for teste.tb_veiculo;

Para nomenclatura de triggers, pode-se utilizar o prefixo do evento que aciona a mesma, e pode-se suprimir o módulo a que a mesma pertence (irá herdar o modulo da tabela), por exemplo:

De modo geral:

Por exemplo:

6. Passagem de parametros

Existem duas formas para passar parâmetros entre objetos:

Referenciada, quando o parâmetro que receberá a informação está identificado. Posicional, quando o parâmetro que receberá a informação é identificado por sua posição na declaração. Por exemplo, temos a seguinte declaração abaixo:

  Procedure prc_fin_calcula_iss(p_id_pessoa   in number,
                                P_dt_base     in date,
                                P_vlr_total   in number,
                                P_id_aliquota in number);

Veja que utilizando a passagem identificada, é possível visualizar que valor cada parâmetro recebe. Ferramentas case podem ajudar a montar a chamada dos objetos com parâmetros identificados.

Declare
Begin

  Prc_fin_calcula_iss(p_id_pessoa   => 3356,
                      P_dt_base     => null,
                      P_vlr_total   => 1000.00,
                      P_id_aliquota => 98);

End;

Utilizando a passagem por posição, não é possível visualizar qual valor refere-se a qual parâmetro. Caso precise de um novo parâmetro no procedimento, o desenvolvedor deverá, sempre, colocá-lo por último na declaração do objeto.

Declare
Begin

  Prc_fin_calcula_iss(3356, null, 1000.00, 98);

End;

Ambas formas realizam a chamada com a mesma eficiência, mas por questão de entendimento do código, prefira utilizar a passagem por referência.

Falaremos de outras situações de boas práticas no próximo artigo.

Abraços.