Function em PL/SQL

 

No Oracle, uma function é um bloco de código PL/SQL que executa operações específicas e retorna um valor. Functions são projetadas para realizar cálculos e retornar resultados que podem ser utilizados em consultas SQL, procedimentos armazenados e outros blocos de código PL/SQL.

Vantagens de Usar Functions

  • Reutilização de Código: Functions encapsulam a lógica complexa, permitindo sua reutilização em diferentes partes do código.
  • Facilidade de Manutenção: Alterações na lógica são feitas em um único lugar, simplificando a manutenção.
  • Modularidade: Functions ajudam a dividir o código em blocos menores e mais gerenciáveis.
  • Performance: Functions podem ser otimizadas e usadas em expressões SQL, melhorando o desempenho das consultas.

Tipos de Functions

Scalar Functions

Retornam um único valor e são usadas em expressões SQL.

 

Exemplo:

CREATE OR REPLACE FUNCTION calcular_bonus(p_salario NUMBER) RETURN NUMBER IS
  v_bonus NUMBER;
BEGIN
  v_bonus := p_salario * 0.10;
  RETURN v_bonus;
END calcular_bonus;

A função calcular_bonus recebe um valor de salário como entrada (parâmetro p_salario) e calcula um bônus correspondente a 10% desse salário. O valor do bônus é armazenado na variável v_bonus, que é então retornada pela função.

 


Table Functions

Retornam uma coleção de registros, como uma tabela.

 

Exemplo:

CREATE OR REPLACE FUNCTION obter_funcionarios(cargo_id NUMBER)
  RETURN SYS_REFCURSOR IS
  v_refcursor SYS_REFCURSOR;
BEGIN
  OPEN v_refcursor FOR
    SELECT * FROM funcionarios WHERE codigo_cargo = cargo_id;
  RETURN v_refcursor;
END obter_funcionarios;

A função obter_funcionarios recebe um valor de cargo_id como entrada e retorna um cursor de referência (SYS_REFCURSOR). Esse cursor de referência é aberto para uma consulta que seleciona todos os registros da tabela funcionarios onde o campo codigo_cargo corresponde ao cargo_id fornecido. A função então retorna o cursor, que pode ser usado para iterar sobre os registros resultantes da consulta.

 


Pipelined Table Functions

Permitem retornar linhas de uma tabela de forma incremental, melhorando a eficiência em certas operações.

 

Exemplo:

-- Tipo de registro correspondente à estrutura da tabela `funcionarios`
CREATE OR REPLACE TYPE tipo_funcionario AS OBJECT
(
  id           NUMBER,
  nome         VARCHAR2(50),
  salario      NUMBER,
  codigo_cargo NUMBER,
  status       VARCHAR2(10)
);

-- Tipo de tabela aninhada baseado no tipo de registro
CREATE OR REPLACE TYPE tipo_funcionario_tab IS TABLE OF tipo_funcionario;

-- Função Pipelined
CREATE OR REPLACE FUNCTION obter_funcionarios_pipelined(p_cargo_id NUMBER)
  RETURN tipo_funcionario_tab
  PIPELINED IS
  v_funcionario tipo_funcionario := tipo_funcionario(NULL,
                                                     NULL,
                                                     NULL,
                                                     NULL,
                                                     NULL);
BEGIN
  FOR r IN (SELECT id, nome, salario, codigo_cargo, status
              FROM funcionarios
             WHERE codigo_cargo = p_cargo_id) LOOP
    v_funcionario.id           := r.id;
    v_funcionario.nome         := r.nome;
    v_funcionario.salario      := r.salario;
    v_funcionario.codigo_cargo := r.codigo_cargo;
    v_funcionario.status       := r.status;
    PIPE ROW(v_funcionario);
  END LOOP;
  RETURN;
END obter_funcionarios_pipelined;

A função obter_funcionarios_pipelined recebe um valor de p_cargo_id como entrada e retorna uma tabela aninhada de registros tipo_funcionario. Para cada registro na tabela funcionarios que corresponde ao codigo_cargo fornecido, a função atribui os valores do registro a uma variável v_funcionario do tipo tipo_funcionario e envia essa variável através da instrução PIPE ROW. A função então retorna a coleção de registros.

 

Observação: Os tipos tipo_funcionario e tipo_funcionario_tab são definidos para corresponder à estrutura dos registros da tabela funcionarios. Isso é necessário para que a função pipelined possa retornar os registros de forma incremental. Certifique-se de criar esses tipos antes de definir a função.

Funções Determinísticas

