Stored Procedures na prática: implementando regras de negócio com PL/SQL

Que é possível criar métodos e funções para manipular um banco de dados no back-end, isso você já sabe. Mas e se eu te contar que também é possível criar lógica e armazená-la dentro do próprio banco melhorando nossas aplicações? Stored Procedures (procedimentos armazenados) são uma arma poderosa no desenvolvimento de sistemas que usam banco de dados. Neste artigo, vamos entender o que são procedures, quais as vantagens de usá-las e como funcionam na prática, com um exemplo de transferência bancária em PL/SQL (Oracle). No final, veremos como chamá-las a partir de uma aplicação Spring Boot. Boa leitura!

Sumário

O que é uma Procedure?
Qual a vantagem de usar Procedures?
Estrutura e sintaxe explicada
Conectando com o Back-End
Conclusão

O que é uma Procedure?

Uma procedure é um bloco de código que armazenamos no banco de dados e que pode ser chamado pelo nome quantas vezes for preciso. Ela pode receber parâmetros, executar comandos SQL e lógica procedural. Por isso, é muito usada para encapsular regras de negócio complexas, tarefas repetitivas e lógicas que são compartilhadas em vários contextos. Cada procedure criada tem uma tarefa específica geralmente descrita no seu nome, assim como em um método.

Vamos aprender usando o exemplo da procedure transferir(), que vai realizar uma transferência bancária. Para fins de esclarecimento, vale lembrar que cada SGBD implementa esse conceito com sua própria sintaxe, e neste artigo usaremos o PL/SQL, a extensão procedural da Oracle para o SQL.

Qual a vantagem de usar Procedures?

Agora que já sabemos o que é uma procedure, fica a pergunta: qual a real vantagem de ter esse trabalho todo?

A boa notícia é que, com esse código armazenado, nosso sistema ganha em vários aspectos:

• Diminuição do tráfego na rede, pois são menos idas e vindas entre a aplicação e o banco;
• Reusabilidade de código, já que uma vez criado ele pode ser usado em diversos componentes e até por aplicações diferentes, independente do back-end;
• Consistência, porque regras críticas (como debitar, creditar e registrar uma transferência) ficam junto dos dados e seguem sempre o mesmo caminho;
• Mais segurança, uma vez que a lógica fica abstraída no banco sendo acessada via procedure sem dar à aplicação o acesso direto às tabelas.

Algumas dessas vantagens, como a redução do tráfego, também podem melhorar a performance da aplicação. Contudo, é válido lembrar que as procedures também têm pontos negativos:

• Maior dependência do banco escolhido, o que dificulta a migração;
• Maior dificuldade para testes, visto que testar dentro do banco não é tão trivial;
• Lógica dividida entre banco e aplicação, o que exige organização para não se perder, sobretudo com muitas procedures.

Estrutura e sintaxe explicada

Agora vamos entender como criar uma procedure e, como dito antes, usaremos a sintaxe PL/SQL. Neste exemplo vamos criar a procedure transferir() que realiza uma transferência bancária. Aqui temos um primeiro esqueleto:

CREATE OR REPLACE PROCEDURE transferir()
IS
BEGIN
    -- Lógica aqui
END;
/

• CREATE OR REPLACE PROCEDURE: instrui o banco a criar a procedure ou, se ela já existir, substituí-la;
• transferir(): é o nome da procedure que se assemelha muito ao nome de um método/função. E da mesma forma, é aqui que vamos receber os parâmetros;
• IS: palavra-chave que marca o fim do cabeçalho e início da seção de declarações. Aqui vamos declarar as variáveis;
• BEGIN/END: entre esses comandos colocamos toda a lógica da procedure (validações, condições) e a manipulação do banco (inserts, selects). O END sempre termina com ponto e vírgula;
• /: sinaliza ao cliente que o bloco terminou, mas não faz parte da procedure. Em outros SGBDs existem outras formas de delimitação.

O próximo passo é adicionar lógica à nossa procedure. Para isso precisamos de parâmetros e também variáveis. Veja:

CREATE OR REPLACE PROCEDURE transferir (
    p_valor            IN NUMBER,
    p_conta_origem_id  IN NUMBER,
    p_conta_destino_id IN NUMBER
)
IS
    v_saldo_origem NUMBER;
    v_saldo_destino NUMBER;
BEGIN
    SELECT saldo INTO v_saldo_origem
      FROM contas
     WHERE id = p_conta_origem_id;

    -- Garante que a conta de destino existe
    SELECT saldo INTO v_saldo_destino
      FROM contas
     WHERE id = p_conta_destino_id;

    UPDATE contas SET saldo = saldo - p_valor WHERE id = p_conta_origem_id;
    UPDATE contas SET saldo = saldo + p_valor WHERE id = p_conta_destino_id;

    INSERT INTO transacoes (valor, conta_origem_id, conta_destino_id)
    VALUES (p_valor, p_conta_origem_id, p_conta_destino_id);
END;
/

Vamos ver o que fizemos até aqui:

• IN / OUT / IN OUT: o modo do parâmetro. IN é entrada (a procedure lê o valor e não pode alterá-lo); OUT é saída (a procedure atribui um valor que volta para quem chamou); IN OUT entra com um valor e pode devolvê-lo modificado. Todos os nossos parâmetros são IN;
• p_valor IN NUMBER: nome, modo e tipo do parâmetro. Neste caso o valor é numérico e vale dizer que cada SGBD aceita tipos específicos;
• v_saldo_origem NUMBER: variável que recebe o saldo da conta de origem no SELECT;
• v_saldo_destino NUMBER: variável que recebe o saldo da conta de destino. Aqui só a usamos para confirmar que a conta existe.

O restante é um código SQL simples com SELECT (leitura dos saldos), UPDATE (débito e crédito nas contas origem e destino), e o INSERT (registro da transferência). Mas perceba que nosso código ainda não valida nada: se o saldo for insuficiente, a conta ficaria negativa, e se o valor for negativo, a transferência andaria no sentido contrário. Por enquanto, ele não é seguro.

Então vamos adicionar validações e lançar mensagens de erro!

CREATE OR REPLACE PROCEDURE transferir (
    p_valor            IN NUMBER,
    p_conta_origem_id  IN NUMBER,
    p_conta_destino_id IN NUMBER
) IS
    v_saldo_origem  NUMBER;
    v_saldo_destino NUMBER;
BEGIN
    IF p_conta_origem_id = p_conta_destino_id THEN
        RAISE_APPLICATION_ERROR(-20001, 'Os números precisam ser de contas diferentes!');
    END IF;

    IF p_valor <= 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'O valor de transferência precisa ser maior que 0!');
    END IF;

    -- O FOR UPDATE trava a conta de origem e lê o saldo
    SELECT saldo INTO v_saldo_origem
      FROM contas
     WHERE id = p_conta_origem_id
       FOR UPDATE;

    -- Confirma que a conta de destino existe (senão, cai no NO_DATA_FOUND)
    SELECT saldo INTO v_saldo_destino
      FROM contas
     WHERE id = p_conta_destino_id;

    IF v_saldo_origem >= p_valor THEN
        UPDATE contas SET saldo = saldo - p_valor WHERE id = p_conta_origem_id;
        UPDATE contas SET saldo = saldo + p_valor WHERE id = p_conta_destino_id;

        INSERT INTO transacoes (valor, conta_origem_id, conta_destino_id)
        VALUES (p_valor, p_conta_origem_id, p_conta_destino_id);
    ELSE
        RAISE_APPLICATION_ERROR(-20003, 'O saldo disponível é insuficiente!');
    END IF;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20004, 'Conta não encontrada');
END;
/

Agora sim estamos validando todos os cenários e lançando erros:

• Se as contas de destino e origem forem iguais;
• Se o valor a ser transferido for menor ou igual a zero;
• Se o saldo da conta de origem for insuficiente para a transferência;
• Se uma ou as duas contas não existirem.

Para isso, RAISE_APPLICATION_ERROR lança um erro personalizado, com código entre -20000 e -20999 e uma mensagem. NO_DATA_FOUND é uma exceção do próprio Oracle, disparada quando um SELECT ... INTO não encontra nenhuma linha. Nós a tratamos na seção EXCEPTION.

O FOR UPDATE trava a linha da conta de origem até o fim da transação. Sem ele, duas transferências simultâneas poderiam ler o mesmo saldo e gastar o mesmo dinheiro duas vezes.

Agora uma parte importante. Nosso código não faz commit, ou seja, as alterações não são definitivas. Isso é proposital, pois deixamos a parte de confirmar a transação para quem chama a procedure. Por isso, depois de chamar a procedure, rode COMMIT; para tornar as alterações definitivas (ou ROLLBACK; para desfazê-las).

Para testar até aqui, você precisa de um SGBD (neste caso, o Oracle) e de um cliente de banco de dados, como o DBeaver, que serve como interface para enviar os comandos ao banco. Mas antes, é necessário criar as tabelas clientes, contas e transacoes, essa última para registrarmos as transferências, exatamente nessa ordem, por causa das chaves estrangeiras. Além disso, é preciso inserir clientes e contas para a transferência ter com o que trabalhar.

Tabela clientes

CREATE TABLE clientes (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    nome VARCHAR2(100) NOT NULL,
    email VARCHAR2(150) NOT NULL UNIQUE,
    cpf VARCHAR2(11) NOT NULL UNIQUE,
    CONSTRAINT clientes_pk PRIMARY KEY (id)
);

Tabela contas

