Boas práticas de programação em PL/SQL – Parte 01
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<br /><br />V_data date;<br />V_usuario varchar2(30);<br />V_result varchar2(2);<br />V_param varchar2(2);<br /><br />begin<br />Select sysdate<br /> Into v_data<br /> From dual;<br /><br />Select user<br /> Into v_usuario<br /> From dual;<br /><br />Select decode(v_param,'SP','BR','EX')<br /> Into v_result<br /> From dual;<br /><br />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<br /><br />V_data date;<br />V_usuario varchar2(30);<br />V_result varchar2(2);<br />V_param varchar2(2);<br /><br />Begin<br /><br /> V_data := sysdate;<br /> V_ususario := user;<br /><br /> If(v_param = 'SP')then<br /> V_result := 'BR';<br /><br /> Else<br /><br /> V_result := 'EX';<br /> End if;<br /><br />End;2. Contando registros
Outro erro comum é a utilização ordenação em query de contagem de registros.
Select count(*)<br /> From tb_movimento<br /> 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(*)<br /> From tb_movimentoNunca, mas nunca, faça estruturas condicionais utilizando a tabela dual:
declare<br /><br />v_a boolean;<br />v_b number := 1;<br />v_c number := 2;<br /><br />begin<br /><br />select true<br /> into v_a<br /> from dual<br /> where v_b > v_c<br /> <br />exception when no_data_found then<br /><br />v_a := false;<br /><br />end; Forma correta:
declare<br /><br />v_a boolean;<br />v_b number := 1;<br />v_c number := 2;<br /><br />begin<br /><br />if(v_b > v_c)then<br /><br /> v_a := true;<br /><br /> else<br /> v_a := false;<br /><br />end if;<br /><br /><br />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<br /><br /> V_result varchar2(2);<br /> V_param varchar2(2);<br /><br />Begin<br /><br /> Select decode(v_param, 'SP', 'BR', 'EX') Into v_result From dual;<br /><br />Exception<br /> when no_data_found then<br /> Raise_applcation_error(-20001,<br /> 'Nenhum registro encontrado na tabela dual');<br /> When too_many_rows then<br /> Raise_applcation_error(-20002,<br /> 'Mais de um registro encontrado na tabela dual');<br />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,<br /> nome varchar2(40));<br /><br />create or replace fnc_cad_nome_tecnico(p_id_tecnico in tb_tecnico.id_tecnico%type)<br />return tb_tecnico.nome%type<br />is<br />v_nome tb_tecnico.nome%type;<br />begin<br /><br /> select t.nome<br /> into v_nome<br /> from tb_tecnico t<br /> where t.id_tecnico = p_id_tecnico;<br /> <br /> return v_nome;<br /><br />exception when no_data_found then<br /> raise_application_error(-20001,'Tecnico não encontrado');<br /> when too_many_rows then<br /> raise_application_error(-20002,'Mais de um técnico encontrado');<br />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<br /><br />v_a number(6) := 999999;<br />v_b number(8) := 99999999;<br />v_total number(6);<br /><br />e_long_value exception;<br />pragma exception_init(e_long_value,-06502);<br /><br /><br />begin<br /> <br /> v_total := v_a + v_b;<br /> <br /> exception when e_long_value then<br /> <br /> v_total := 0;<br /> <br />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<br /><br />v_a number(6) := 999999;<br />v_b number(8) := 99999999;<br />v_total number(9); <br /><br /><br />begin<br /> <br /> v_total := v_a + v_b;<br /> <br />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<br /><br /> v_qtd_movimento tb_movimento.qtd_movimento%type;<br /> v_local_estoque tb_local_estoque.local_estoque%type;<br /><br />begin<br /><br /> select m.qtd_movimento,l.local_estoque<br /> into v_qtd_movimento, v_local_estoque<br /> from tb_movimento m<br /> where m.id_movimento = 1<br /> and m.id_local_estoque = l.id_local_estoque;<br /><br />End;<br /><br />Create or replace Function fnc_fin_vlr_titulo(p_id_titulo in tb_titulo.id_titulo%type)<br />Return tb_titulo.vlr_titulo%type<br />Is<br />Vlr_titulo tb_titulo.vlr_titulo%type;<br />Begin<br /><br /><br />Select vlr_titulo<br /> Into vlr_titulo<br /> From tb_titulo<br />Where id_titulo = p_id_titulo;<br /><br /> Return vlr_titulo;<br /><br />End fnc_fin_vlr_titulo;Para representar todas as colunas de uma tabela é interessante utilizar a herança de linha:
Declare<br /><br />V_movimento tb_movimento%rowtype;<br />V_pessoa tb_pessoa%rowtype;<br /><br />Begin<br /><br /> Select m.*<br /> Into v_movimento<br /> From tb_movimento m<br /> Where m.id_movimento = 1;<br /><br /> Select p.*<br /> Into v_pessoa<br /> From tb_pessoa p<br /> Where p.id_pessoa = 32;<br /> <br />End;<br /><br /><br />create or replace function fnc_titulo(p_id_titulo in tb_titulo.id_titulo%type)<br />return tb_titulo%rowtype<br />is<br />v_titulo tb_titulo%rowtype;<br />is<br />begin<br /><br /> select t.*<br /> into v_titulo<br /> from tb_titulo t<br /> where t.id_titulo = p_id_titulo;<br /><br /> return v_titulo;<br /><br />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:
| Objeto | Modulo | Tipo | Objetivo | |
|---|---|---|---|---|
| Pck_est_movimento | Package | Efetuar | Movimento de estoque | |
| Vw_cad_pessoa | Cadastros | View | Listar as pessoas cadastradas | |
| Fnc_fin_vlr_titulo | Financeiro | Função | Retornar o valor de um título | |
| Typ_cad_veiculo | Cadastros | Type | Estrutura referente a um veículo | |
| E_long_value | Genérico | Exception | Exception referente ao erro -06502, valor muito grande | |
| Prc_cad_atu_veiculo | Cadastros | Procedure | Atualizar o cadastro de um veículo | |
| Tb_estado | Cadastros | Tabela | Informações do estado | |
| Tb_veiculo.Ds_veiculo | Cadastros | Coluna | Descrição do veículo |
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:
| Sigla | Momento |
|---|---|
| A | After |
| B | Before |
| Sigla | Evento |
|---|---|
| I | Inset |
| U | Update |
| D | Delete |
Por exemplo:
| Objeto | Prefixo | Evento | Descrição |
|---|---|---|---|
| BI_CALC_NOTA | BI | Before Insert | Antes do Insert |
| BU_LOG_VEICULO | BU | Before Update | Antes do Update |
| BD_VALIDA_REFERENCIA | BD | Before Delete | Antes do Delete |
| AIUD_LOG_VEICULO | AIUD | After Insert, Update e Delete | Depois do insert, update ou delete |
| AD_CRIA_GENERICO | AD | After Delete | Depois do delete |
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,<br /> P_dt_base in date,<br /> P_vlr_total in number,<br /> 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<br />Begin<br /><br /> Prc_fin_calcula_iss(p_id_pessoa => 3356,<br /> P_dt_base => null,<br /> P_vlr_total => 1000.00,<br /> P_id_aliquota => 98);<br /><br />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<br />Begin<br /><br /> Prc_fin_calcula_iss(3356, null, 1000.00, 98);<br /><br />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.
é formado em Análise de Sistemas pela Uniban. Possui 10 anos de experiência em análise, implementação e desenvolvimento de softwares com Oracle Forms/Reports, PL/SQL. Também possui experiência de 6 anos em WebTool Kit, HTML, JavaScript, XML, CSS e APEX, além de conhecimentos em JAVA e Delphi. Possui certificação Oracle Advanced PL/SQL Developer Certified Professional 11g. Em sua experiência profissional teve a oportunidade de participar de diversos projetos, dos quais pode-se destacar migrações de sistemas de arquivos indexados em Cobol para banco de dados Oracle; tunning em camada de aplicações e camada de banco de dados; administração de banco de dados Oracle 9i e 10g; modelagem relacional de dados utilizando Erwin; migração do Forms 6i para Forms IAS 10g; levantamento, análise e desenvolvimento de software em Delphi com Oracle, Oracle WebTool Kit e APEX. Atualmente trabalha em uma empresa petroquimica, na qual atua como Desenvolvedor Oracle EBS, desenvolvendo customizações para todos os módulos, nos padrões e recursos do ERP, utilizando PL/Sql, Forms 6i, Reports 6i, Discover, WorkFlow e APEX.
