Parâmetro opcional em stored procedure (mão na roda)

Hoje vou dar uma dica para facilitar o trabalho de debug de uma stored procedure. Vai em Oracle, porque é o que eu tenho na mão para mostrar (sem falar da minha preferência, claro).

Frequentemente, quando escrevo uma nova stored procedure (ou altero uma existente), eu acrecento um parâmetro adicional, opcional, que indica se a proc deve rodar em "modo debug". Coloco no final da lista de parâmetros (geralmente é o último) e como opcional, porque assim eu não preciso alterar nenhum programa que use esta proc, e ainda tenho a flexibilidade de usar o parâmetro.

"Mas o que faz este parâmetro?" - perguntarão alguns. Eu já disse! Indica se a proc deve rodar em modo debug.

"Modo debug?"

É. É um apelido, uma forma sofisticada de dizer que a proc vai fazer algum trabalho extra. No caso, o trabalho de coletar informações sobre a sua própria execução.
Essa coleta pode ser, exibir o valor de uma variável, indicar se entrou em um IF (ou se entrou no ELSE), exibir a quantide de registros lidos, etc...

Vamos ao exemplo prático:

(lembrando, é ORACLE e vc pode colar os comandos direto no SQL*Plus)

Imagine uma proc spProc1, que serve apenas para inserir descrições em uma tabela TabA, se não existirem, e retornar o Id gerado. Assim:

create table TabA (
Id number not null,
Descricao varchar2(50) not null,
DataCriacao date not null
);

alter table TabA add constraint PK_TabA primary key (Id);
alter table TabA add constraint UK_TabA_Descricao unique (Descricao);

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;

if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

Vamos inserir uns dados para ver que está tudo funcionando direito...

variable novo_id number

exec spProc1('Leonardo', :novo_id);
print novo_id;

exec spProc1('ImaginarTI', :novo_id);
print novo_id;

exec spProc1('Zacche', :novo_id);
print novo_id;

select * from TabA;

Agora vamos supor que você descobriu que, se ao chamar a proc, fornecer uma descrição que já exista na tabela, o ID retornado é nulo.

exec spProc1('Mais um', :novo_id);
print novo_id;

exec spProc1('Mais um', :novo_id);
print novo_id;

Depurar esta procedure é fácil porque é curta. Uma procedure longa, seria mais complicado. Para facilitar, vou colocar algumas linhas de DEBUG na procedure.

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;
dbms_output.put_line(vQtd || ' registro(s) encontrado(s).');


if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

Vamos testar novamente, com um novo valor, e depois repetí-lo.

set serveroutput on 100000
exec spProc1('Teste dois', :novo_id);
print novo_id;

exec spProc1('Teste dois', :novo_id);
print novo_id;

Descobrimos que não está entrando no IF. Vamos escrever um ELSE.

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
vQtd number;
begin

select count(1) into vQtd from TabA where Descricao = pDescricao;
dbms_output.put_line(vQtd || ' registro(s) encontrado(s).');


if (vQtd = 0) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

else

select Id into pId from TabA where Descricao = pDescricao;
dbms_output.put_line('O Id encontrado foi ' || pID);

end if;

end;
/
show errors


exec spProc1('Teste tres', :novo_id);
print novo_id;

exec spProc1('Teste tres', :novo_id);
print novo_id;

Agora a proc já está funcionando da forma que desejamos! Um último refactoring para otimizar o código e ...

create or replace procedure spProc1(pDescricao in varchar2, pId out number) as
begin

begin
select Id into pId from TabA where Descricao = pDescricao;
dbms_output.put_line('O Id encontrado foi ' || nvl(to_char(pID),'[nulo]'));
exception
when NO_DATA_FOUND then pId := null;
when OTHERS then raise;
end;


if (pId is null) then

select nvl(max(Id),0)+1 into pId from TabA;
dbms_output.put_line('O Id gerado foi ' || pID);

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors

exec spProc1('Teste quatro', :novo_id);
print novo_id;

exec spProc1('Teste quatro', :novo_id);
print novo_id;

Perfeito! A proc está exatamente como desejamos!

Agora podemos remover aquelas linhas de debug...

Mas seria interessante deixar essas linhas, para o caso de precisar depurar novamente...

Só que não é legal deixar essas linhas serem executadas SEMPRE, somente quando precisarmos
depurar.

Aí entra em cena o parâmetro opcional que comentei no início deste post. Como o parâmetro é OPCIONAL (todo parâmetro que tem um valor default pode ser otimido), você não precisa indicar o valor.

Assim, os programas que usam a proc não precisam ser alterados.

Eu defini o valor default como FALSE porque normalmente a proc não deve exibir informações de debug.

Nos casos (especiais) que eu quiser mostrar, informo explicitamente o valor true.

create or replace procedure spProc1(pDescricao in varchar2, pId out number, p_showDebug in boolean default false) as
begin

begin
select Id into pId from TabA where Descricao = pDescricao;
if (p_showDebug) then
dbms_output.put_line('O Id encontrado foi ' || nvl(to_char(pID),'[nulo]'));
end if;
exception
when NO_DATA_FOUND then pId := null;
when OTHERS then raise;
end;


if (pId is null) then

select nvl(max(Id),0)+1 into pId from TabA;
if (p_showDebug) then
dbms_output.put_line('O Id gerado foi ' || pID);
end if;

insert into TabA (Id, Descricao, DataCriacao)
values (pId, pDescricao, sysdate);

end if;

end;
/
show errors


exec spProc1('Teste cinco', :novo_id);
print novo_id;

exec spProc1('Teste cinco', :novo_id);
print novo_id;

exec spProc1('Teste cinco', :novo_id, true);
print novo_id;

exec spProc1('Teste seis', :novo_id, true);
print novo_id;

Abraços e até a próxima!

LZ

www.imaginarti.com