CREATE TABLE contas (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    cliente_id NUMBER NOT NULL,
    numero VARCHAR2(100) NOT NULL UNIQUE,
    saldo NUMBER(15,2) DEFAULT 0 NOT NULL,
    CONSTRAINT contas_pk PRIMARY KEY (id),
    CONSTRAINT fk_contas_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

Tabela transacoes

CREATE TABLE transacoes (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    valor NUMBER(15,2) NOT NULL,
    conta_origem_id NUMBER NOT NULL,
    conta_destino_id NUMBER NOT NULL,
    data_transacao TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT transacoes_pk PRIMARY KEY (id),
    CONSTRAINT fk_transacoes_conta_origem FOREIGN KEY (conta_origem_id) REFERENCES contas(id),
    CONSTRAINT fk_transacoes_conta_destino FOREIGN KEY (conta_destino_id) REFERENCES contas(id)
);

Dados de exemplo

INSERT INTO clientes (nome, email, cpf) VALUES ('Maria Silva', 'maria@email.com', '11111111111');
INSERT INTO clientes (nome, email, cpf) VALUES ('João Souza', 'joao@email.com', '22222222222');

INSERT INTO contas (cliente_id, numero, saldo) VALUES (1, '0001-1', 1000);
INSERT INTO contas (cliente_id, numero, saldo) VALUES (2, '0002-2', 1000);

COMMIT;

Com as contas criadas, basta chamar a procedure e fazer o commit. Exemplo simples usando DBeaver:

-- transferir(valor, id da conta de origem, id da conta de destino)
CALL transferir(100, 2, 1);
COMMIT;

SELECT id, saldo FROM contas;

Lembra que a procedure não executa COMMIT? É por isso que ele aparece aqui: quem chama decide quando confirmar. Com isso, a procedure está funcionando, mas nosso trabalho não para por aqui. A seguir vamos ver como seria fazer essa chamada via back-end.

Conectando com o Back-End

Neste exemplo vamos utilizar o Spring Boot com Spring JDBC + JdbcTemplate para executar a procedure, mas a vantagem de utilizar procedures é que qualquer back-end pode chamá-las.

O JDBC é uma API Java que permite a interação com bancos relacionais e o JdbcTemplate é uma classe auxiliar do Spring que trabalha sobre o JDBC facilitando nossa vida.

Em uma classe service injetamos o JdbcTemplate, e usamos o método update passando a chamada da procedure:

  private final JdbcTemplate jdbcTemplate;

  public TransacaoService(JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
  }

Agora o método:

@Transactional
public String transferir(TransferenciaDto data) {
    String query = "{CALL transferir(?,?,?)}";

    jdbcTemplate.update(
        query,
        data.valor(),
        data.contaOrigemId(),
        data.contaDestinoId()
    );

    return "Transferência realizada com sucesso!";
}

Vamos destrinchar o que criamos aqui:

• Usamos a anotação @Transactional, dado que nossa procedure não faz commit nem rollback. Com ela, o Spring confirma a transação ou cancela se uma exceção for lançada (como os erros da procedure);
• A query é a sintaxe da chamada da procedure passando interrogações no lugar de cada parâmetro;
• O método update executa a chamada no banco. Os valores passados em seguida substituem os ? na ordem em que aparecem.

Se tudo der certo, o método retorna a mensagem de sucesso que definimos. Se a procedure lançar um erro, o método é interrompido e um handler devolve ao cliente a mensagem de erro que veio da procedure.

Veja um exemplo simples, usando o @ExceptionHandler do Spring para capturar o erro e uma regex para extrair a mensagem de cada situação:

@ExceptionHandler(UncategorizedSQLException.class)
public ResponseEntity<String> handleProcedureError(UncategorizedSQLException ex) {
    String fullMessage = ex.getCause().getMessage();

    Pattern pattern = Pattern.compile("ORA-\\d+: ([^\n]+)");
    Matcher matcher = pattern.matcher(fullMessage);

    String cleanMessage = matcher.find() ? matcher.group(1).trim() : "Erro ao processar a transferência";

    return ResponseEntity.badRequest().body(cleanMessage);
}

Aqui capturamos a UncategorizedSQLException, que é o que o Spring lança para erros do banco que ele não reconhece, como os definidos com RAISE_APPLICATION_ERROR. É uma simplificação proposital para pegar as mensagens da procedure.

Dessa forma, saberemos se a transferência foi concluída com sucesso, ou se caímos em algum dos erros de validação mapeados no capítulo 3.

Conclusão

Assim, concluímos este artigo entendendo um pouco mais sobre como as procedures são um recurso poderoso no desenvolvimento de sistemas. Se usadas corretamente, podem melhorar muito uma aplicação, mas requerem cautela e uma análise cuidadosa de arquitetura. Vimos aqui apenas uma introdução ao tema, já que este é um assunto complexo em que vale a pena mergulhar um pouco mais. No próximo artigo desta série, veremos as functions, outro recurso muito interessante do banco de dados.

Se quiser ver esse projeto completo, clique aqui: repositório