Functions podem ser marcadas como DETERMINISTIC se sempre retornarem o mesmo resultado para os mesmos valores de entrada. Isso pode melhorar o desempenho, pois o Oracle pode otimizar a execução evitando chamadas repetidas.

 

Exemplo:

CREATE OR REPLACE FUNCTION calcular_desconto(p_preco NUMBER) RETURN NUMBER
  DETERMINISTIC IS
BEGIN
  RETURN p_preco * 0.95;
END calcular_desconto;

A função calcular_desconto recebe um valor p_preco como entrada e retorna esse valor multiplicado por 0.95, aplicando um desconto de 5%. A palavra-chave DETERMINISTIC indica que a função sempre retornará o mesmo resultado para o mesmo valor de entrada, permitindo ao Oracle otimizar o desempenho evitando execuções desnecessárias.

Chamando uma Function

Functions podem ser chamadas dentro de expressões SQL, PL/SQL ou em outros blocos PL/SQL.

 

Exemplo em SQL:

SELECT nome, salario, calcular_bonus(salario) AS bonus FROM funcionarios;

Neste exemplo, a função calcular_bonus é chamada para cada linha da tabela funcionarios, calculando o bônus com base no salário de cada funcionário e retornando o resultado como uma nova coluna chamada bonus.

 

NOMESALARIOBONUS
Carlos Silva7800,000780
Ana Souza2750,000275
Mariana Costa
Roberto Alves3000,000300
Fernanda Santos3300,000330
Lucas Oliveira4950,000495
Camila Pereira2595,450259,545
Ramon Vieira4950,000495

Exemplo em PL/SQL:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Bônus: ' || calcular_bonus(3000));
END;

Neste bloco PL/SQL, a função calcular_bonus é chamada com o valor de 3000 e o resultado é exibido utilizando a função DBMS_OUTPUT.PUT_LINE.

 

DBMS Output

Bônus: 300

Tratamento de Exceções em Functions

É importante tratar exceções em functions para garantir a robustez do código e a correta gestão de erros.

 

Exemplo:

CREATE OR REPLACE FUNCTION obter_salario(p_funcionario_id NUMBER)
  RETURN NUMBER IS
  v_salario NUMBER;
BEGIN
  SELECT salario
    INTO v_salario
    FROM funcionarios
   WHERE id = p_funcionario_id;
  RETURN v_salario;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Nenhum registro encontrado com o ID fornecido.');
    RETURN NULL;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Ocorreu um erro: ' || SQLERRM);
    RETURN NULL;
END obter_salario;

Observação: Neste exemplo, a função obter_salario tenta obter o salário de um funcionário pelo seu ID. Se nenhum registro for encontrado, uma mensagem é exibida. Se ocorrer qualquer outro erro, uma mensagem de erro é exibida e a função retorna NULL.

Usando SAVEPOINT em Functions

Você pode usar SAVEPOINT dentro de functions para marcar pontos na transação que podem ser revertidos sem afetar as operações anteriores ao ponto de salvamento.

 

Exemplo:

CREATE OR REPLACE FUNCTION atualizar_salario(p_funcionario_id     NUMBER,
                                             p_percentual_aumento NUMBER)
  RETURN VARCHAR2 IS
BEGIN
  UPDATE funcionarios
     SET salario = salario * (1 + p_percentual_aumento / 100)
   WHERE id = p_funcionario_id;

  SAVEPOINT ponto1;

  UPDATE funcionarios
     SET salario = salario * (1 + p_percentual_aumento / 200)
   WHERE id = p_funcionario_id;

  COMMIT;
  RETURN 'Salário atualizado com sucesso';

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO ponto1;
    DBMS_OUTPUT.PUT_LINE('Ocorreu um erro: ' || SQLERRM);
    RETURN 'Erro ao atualizar salário';
END atualizar_salario;

Observação: Neste exemplo, a função atualizar_salario tenta atualizar o salário de um funcionário. Após a primeira atualização, um ponto de salvamento (SAVEPOINT ponto1) é definido. Se ocorrer um problema durante a segunda atualização, a transação é revertida até o ponto de salvamento, preservando a primeira atualização.

Considerações Finais

 

Functions são uma ferramenta poderosa no Oracle para encapsular lógica complexa e melhorar a modularidade e reutilização do código. Compreender os diferentes tipos de functions e suas melhores práticas é essencial para otimizar a performance e garantir a eficiência do banco de dados.

 

Se você tiver alguma dúvida ou sugestão, deixe um comentário abaixo!

Deixe um comentário

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

Rolar para cima