
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
.
NOME | SALARIO | BONUS |
---|---|---|
Carlos Silva | 7800,000 | 780 |
Ana Souza | 2750,000 | 275 |
Mariana Costa | ||
Roberto Alves | 3000,000 | 300 |
Fernanda Santos | 3300,000 | 330 |
Lucas Oliveira | 4950,000 | 495 |
Camila Pereira | 2595,450 | 259,545 |
Ramon Vieira | 4950,000 | 495 |
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!