Aula 18: Procedures e Functions em PL/SQL — criando lógica no banco

Bem-vindo à décima oitava aula do curso Oracle SQL — Do Zero ao Avançado. Hoje vamos tratar de um dos pilares da produtividade e da segurança em bancos de dados Oracle: o desenvolvimento de Procedures e Functions em PL/SQL. Se você trabalha com infraestrutura, segurança da informação ou administração de sistemas, certamente já se deparou com a necessidade de mover lógica de negócio da camada de aplicação para dentro do banco de dados. Esta aula é o ponto de virada que transforma você de um simples executor de queries para um desenvolvedor de soluções robustas, encapsuladas e reutilizáveis diretamente no SGBD.

Procedures e Functions em PL/SQL são blocos nomeados que residem no banco e podem ser invocados sob demanda. A diferença fundamental entre eles — que exploraremos com profundidade ao longo da aula — está no retorno: functions devolvem um valor escalar, procedures não. Mas essa é apenas a ponta do iceberg. Em nossos projetos na JRT Technology Solutions, nossos especialistas utilizam diariamente esses objetos para centralizar regras de validação, automatizar rotinas de manutenção e reduzir drasticamente o tráfego de rede em ambientes de missão crítica. Nesta aula você aprenderá não apenas a sintaxe, mas o raciocínio por trás de cada decisão de design.

Ao final desta aula, você será capaz de criar procedures com parâmetros de entrada e saída, construir functions escalares que participam de SELECTs, tratar exceções com elegância e testar cada bloco de forma isolada. Tudo isso seguindo boas práticas que aplicamos em implementações reais na JRT Technology Solutions. Vamos mergulhar no mundo da lógica de banco de dados com Oracle SQL Developer e SQL*Plus, garantindo que cada passo funcione exatamente como descrito.

O que você vai aprender nesta aula

• Diferenças conceituais e práticas entre Procedures e Functions em PL/SQL
• Criação, compilação e invocação de procedures com parâmetros IN, OUT e IN OUT
• Construção de functions escalares e seu uso em consultas SQL
• Tratamento de exceções com EXCEPTION e blocos WHEN
• Verificação de objetos compilados e diagnóstico de erros de compilação
• Boas práticas de nomenclatura, documentação e controle de privilégios
• Estratégias de otimização e debugging que usamos na JRT Technology Solutions

Pré-requisitos e Ambiente

Esta aula assume que você já possui um banco Oracle acessível (versão 19c ou superior é recomendada, mas o conteúdo é compatível a partir da 11gR2), com permissão para criar objetos no seu schema. Você precisará de acesso via SQL*Plus, SQLcl ou Oracle SQL Developer. Todos os exemplos foram testados em Oracle Database 21c Express Edition, tanto em Windows Server 2022 quanto em Oracle Linux 9 — em ambientes que montamos frequentemente nos laboratórios da JRT Technology Solutions. Verifique se o parâmetro PLSQL_CCFLAGS está em branco (padrão) e se você tem as roles RESOURCE e CONNECT concedidas.

Antes de prosseguir, confirme que o seu ambiente está funcional com o comando de verificação abaixo. Ele deve retornar a versão do banco e o status do catálogo PL/SQL.

-- Verificação do ambiente Oracle e PL/SQL
SELECT banner FROM v$version WHERE ROWNUM = 1;
SELECT object_name, status FROM user_objects WHERE object_type IN ('PROCEDURE','FUNCTION') AND ROWNUM <= 5;
SHOW USER;

BANNER
----------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

no rows selected

USER is "HR"

1. Conceitos Fundamentais de Procedures e Functions em PL/SQL

Antes de criarmos nosso primeiro objeto, precisamos alinhar a terminologia. Em PL/SQL, tanto procedures quanto functions são subprogramas armazenados no banco. Eles compartilham a mesma estrutura básica de bloco (DECLARE, BEGIN, EXCEPTION, END), mas diferem em três aspectos essenciais. Primeiro, Functions retornam um valor via cláusula RETURN, enquanto procedures não. Segundo, functions podem ser invocadas diretamente em expressões SQL (como SELECT minha_funcao(col) FROM tabela), desde que atendam a critérios de pureza. Terceiro, procedures são tipicamente usadas para realizar ações (operações DML, envio de e-mails, chamadas a APIs), enquanto functions processam e devolvem dados. A escolha errada entre procedure e function pode levar a gargalos de performance e problemas de manutenção — vemos isso com frequência nos diagnósticos que realizamos na JRT Technology Solutions.

