Oralce PL/SQL – Funções (Funtions) e Procedures

Fala PessoAll,

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.
Gerson Júnior
gerson.vasconcelos@gmail.com

16 ideias sobre “Oralce PL/SQL – Funções (Funtions) e Procedures

  1. Leandro Lister Beividas

    Ok, agora me diz uma coisa, eu estou com um problema no trabalho que preciso retornar um status e um número, beeeleza, só fazer uma procedure e ja eras..

    O problema é que eu precisa que o P_NUMERO seja uma coluna da minha SELECT QUERY e o outro parametro, vamos chamar de P_STATUS, seja outro campo do meu SELECT.

    O parametro IN da minha procedure precisa receber um dos outros campos deste mesmo SELECT.

    Iai, como faço? A unica alternativa que me sobrou foi criar duas funções e chama-las na query… Você teria outra idéia para me dar por favor?

    Responder
    1. oracle Autor do post

      Fala Leandro,
      Muito obrigado pela visita e pelo comentário.

      Não consegui entender muito bem o seu caso.
      Você tem um select e dois campos deste select precisam ser o resultado de um calculo, que você pretende fazer na function, é isso?

      Este select roda onde? Direto na aplicação?
      Se sim, se for direto na aplicação, você tem duas opções:

      1 – Criar uma função para cada campo, que recebe os parametros, que já serão colunas do próprio select e retornam um calor, cada uma. F_CALCULA_NUMERO e outra F_CALCULA_STATUS, por exemplo.

      2 – Outra opção é criar uma única função, que recebe um parametro ‘S’ para status e ‘N’ para número, e você chama ela duas vezes, uma em cada campo do seu select.

      Já se isso estiver num PL/SQL, que acho não ser o caso… você pode chamar a procedure antes do select, que vai popular os dois parâmetros out e então você usa os parâmetros diretamente no select.

      Qualquer dúvida, só falar!!

      Abraços!

      Responder
  2. euzico.biombo

    UMA DÚVIDA
    TENTEI INSTALAR ORACLE 10G NA MINHA MAQUINA NÃO CONSEGUI NO SITE DE ORACLE, SABE ONDE POSSO ENCONTRAR?
    ESTOU ESTUDANDO VÍDEO AULA ORACLE, MAS SÓ TEM VIDEO AULA DE 10G EM PORTUGUÊS.
    DESDE JÁ OBRIGADO.

    Responder
  3. Adriano

    Amigo,

    Suas dicas tem sido de grande valor para agregar conhecimento. Gostaria s epossível um aluz num problema que tenho. Seguinte cenário:

    Num_Acidente Tipo
    0123 Vitima
    0123 Testemunha
    0123 Terceiro
    0415 Vitima
    0415 Terceiro
    0897 Testemunha
    0684 Testemunha
    0684 Terceiro

    Preciso criar um relatório onde trago os acidentes que não possuem vítimas. Neste exemplo traria o acidente 0897 e 0684. Estou a dois dias quebrando a cabeça, se puder me ajudar ficarei muito grato.

    Obrigado
    Abraço
    Adriano

    Responder
    1. oracle Autor do post

      Olá Adriano (suponho que seja Adriano, se for Adrinao mesmo me desculpe), mandei até para os dois e-mails.

      Primeiro obrigado pela visita no site http://www.diaadiaoracle.com.br.
      Espero que tenha realmente gostado do conteúdo e que o ajude sempre. Caso precise de algo que não esteja lá, fique a vontade de avisar que tentamos aprender juntos e postamos lá.

      Vamos a este caso… criei um cenário aqui pra fazer o teste. O script segue abaixo.

      create table tacidente(num_acidente number, tipo varchar2(10));
      insert into tacidente (num_acidente, tipo) values (0123, 'Vitima');
      insert into tacidente (num_acidente, tipo) values (0123, 'Testemunha');
      insert into tacidente (num_acidente, tipo) values (0123, 'Terceiro');
      insert into tacidente (num_acidente, tipo) values (0415, 'Vitima');
      insert into tacidente (num_acidente, tipo) values (0415, 'Terceiro');
      insert into tacidente (num_acidente, tipo) values (0897, 'Testemunha');
      insert into tacidente (num_acidente, tipo) values (0684, 'Testemunha');
      insert into tacidente (num_acidente, tipo) values (0684, 'Terceiro');
      commit;

      Depois vem o select…. Para saber os que não tem vítima, uma forma é saber todos que tem e pegar os que não estiverem nesta lista, que é o caminho que adotei no select abaixo:


      select distinct num_acidente
      from tacidente
      where num_acidente not in (select num_acidente
      from tacidente
      where upper(tipo)='VITIMA')

      Neste caso, coloquei o distinct porque pode ter acidente que tem mais de um tipo e não é vítima e aí repete o código.

      Espero que te ajude.

      Abraços!
      Gerson Júnior
      gerson.vasconcelos@gmail.com

      Responder
    1. oracle Autor do post

      Bom dia José Machado.
      Primeiro, obrigado pelo seu comentário/pergunta em meu site Dia a Dia Oracle.

      Cara, se você remover o Oracle do PC, imaginando que tenha um banco de dados qualquer instalado nele, possivelmente todos os datafiles serão excluídos junto com a desinstalação, e caso não tenha backup, você perderá tudo que estiver neste banco. Além disso, se for um servidor, todos os aplicativos que utilizavam este banco para qualquer fim, deixarão de funcionar corretamente.

      Espero ter entendido sua pergunta, caso contrário, só explicar melhor que tentamos pensar juntos, ok?

      Obrigado!!

      Responder

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *