Qual a diferença entre Function e Procedure no Oracle?

Qual a diferença entre Function e Procedure no Oracle? Veja as diferenças entre uma Function e uma Procedure no Oracle e quando utilizar cada uma.

Estudando Pl/SQl me deparei com uma situação que já deve ter passado na cabeça de muitos e se você esta lendo é claro que também esta enfrentando o mesmo dilema. Afinal qual a diferença entre function e procedure e quando devo utilizar cada uma? Continue lendo para entender os detalhes destes semelhantes porem ‘diferentes’ blocos Oracle.

Seja bem vindo ao primeiro artigo sobre tecnologia aqui no site, meu nome é William Machado Costa, sou desenvolvedor .Net, Java Script e preciso estudar PL SQL para atender as necessidades de um novo trabalho. Seguindo orientações de Rodrigo Branas resolvi escrever sobre o que estudo afim de aprofundar os conhecimentos. Também sou empreendedor e tenho mais dois sites que junto a este formam o grupo WMC. Vem comigo!

Qual a diferença entre uma Procedure e uma Function?

Uma function é um bloco PL/SQL muito semelhante a uma procedure. Porem apresenta algumas diferenças que são cruciais para um bom entendimento dos mesmos.

• Functions retornam valores ;
• Procedures podem ou não retornar um valor.

• Functions podem ser usadas em uma instrução SELECT;
• Procedures não podem ser usadas em uma instrução SELEC.

• Functions podem ter apenas parâmetros de entrada;
• Procedures podem ter parâmetros de entrada / saída.

• Functions podem ser chamadas de dentro de Procedures ;
• Procedures não podem ser chamados a partir de functions.

• Não pode utilizar Commit ou Rowback em Functions

O que é uma Function?

Uma função armazenada (também chamada de função de usuário ou função definida pelo usuário ) é um conjunto de instruções PL / SQL que você pode chamar pelo nome. As funções armazenadas são muito semelhantes aos procedimentos armazenados (procedure), exceto que uma função retorna um valor para o ambiente no qual é chamada. 

Podemos passar os parâmetros para uma function apenas através do IN:

1. Parâmetros IN – passamos o valor na própria function.

Veja a seguir a estrutura de uma função:

CREATE OR REPLACE FUNCTION get_total_sales(
in_year PLS_INTEGER
)
RETURN NUMBER
IS
l_total_sales NUMBER := 0;
BEGIN
-- get total sales
SELECT SUM(unit_price * quantity)
INTO l_total_sales
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY EXTRACT(YEAR FROM order_date)
HAVING EXTRACT(YEAR FROM order_date) = in_year;

Podemos executar a função de algumas formas:

Podemos executar a função como uma instrução PL/SQL

dbms_output.put_line( get_total_sales);

Como parte de uma instrução Select

SELECT   get_total_sales FROM dual;

Como uma variável

get_total :=  get_total_sales;

O que é uma Procedure?

Um procedimento PL / SQL é uma unidade reutilizável que encapsula a lógica de negócios específica do aplicativo. Tecnicamente falando, um procedimento PL / SQL é um bloco nomeado armazenado como um objeto de esquema no banco de dados Oracle.

Uma procedure normalmente possui um cabeçalho e um corpo. O cabeçalho consiste do nome e de parâmetros ou variáveis que serão passadas para a procedure. Já o corpo consiste da declaração de uma seção, execução de uma seção e uma seção de exceções muito similar a um bloco geral da PL/SQL. Uma procedure pode ou não ter um valor de retorno. Normalmente as procedures são criadas dentro de pacotes ou em blocos PL/SQL.

Podemos passar os parâmetros para uma procedure de três maneiras:

1. Parâmetros IN – passamos o valor na própria procedure.
2. Parâmetros OUT – recebemos o valor a partir da chamada de blocos externos.
3. Parâmetros IN OUT – passamos um valor inicial para a procedure e recebemos de volta uma atualização.

Exemplo de uma Procedure PL / SQL

O procedimento a seguir aceita um ID de cliente e imprime as informações de contato do cliente, incluindo nome, sobrenome e e-mail:

CREATE OR REPLACE PROCEDURE print_contact(
    in_customer_id NUMBER 
)
IS
  r_contact contacts%ROWTYPE;
BEGIN
  -- get contact based on customer id
  SELECT *
  INTO r_contact
  FROM contacts
  WHERE customer_id = p_customer_id;

  -- print out contact's information
  dbms_output.put_line( r_contact.first_name || ' ' ||
  r_contact.last_name || '<' || r_contact.email ||'>' );

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line( SQLERRM );
END;

Executando um procedimento PL / SQL

Podemos executar uma procedure da seguinte forma:

EXECUTE procedure_name( arguments); ou
EXEC procedure_name( arguments);

Conclusão

Apesar da semelhança entre os blocos, existem diferenças bem acentuadas quando olhamos com mais detalhes para sua a estrutura lógica e funcionalidade dos mesmos.

Caso precise retornar um único valor, utilize Function

Caso precise retornar mais de um valor utilize Procedure

Caso não precise retornar valor utilize Procedure

Caso tenha que executar instrução DML, utilize Procedure

Veja abaixo um pequeno resumo do que discutimos aqui extraída do site Profissionais do Oracle escrita por GERSONJR .

É 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.

https://www.profissionaloracle.com.br/

Oracle fornece várias funções já predefinidas que viabilizam muitas operações, mas não confunda funções próprias do Oracle com funções definidas em PL\SQL. Veja aqui as funções Oracle.