Outro conceito crucial é o de parâmetros formais. Procedures e functions podem declarar parâmetros nos modos IN (entrada — somente leitura), OUT (saída — escrito pelo subprograma) e IN OUT (leitura e escrita). O modo padrão, quando omitido, é IN. Em functions, a boa prática recomenda evitar parâmetros OUT, pois a comunicação de volta ao chamador já é feita pelo retorno da função. Contudo, o Oracle permite, e há cenários legados onde isso aparece. A regra que seguimos na JRT Technology Solutions é clara: se a rotina precisa devolver mais de um valor, uma procedure com múltiplos parâmetros OUT é mais idiomática do que uma function com OUT disfarçado.

A tabela a seguir resume as diferenças críticas que você deve internalizar antes de programar:

2. Criando sua Primeira Procedure em PL/SQL

Vamos ao primeiro exemplo prático: uma procedure que insere um registro em uma tabela de log com data/hora e uma mensagem. Primeiro, crie a tabela de apoio execution_log. É fundamental que você execute cada comando na ordem apresentada. Em nossos treinamentos na JRT Technology Solutions, sempre começamos com a infraestrutura de monitoramento — isso salva horas de debugging posterior.

1. Conecte-se ao banco com seu usuário (neste exemplo, HR).
2. Crie a tabela execution_log com colunas id (autoincremento via sequence e trigger, ou identidade no 12c+), log_date e message.
3. Crie uma sequence e um trigger para preencher o ID, caso não esteja usando coluna identidade.
4. Crie a procedure log_message que recebe uma string e insere na tabela.
5. Execute a procedure e confira o resultado.

-- Criação da tabela de log (compatível com 12c+)
CREATE TABLE execution_log (
    id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    log_date  TIMESTAMP DEFAULT SYSTIMESTAMP,
    message   VARCHAR2(4000)
);

