Oracle PL/SQL – Funções (Functions) e Procedures

Oracle PL/SQL – Funções (Functions) e Procedures

Bom… hoje em mais um dia a dia de trabalho, rolou mais uma dúvida sobre o uso de procedures e funções no PL/SQL. Aí fiz a seguinte pergunta: Qual a diferença entre Procedure e Function no PL/SQL? Aí surgiram aquelas velhas respostas decoradas da faculdade: “Procedure não retorna valor!”, “Função retorna valor e procedure não retorna.”, entre outras. Vamos lá então:

No Oracle a diferença básica entre uma e outra é que a Function OBRIGATÓRIAMENTE tem que retornar um valor, você pode até criar a função e compilar ela sem um Return, mas na hora que você rodar esta função você vai obter um erro oracle dizendo que: “Function Retorned withou value”, ou seja, função não retorna nenhum valor (algo parecido), e não funciona. Porque a diferença básica? Porque procedures no Oracle também podem retornar valores, isso mesmo, basta você criar um parametro do tipo OUT, assim:

create or replace procedure pr_teste(p_t number, p_ret out varchar2) is
begin
   if(p_t = 1) then
      p_ret := 'É 1';
   else
      p_ret := 'Não é 1';
   end if;
end;

Neste exemplo de código, note que estamos atribuindo ao parâmetro p_ret o valor que será retornado para o local que chamou essa procedure (veremos exemplo desta chamada nos exemplos a seguir).

Ah… então, se as duas retorna valor, porque eu tenho procedure e function? E porque eu uso uma e não outra ou a outra e não uma?

Vamos aos pontos de cada uma delas:

Nas Funtions

• Pode ser usada em comandos select, insert etc para ser retornada como uma coluna da query:

select codigo, nome, fn_calculaIdade(codigo) Idade from pessoas;

Neste exemplo, fn_calculaIdade recebe o codigo da pessoa como parametro e retorna a idade dela, isso será exibido como uma coluna na query com nome Idade

• Pode ser atribuida diretamente a uma variável:

declare
   v_idade number;
begin
.
.
   v_idade := fn_calculaIdade(codigo);
.
.
end;

Neste exemplo, estamos no meio de um bloco PL/SQL e atribuímos diretamente a uma variável o valor que retornará da função.

E mais algumas coisas sobre função.

Nas Procedures

• O grande “plus” das procedures é: Podem retornar mais de um resultado! Ah… isso mesmo, essa é a grande vantagem das procedures, existe a possibilidade de ser retornado mais de um retorno (retornar retorno, coisa feia não? você entenderá jájá), coisa que é completamente impossível usando função. Funções só retornam um único resultado.

Complicou? Vamos ao exemplo:

Primeiro vamos criar uma procedure com 3 (isso mesmo 3, três, III, rsrs) parâmetros de retorno:

create or replace procedure pr_buscaEndereco(p_codigo_pessoa number, p_rua out varchar2, p_bairro out varchar2, p_cidade out varchar2) is
begin
   begin
      select rua,
      estado,
      cidade
   into p_rua,
      p_bairro,
      p_cidade
   from pessoas
   where codigo = p_codigo_pessoa;

   exception
      when no_data_found then
         p_rua := 'Rua não encontrada.';
         p_bairro := 'Bairro não encontrada.';
         p_cidade := 'Cidade não encontrada.'; 
   end; 
end;

Note que os parâmetros que serão usados para retorno, tem uma cláusula OUT na frente do tipo, isso que diferencia ele de um parâmetro comum, IN.

Ah… bom, mas e como é que eu vou usar isso? Assim:

declare
   v_pessoa_rua    varchar2(100);
   v_pessoa_bairro varchar2(100);
   v_pessoa_cidade varchar2(100);
begin
   pr_teste(212, v_pessoa_rua, v_pessoa_bairro, v_pessoa_cidade);
   dbms_output.put_line('Endereço da pessoa 212: Rua: '||v_pessoa_rua||' Bairro: '||v_pessoa_bairro||' Cidade: '||v_pessoa_cidade);
end;

Neste exemplo, temos três variáveis criadas, estas três variáveis são passadas na chamada da procedure e como estes parâmetros no qual elas são passadas são OUT, irão retornar algum valor, que será o valor da variável após execução da procedure.

É isso aí, portanto, caso você precise de uma função que retorne mais de um valor, não tente criar uma função genérica cheia de IF’s e chamar ela mais de uma vez fazendo o mesmo select em colunas diferentes, use uma procedure com mais de um parâmetro OUT que isso provavelmente resolverá seus problemas.

Momento DBA: Lembrem que quanto menos funções são chamadas, mais agradável para o banco. A cada função que chamamos o banco vai ter que ver, executar, retornar, isso usa memória, processador e etc. Se no lugar de 10 chamadas para uma função voce usar uma procedure com 10 parametros OUT, é bem menos “doloroso” para o banco. Outra coisa que é muito importante e que ocorre muito é usar função para retornar como uma determinada coluna de um comando select… lembre-se que se está função está na clausula select ela será chamada exatamente a quantidade de vezes de quantos registros existirem. No exemplo da função fn_calculaIdade que citei lá em cima, se tivermos 1.000.000 de registros, esta função será executada 1.000.000 de vezes, se é uma função mais robusta e complexa, imagine pra onde vai a performance da sua query. Além da perda de performance na query, de quebra você ainda perde uma credibilidade com o DBA! Que quando você degradar o banco todo, ele vai ficar bravo com você!!

Grande abraço a todos.

Fiquem a vontade para comentários e/ou e-mails.

Atc