PL-SQL na Prática

Conceito

Em muitos sistemas Oracle, a aplicação está no banco. Não como resquício, mas por projeto: packages PL/SQL com regra de negócio, triggers de auditoria, jobs agendados. Quem chega de fora precisa entender esse mundo antes de propor mudanças — e precisa saber a única coisa que realmente determina performance em PL/SQL.

A troca de contexto SQL ↔ PL/SQL

PL/SQL e SQL são duas máquinas virtuais diferentes dentro do mesmo processo. Cada travessia custa. O laço "linha a linha" é o antipadrão-mãe do Oracle:

-- ❌ ROW-BY-ROW ("slow-by-slow"): 1 milhão de travessias de contexto
DECLARE
BEGIN
  FOR r IN (SELECT id, valor FROM pedidos WHERE status = 'novo') LOOP
    UPDATE pedidos SET taxa = r.valor * 0.05 WHERE id = r.id;   -- uma por linha!
    COMMIT;                                                      -- pior ainda
  END LOOP;
END;
/

-- ✅ BULK: uma travessia por lote de 1000
DECLARE
  TYPE t_ids IS TABLE OF pedidos.id%TYPE;
  TYPE t_vals IS TABLE OF pedidos.valor%TYPE;
  v_ids t_ids; v_vals t_vals;
  CURSOR c IS SELECT id, valor FROM pedidos WHERE status = 'novo';
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids, v_vals LIMIT 1000;   -- LIMIT é essencial (memória!)
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1 .. v_ids.COUNT
      UPDATE pedidos SET taxa = v_vals(i) * 0.05 WHERE id = v_ids(i);

    COMMIT;   -- por lote, não por linha
  END LOOP;
  CLOSE c;
END;
/

Ganho típico: 10× a 50×. E há uma opção ainda melhor, que muita gente esquece:

-- ✅✅ SQL puro: zero travessia de contexto. Sempre a primeira opção.
UPDATE pedidos SET taxa = valor * 0.05 WHERE status = 'novo';

A regra: se dá para fazer em SQL puro, faça em SQL puro. Se precisa de PL/SQL, use BULK COLLECT + FORALL. Laço linha a linha é o último recurso, não o primeiro.

E COMMIT dentro do laço não é só lento (um log file sync por commit): é o gerador número um de ORA-01555 no próprio cursor que está sendo lido. Ver 3. Redo, Undo e Consistência de Leitura.

Packages: a unidade de organização

CREATE OR REPLACE PACKAGE pkg_pedido AS          -- especificação (o contrato público)
  e_saldo_insuficiente EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_saldo_insuficiente, -20001);

  FUNCTION calcular_total(p_pedido_id NUMBER) RETURN NUMBER;
  PROCEDURE confirmar(p_pedido_id NUMBER);
END pkg_pedido;
/

CREATE OR REPLACE PACKAGE BODY pkg_pedido AS     -- corpo (privado)
  g_taxa CONSTANT NUMBER := 0.05;                -- estado de PACOTE: vive na SESSÃO

  FUNCTION calcular_total(p_pedido_id NUMBER) RETURN NUMBER IS
    v_total NUMBER;
  BEGIN
    SELECT SUM(qtd * preco) INTO v_total FROM itens WHERE pedido_id = p_pedido_id;
    RETURN NVL(v_total, 0) * (1 + g_taxa);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 0;
  END;

  PROCEDURE confirmar(p_pedido_id NUMBER) IS ... END;
END pkg_pedido;
/

Detalhes que importam:

• Especificação separada do corpo: mudar o corpo não invalida os objetos dependentes. É o mecanismo que permite evoluir sem recompilar meio banco.
• Estado de pacote é por sessão e sobrevive entre chamadas. Com pool de conexões, isso significa que a variável global pode carregar valor de outra requisição — fonte de bugs sutis.
• Exceções nomeadas com PRAGMA EXCEPTION_INIT mapeiam código de erro para nome, e é assim que o app distingue erro de negócio de erro de banco.

Interoperar com a aplicação

// Chamando uma procedure do Go (driver godror), com bind de saída
var total float64
_, err := db.ExecContext(ctx,
	`BEGIN :1 := pkg_pedido.calcular_total(:2); END;`,
	sql.Out{Dest: &total}, pedidoID)

// RETURNING INTO no INSERT: o "RETURNING" do Oracle
var novoID int64
_, err = db.ExecContext(ctx,
	`INSERT INTO pedidos (cliente_id, total) VALUES (:1, :2) RETURNING id INTO :3`,
	clienteID, total, sql.Out{Dest: &novoID})

E o mapeamento de erro de negócio, que é o padrão limpo:

-- no PL/SQL
IF v_saldo < p_valor THEN
  RAISE_APPLICATION_ERROR(-20001, 'Saldo insuficiente para o pedido ' || p_pedido_id);
END IF;

// no Go: -20001..-20999 é a faixa reservada para erro de aplicação
var oraErr *godror.OraErr
if errors.As(err, &oraErr) && oraErr.Code() == 20001 {
	return ErrSaldoInsuficiente   // erro de DOMÍNIO, não de infraestrutura
}