-- Procedure que insere uma mensagem de log
CREATE OR REPLACE PROCEDURE log_message (
    p_message IN VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION; -- Permite commit independente
BEGIN
    INSERT INTO execution_log (message) VALUES (p_message);
    COMMIT; -- Essencial para persistir imediatamente
END log_message;
/

Table EXECUTION_LOG created.

Procedure LOG_MESSAGE compiled

Note o uso de PRAGMA AUTONOMOUS_TRANSACTION. Essa diretiva diz ao Oracle que a procedure roda em uma transação independente da sessão chamadora. Sem ela, um COMMIT dentro da procedure afetaria toda a transação do chamador, o que raramente é desejável em procedimentos de log. Na JRT Technology Solutions, tratamos essa pragma como obrigatória para qualquer rotina de auditoria ou logging, evitando efeitos colaterais em transações de negócio. Após compilar, invocamos a procedure com EXEC ou CALL e verificamos a tabela.

-- Execução da procedure
EXEC log_message('Aula 18 - primeira procedure executada com sucesso');

-- Verificação do log
SELECT * FROM execution_log ORDER BY id DESC FETCH FIRST 3 ROWS ONLY;

PL/SQL procedure successfully completed.

ID   LOG_DATE                          MESSAGE
--   -------------------------------  -----------------------------------------
1    28-JUL-26 10.23.45.123456 AM     Aula 18 - primeira procedure executada com sucesso

3. Criando Functions e Entendendo o Mecanismo de Retorno

Agora que dominamos uma procedure simples, vamos construir uma function que calcula o dígito verificador de um CPF — um exemplo clássico que reaproveitamos em diversas implementações na JRT Technology Solutions para validação de dados cadastrais. A function receberá os 9 primeiros dígitos como número e retornará o dígito verificador completo (dois caracteres).

Functions são definidas com a sintaxe CREATE OR REPLACE FUNCTION nome (…) RETURN tipo. Dentro do corpo, em algum ponto deve existir um comando RETURN expressão;. O tipo de retorno pode ser qualquer tipo SQL escalar (NUMBER, VARCHAR2, DATE) ou tipos PL/SQL, desde que compatíveis com SQL. Uma vez compilada, a function pode ser testada com SELECT func FROM dual;. Essa é uma das grandes vantagens: você pode incorporar a lógica diretamente em queries, sem precisar de cursores explícitos.

-- Function para calcular dígito verificador de CPF
CREATE OR REPLACE FUNCTION calc_cpf_dv (
    p_base IN NUMBER
) RETURN VARCHAR2
IS
    v_base_str  VARCHAR2(9);
    v_sum       NUMBER := 0;
    v_dv        VARCHAR2(2);
    v_d1        NUMBER;
    v_d2        NUMBER;
BEGIN
    -- Preenche com zeros à esquerda até 9 dígitos
    v_base_str := LPAD(TO_CHAR(p_base), 9, '0');

    -- Primeiro dígito
    FOR i IN 1..9 LOOP
        v_sum := v_sum + TO_NUMBER(SUBSTR(v_base_str, i, 1)) * (11 - i);
    END LOOP;
    v_d1 := MOD(v_sum * 10, 11);
    IF v_d1 = 10 THEN v_d1 := 0; END IF;

    -- Segundo dígito
    v_sum := 0;
    FOR i IN 1..9 LOOP
        v_sum := v_sum + TO_NUMBER(SUBSTR(v_base_str, i, 1)) * (12 - i);
    END LOOP;
    v_sum := v_sum + v_d1 * 2;
    v_d2 := MOD(v_sum * 10, 11);
    IF v_d2 = 10 THEN v_d2 := 0; END IF;

    v_dv := TO_CHAR(v_d1) || TO_CHAR(v_d2);
    RETURN v_dv;
END calc_cpf_dv;
/

Function CALC_CPF_DV compiled

Para testar, utilizamos um CPF base conhecido. O número 123456789, por exemplo, tem dígitos verificadores calculáveis. Vamos invocar a function e ver o retorno.

SELECT calc_cpf_dv(123456789) AS digito_verificador FROM dual;

DIGITO_VERIFICADOR
------------------
09

4. Parâmetros em Procedures e Functions em PL/SQL — Modos e Boas Práticas

Um dos pontos que mais gera confusão em iniciantes — e que revisamos exaustivamente nos treinamentos da JRT Technology Solutions — é o uso correto dos modos de parâmetro. Cada modo tem implicações de performance e segurança. O modo IN é o padrão: o valor é copiado para uma variável local e pode ser lido, mas não alterado de forma que reflita no chamador. O modo OUT permite que a procedure escreva um valor que estará disponível para o chamador após a execução. Já o modo IN OUT combina ambos, mas deve ser evitado quando possível, pois obscurece a intenção do código.

Vamos criar uma procedure que recebe um salário (IN), um percentual de reajuste (IN) e devolve o novo salário (OUT) e o valor do aumento (OUT). Esse padrão é muito comum em rotinas de RH que migramos para dentro do banco nos projetos da JRT Technology Solutions, eliminando cálculos inconsistentes na aplicação.

-- Procedure com múltiplos parâmetros OUT
CREATE OR REPLACE PROCEDURE reajustar_salario (
    p_salario_atual  IN  NUMBER,
    p_percentual     IN  NUMBER,
    p_novo_salario   OUT NUMBER,
    p_valor_aumento  OUT NUMBER
) IS
BEGIN
    p_valor_aumento := ROUND(p_salario_atual * p_percentual / 100, 2);
    p_novo_salario  := p_salario_atual + p_valor_aumento;
END reajustar_salario;
/

Para invocar uma procedure com parâmetros OUT em SQL*Plus, precisamos declarar variáveis de bind com VARIABLE. Isso é um detalhe que faz muita diferença na prática: sem as variáveis de bind, você não consegue capturar os valores de saída.

-- Teste da procedure com parâmetros OUT
VARIABLE v_novo NUMBER;
VARIABLE v_aumento NUMBER;

EXEC reajustar_salario(5000, 10, :v_novo, :v_aumento);

PRINT v_novo;
PRINT v_aumento;

PL/SQL procedure successfully completed.

V_NOVO
----------
5500

V_AUMENTO
----------
500

A tabela abaixo compila os modos de parâmetro e suas características, servindo como referência rápida para qualquer projeto:

5. Tratamento de Exceções em Blocos PL/SQL

Nenhum código de produção sobrevive sem tratamento de erros. Em PL/SQL, utilizamos a seção EXCEPTION para capturar exceções predefinidas do Oracle (como NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX) ou exceções definidas pelo usuário. Na JRT Technology Solutions, implementamos um padrão onde toda procedure de negócio registra erros em uma tabela de auditoria, facilitando a rastreabilidade. Vamos construir uma function que busca o nome de um departamento pelo ID e trata a ausência de dados de forma elegante, sem propagar exceção não tratada.

-- Function com tratamento de exceção
CREATE OR REPLACE FUNCTION get_department_name (
    p_dept_id IN departments.department_id%TYPE
) RETURN VARCHAR2
IS
    v_name departments.department_name%TYPE;
BEGIN
    SELECT department_name INTO v_name
    FROM departments
    WHERE department_id = p_dept_id;
    
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Departamento inexistente';
    WHEN TOO_MANY_ROWS THEN
        -- Este caso é improvável com PK, mas serve de exemplo
        RETURN 'Múltiplos departamentos encontrados';
    WHEN OTHERS THEN
        -- Log genérico; em produção, usaria PRAGMA AUTONOMOUS_TRANSACTION
        RETURN 'Erro inesperado: ' || SQLERRM;
END get_department_name;
/

Function GET_DEPARTMENT_NAME compiled

Testamos com um ID existente e com um ID inválido, demonstrando que a function nunca lança exceção para o chamador, mas sim retorna mensagens amigáveis. Essa abordagem melhora a experiência do usuário e simplifica a depuração.

-- Teste com ID existente (assumindo departamento 50)
SELECT get_department_name(50) AS dept FROM dual;

-- Teste com ID inexistente
SELECT get_department_name(9999) AS dept FROM dual;

DEPT
-------------------------
Shipping

DEPT
-------------------------
Departamento inexistente

6. Verificando a Instalação / Testando a Configuração

Para garantir que todos os objetos foram criados corretamente e estão compilados, utilizamos as views do dicionário de dados. Esta etapa é obrigatória antes de entregar qualquer código em produção — e faz parte do checklist que mantemos na JRT Technology Solutions. Execute os comandos abaixo e confira se o status é VALID e se não há erros de compilação.

-- Listar procedures e functions do usuário e seu status
SELECT object_name, object_type, status, created
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION')
ORDER BY object_type, object_name;

OBJECT_NAME          OBJECT_TYPE   STATUS   CREATED
-------------------- ------------- -------- ------------------------
LOG_MESSAGE          PROCEDURE     VALID    28-JUL-2026 10:20:30
REAJUSTAR_SALARIO    PROCEDURE     VALID    28-JUL-2026 11:05:00
CALC_CPF_DV          FUNCTION      VALID    28-JUL-2026 10:35:12
GET_DEPARTMENT_NAME  FUNCTION      VALID    28-JUL-2026 11:10:45

Caso algum objeto apareça com status INVALID, você deve consultar a view USER_ERRORS para obter detalhes do problema de compilação. O comando a seguir exibe os erros com indicação de linha e coluna, essencial para troubleshooting.

-- Verificar erros de compilação (se houver)
SELECT name, type, line, position, text
FROM user_errors
WHERE name IN ('LOG_MESSAGE','REAJUSTAR_SALARIO','CALC_CPF_DV','GET_DEPARTMENT_NAME')
ORDER BY name, sequence;

no rows selected

7. Erros Comuns e Como Resolver

Durante os anos de consultoria na JRT Technology Solutions, catalogamos os erros mais frequentes que profissionais enfrentam ao criar Procedures e Functions em PL/SQL. Abaixo, listamos quatro situações reais com sintoma, causa e solução detalhada.

• PLS-00103: Encountered the symbol “CREATE”
  Sintoma: O SQL*Plus rejeita a criação do objeto e exibe esse erro logo na primeira linha.
  Causa: O comando CREATE OR REPLACE precisa ser a primeira instrução de um bloco PL/SQL, mas há um caractere não imprimível ou uma instrução anterior sem terminador adequado (falta de / ou ;).
  Solução: No SQL*Plus, sempre encerre o bloco com uma linha contendo apenas /. Se o erro persistir, digite LIST e verifique caracteres estranhos. Em ferramentas gráficas, remova espaços extras antes do CREATE.
• PLS-00904: insufficient privilege to access object
  Sintoma: A compilação falha mesmo com sintaxe correta, especialmente ao referenciar tabelas de outros schemas.
  Causa: Privilégios concedidos via role não se aplicam a objetos PL/SQL definidos com AUTHID DEFINER (padrão). As roles são desabilitadas durante a compilação.
  Solução: Conceda o privilégio diretamente ao usuário (GRANT SELECT ON schema.tabela TO usuario;), não via role. Se necessário, use AUTHID CURRENT_USER para herdar privilégios de roles no momento da execução, mas avalie impactos de segurança.
• ORA-06503: PL/SQL: Function returned without value
  Sintoma: Ao executar uma function, o Oracle retorna esse erro em tempo de execução, mesmo que a compilação tenha sido bem-sucedida.
  Causa: Há um caminho de execução dentro da function que não atinge um comando RETURN. Por exemplo, um IF sem ELSE que deixa o fluxo cair para o final do bloco.
  Solução: Garanta que todos os blocos condicionais convirjam para um RETURN ou que haja um RETURN padrão antes do EXCEPTION. Use ferramentas de cobertura de código para identificar branches descobertos.
• PLS-00306: wrong number or types of arguments in call
  Sintoma: A chamada da procedure/function falha informando número ou tipo incorreto de argumentos.
  Causa: Parâmetros obrigatórios omitidos, troca de posição de argumentos ou incompatibilidade de tipos (ex.: passar NUMBER onde se espera VARCHAR2 sem conversão implícita segura).
  Solução: Utilize notação nomeada (p_param => valor) para evitar dependência de ordem. Verifique as assinaturas com DESCRIBE nome_objeto e confirme os tipos de dados exatos.

8. Boas Práticas e Dicas Avançadas de Procedures e Functions em PL/SQL

Escrever código que funciona é o primeiro passo; escrever código que escala e se mantém é o diferencial. Na JRT Technology Solutions, seguimos um conjunto de diretrizes que compartilho agora. Primeiro, sempre prefixe parâmetros com p_ e variáveis locais com v_. Isso evita colisão com nomes de colunas e torna o código autoexplicativo. Segundo, utilize %TYPE e %ROWTYPE sempre que possível, ancorando variáveis a colunas de tabelas ou cursores. Isso protege seu código contra alterações futuras no modelo de dados. Terceiro, evite WHEN OTHERS sem registro de log; capture exceções específicas e relance o erro se não puder tratá-lo adequadamente com RAISE ou RAISE_APPLICATION_ERROR.

Para debugging, o Oracle oferece o pacote DBMS_OUTPUT.PUT_LINE, que exige SET SERVEROUTPUT ON no SQL*Plus. Em nossas fábricas de código, também utilizamos o pacote DBMS_UTILITY.FORMAT_CALL_STACK para rastrear a pilha de chamadas em exceções. Outra técnica avançada é o uso de RESULT_CACHE em functions determinísticas que são invocadas repetidamente com os mesmos parâmetros — isso pode reduzir drasticamente o consumo de CPU em ambientes concorridos.

Por fim, considere empacotar procedures e functions relacionadas em packages — tópico que será abordado na próxima aula. Packages permitem sobrecarga, estado compartilhado e melhor organização do código. Mas mesmo em scripts independentes, manter a coesão e documentar cada objeto com comentários sobre propósito, autor e data de criação é um hábito que recomendamos fortemente nos treinamentos da JRT Technology Solutions.

Resumo da Aula 18

Nesta aula, você aprendeu a criar Procedures e Functions em PL/SQL do zero, entendendo suas diferenças, modos de parâmetros, tratamento de exceções e boas práticas. Começamos com uma procedure de log usando PRAGMA AUTONOMOUS_TRANSACTION, passamos por uma function de cálculo de dígito verificador e exploramos parâmetros OUT com variáveis de bind no SQL*Plus. Verificamos a compilação via dicionário de dados e discutimos os quatro erros mais comuns e suas soluções, baseados na experiência real da JRT Technology Solutions.

A tabela de referência rápida abaixo consolida os comandos essenciais que você deve ter à mão:

Com essa base sólida, você está pronto para projetos mais complexos. Na Aula 19, vamos elevar o nível: mergulharemos em Packages, onde agruparemos procedures e functions relacionadas, exploraremos sobrecarga de subprogramas e variáveis de estado persistente — um dos recursos mais poderosos do PL/SQL para construir APIs de banco de dados coesas e profissionais. Até lá!