Guia Prático: Criação e Uso de Procedimentos no Oracle Database

Procedimentos armazenados no Oracle PL/SQL são blocos de código SQL e PL/SQL compilados e armazenados diretamente no banco de dados. Eles permitem encapsular lógica de negócios complexa, reduzir o tráfego de rede e aprimorar o desempenho através de execução pré-compilada.

1. Vantagens e Estruutra Básica

A modularização através de procedimentos oferece benefícios significativos:

• Reutilização: O código é criado uma única vez e pode ser chamado por múltiplas aplicações.
• Performance: Operações que envolvem múltiplas instruções SQL são executadas mais rapidamente no servidor.
• Segurança: É possível conceder permissão de execução a um usuário sem dar acesso direto às tabelas subjacentes.
• Manutenção: A lógica centralizada facilita atualizações e correções.

A sintaxe básica de criação segue a estrutura a seguir:

CREATE [OR REPLACE] PROCEDURE nome_procedimento (parametro1 IN tipo, parametro2 OUT tipo)
AS
    -- Declaração de variáveis locais
    variavel_local tipo;
BEGIN
    -- Bloco executável
EXCEPTION
    -- Tratamento de erros
END;

Os parâmetros podem ter três modos: IN (valor de entrada, padrão), OUT (valor de saída) e IN OUT (valor de entrada e saída).

2. Exemplos Práticos Detalhados

Exemplo 1: Procedimento Simpsem Parâmetros

Este procedimento demonstra a declaração de variáveis e a saída de dados usando DBMS_OUTPUT.

CREATE OR REPLACE PROCEDURE exibir_info_funcionario
AS
    v_nome_func VARCHAR2(50) := 'Ana Silva';
    v_idade_func NUMBER := 30;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Nome: ' || v_nome_func || ', Idade: ' || v_idade_func);
END;
/

-- Execução
BEGIN
    exibir_info_funcionario;
END;

Exemplo 2: Procedimento com Parâmetros de Entrada

Parâmetros de entrada (IN) permitem passar valores para o procedimento.

CREATE OR REPLACE PROCEDURE mostrar_detalhes_salario(
    p_nome_func IN VARCHAR2,
    p_salario_func IN NUMBER
)
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Funcionário: ' || p_nome_func || ', Salário: R$' || p_salario_func);
END;
/

-- Execução
BEGIN
    mostrar_detalhes_salario('Carlos Eduardo', 4500.00);
END;

Exemplo 3: Tratamento de Exceções

O bloco EXCEPTION captura erros em tempo de execução.

CREATE OR REPLACE PROCEDURE calcular_bonus
AS
    v_salario NUMBER := 5000;
    v_bonus NUMBER;
BEGIN
    v_bonus := v_salario / 0; -- Causará um erro de divisão por zero
    DBMS_OUTPUT.PUT_LINE('Bônus: ' || v_bonus);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Erro ao calcular bônus: ' || SQLERRM);
END;
/

-- Execução
BEGIN
    calcular_bonus;
END;

3. Interação com Dados e Parâmetros de Saída

Consultando Dados e Usendo Parâmetros OUT

Procedimentos podem retornar valores através de parâmetros OUT ou IN OUT.

CREATE OR REPLACE PROCEDURE obter_salario_funcionario(
    p_cod_emp IN NUMBER,
    p_sal_emp OUT NUMBER,
    p_nome_emp OUT VARCHAR2
)
AS
BEGIN
    SELECT NOME, SALARIO INTO p_nome_emp, p_sal_emp
    FROM FUNCIONARIOS
    WHERE COD_FUNCIONARIO = p_cod_emp;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_sal_emp := NULL;
        p_nome_emp := NULL;
        DBMS_OUTPUT.PUT_LINE('Funcionário não encontrado.');
END;
/

-- Execução com bloco anônimo
DECLARE
    v_sal NUMBER;
    v_nome VARCHAR2(100);
BEGIN
    obter_salario_funcionario(101, v_sal, v_nome);
    IF v_sal IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(v_nome || ' ganha R$' || v_sal);
    END IF;
END;

Realizando Inserções com Tratamento de Erros

CREATE OR REPLACE PROCEDURE inserir_novo_projeto(
    p_nome_projeto IN VARCHAR2,
    p_gerente_id IN NUMBER,
    p_status_saida OUT VARCHAR2
)
AS
BEGIN
    INSERT INTO PROJETOS (NOME_PROJETO, GERENTE_ID, DT_CRIACAO)
    VALUES (p_nome_projeto, p_gerente_id, SYSDATE);
    COMMIT;
    p_status_saida := 'SUCESSO';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        p_status_saida := 'FALHA: ' || SQLERRM;
END;
/

-- Execução
DECLARE
    v_resultado VARCHAR2(200);
BEGIN
    inserir_novo_projeto('Projeto Alpha', 205, v_resultado);
    DBMS_OUTPUT.PUT_LINE('Status da Inserção: ' || v_resultado);
END;

4. Uso de Cursores para Processamento de Linhas Múltiplas

Cursores explícitos permitem recuperar e processar um conjunto de linhas de resultado.

CREATE OR REPLACE PROCEDURE listar_salarios_depto(p_dept_id IN NUMBER)
AS
    CURSOR c_emp_dept IS
        SELECT NOME, SALARIO FROM FUNCIONARIOS WHERE DEPT_ID = p_dept_id;
    v_registro c_emp_dept%ROWTYPE;
BEGIN
    OPEN c_emp_dept;
    LOOP
        FETCH c_emp_dept INTO v_registro;
        EXIT WHEN c_emp_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Func: ' || v_registro.NOME || ', Sal: ' || v_registro.SALARIO);
    END LOOP;
    CLOSE c_emp_dept;
EXCEPTION
    WHEN OTHERS THEN
        IF c_emp_dept%ISOPEN THEN CLOSE c_emp_dept; END IF;
        RAISE;
END;
/

-- Execução
BEGIN
    listar_salarios_depto(10);
END;

5. Manipulação de Dados e Atributos de SQL Implícito

Após uma operação DML, os atributos do cursor implícito SQL fornecem informações sobre a execução.

CREATE OR REPLACE PROCEDURE atualizar_comissao(
    p_cod_func IN NUMBER,
    p_percentual IN NUMBER
)
AS
BEGIN
    UPDATE FUNCIONARIOS
    SET COMISSAO = SALARIO * (p_percentual / 100)
    WHERE COD_FUNCIONARIO = p_cod_func;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Comissão atualizada para ' || SQL%ROWCOUNT || ' funcionário(s).');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Nenhum funcionário encontrado com o código informado.');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Erro durante a atualização: ' || SQLERRM);
END;
/

-- Execução
BEGIN
    atualizar_comissao(101, 10);
END;