Isso conecta com 3. Erros, Interfaces e o Estilo Go: o erro nasce como regra de negócio no banco e chega ao handler como erro de domínio tipado.

O SQL Oracle que difere: revisão rápida

SELECT SYSDATE FROM dual;                              -- toda query precisa de FROM
SELECT NVL(nome, 'sem nome') FROM clientes;            -- COALESCE também funciona
SELECT * FROM pedidos ORDER BY id
  OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;             -- 12c+; antes era ROWNUM
SELECT LISTAGG(nome, ', ') WITHIN GROUP (ORDER BY nome) FROM clientes;
SELECT TRUNC(criado_em) FROM pedidos;                  -- DATE inclui hora!
MERGE INTO destino d USING origem o ON (d.id = o.id)
  WHEN MATCHED THEN UPDATE SET d.v = o.v
  WHEN NOT MATCHED THEN INSERT (id, v) VALUES (o.id, o.v);

E as três armadilhas que quebram código portado, detalhadas em 2. Dialetos SQL na Prática:

1. '' é NULL para VARCHAR2 — não existe string vazia.
2. Concatenação ignora NULL: 'a' || NULL || 'b' = 'ab' (no Postgres seria NULL).
3. Identificador sem aspas vira MAIÚSCULA: create table Pedidos cria PEDIDOS, e SELECT * FROM "Pedidos" falha.

Trade-offs

Lógica no banco é rápida e é lock-in. Zero latência de rede entre lógica e dado, e transação natural. Em troca: não versiona bem no Git (embora dê), não testa com o ferramental da aplicação, não escala horizontalmente com os pods, e amarra você ao Oracle — 200 mil linhas de PL/SQL não migram, são reescritas.

Trigger é conveniente e invisível. Uma trigger de auditoria resolve o problema em três linhas e faz o INSERT ter efeitos que ninguém lendo o código da aplicação enxerga. Debug de sistema trigger-heavy é arqueologia.

Estado de pacote + pool de conexão = bug intermitente. Variável global de pacote persiste na sessão; a sessão volta ao pool; a próxima requisição herda o estado. Se você usa PL/SQL com pool (e usa), trate estado de pacote como se fosse variável global em código concorrente — ou seja, evite.

O trade-off central: PL/SQL entrega o menor tempo possível entre a regra e o dado, e paga com portabilidade e integração ao ciclo moderno de desenvolvimento. Num sistema que já é Oracle e vai continuar sendo, usá-lo bem (bulk, packages, exceções nomeadas) é muito melhor que reimplementar mal na aplicação.

Exemplo prático

Encontrar o antipadrão row-by-row em produção

-- SQL executado MUITAS vezes com POUCAS linhas por execução = laço PL/SQL
SELECT sql_id, executions,
       ROUND(rows_processed / GREATEST(executions,1), 2) AS linhas_por_exec,
       ROUND(elapsed_time/1e6) AS total_s,
       LEFT(sql_text, 70) AS sql
  FROM v$sql
 WHERE executions > 100000 AND rows_processed / GREATEST(executions,1) < 2
 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

Um UPDATE com 3 milhões de execuções e 1 linha cada é um laço PL/SQL (ou um 7. Problema N+1 vindo da aplicação). Os dois se corrigem do mesmo jeito: operar em conjunto, não em linha.

Instrumentar PL/SQL para o diagnóstico ser possível

-- marque a sessão: aparece em v$session e no ASH, e salva horas de investigação
DBMS_APPLICATION_INFO.SET_MODULE('faturamento', 'gerar_nota');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pedido=' || p_pedido_id);

-- e para achar o gargalo dentro de um package grande
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;   -- necessário para o profiler
-- DBMS_HPROF.START_PROFILING(...) / STOP_PROFILING

Como isso falha em produção

Aprofundamento aplicado

Lote não define sozinho a unidade de negócio

FORALL reduz travessias entre PL/SQL e SQL ao aplicar operações em lote. Isso não decide se falhas parciais devem invalidar todo o lote. Com tratamento que coleta erros, a aplicação precisa saber quais itens falharam e qual parte poderá ser confirmada. Referência: bulk SQL e bulk binding.

Cenário: importar mil itens independentes pode permitir registrar 998 e reportar 2 inválidos. Transferir saldo entre duas contas exige que débito e crédito formem uma unidade; confirmar cada instrução separadamente viola a regra mesmo que seja rápido.

Definir quem pode confirmar

Uma procedure reutilizável que executa COMMIT internamente pode impedir o chamador de compor várias operações em uma transação maior. Declare no contrato se a rotina controla a transação ou participa da transação do chamador.

Exercício: desenhe uma importação retomável com identificador por item e relatório de erros. Resultado esperado: repetir o arquivo não duplica os itens já aceitos; o tamanho de lote limita memória e a política de confirmação preserva a regra do domínio.

Relacionado

• 1. Oracle Database
• 4. Otimizador, Planos e Bind Variables
• 3. Redo, Undo e Consistência de Leitura
• 2. Dialetos SQL na Prática
• 3. Erros, Interfaces e o Estilo Go
• 7. Problema N+1

---

Parte de MOC Oracle · PL/SQL Language Reference (docs)