Como usar BULK COLLECT e FORALL para manipular grandes volumes de dados de forma eficiente

Introdução

Duas das funções que eu particularmente trabalho com grande frequência no cotidiano como desenvolvedor é o BULK COLLECT e FORALL, dado a grande quantidade de dados que os clientes trabalham e que o produto precisa ser capaz de atuar tendo a perfomance como ponto primordial. E é exatamente aí que entra a necessidade de uso destas duas funcionalidades que a linguangem PL/SQL nos fornece, nos permitindo processar grandes quantidade de dados de maneira mais eficiente, reduzindo a sobrecarga do contexto entre SQL e PL/SQL.

O que é BULK COLLECT

1. Acesse Recursos em Ordem Consistente

O BULK COLLECT permite recuperar múltiplas linhas de uma consulta SQL em uma única operação, armazenando os dados em uma coleção PL/SQL (como uma tabela aninhada ou um array associativo). Isso reduz a quantidade de trocas de contexto entre SQL e PL/SQL, melhorando o desempenho.

DECLARE
    TYPE t_clientes IS TABLE OF clientes%ROWTYPE;
    l_clientes t_clientes;
BEGIN
    SELECT * BULK COLLECT INTO l_clientes FROM clientes;

    FOR i IN 1..l_clientes.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Cliente: ' || l_clientes(i).cliente_name);
    END LOOP;
END;

Neste exemplo, a consulta carrega todos os registros de clientes para a coleção l_clientes de uma só vez, eliminando a necessidade de um cursor linha a linha.

O que é FORALL

O FORALL é usado para executar operações DML (INSERT, UPDATE, DELETE) em massa dentro de um loop, reduzindo a sobrecarga da troca de contexto entre PL/SQL e SQL.

DECLARE
    TYPE t_ids IS TABLE OF produtos.prod_id%TYPE;
    l_ids t_ids := t_ids(1001, 1002, 1003, 1004, 1005);
BEGIN
    FORALL i IN 1..l_ids.COUNT
        UPDATE produtos SET preco = preco * 1.1 WHERE prod_id = l_ids(i);
    COMMIT;
END;

Em vez de executar uma atualização para cada funcionário individualmente, o FORALL permite que todas as atualizações sejam processadas em um único bloco, otimizando o desempenho.

Combinando BULK COLLECT e FORALL

Podemos utilizar BULK COLLECT para recuperar dados em massa e FORALL para processá-los eficientemente. Exemplo:

DECLARE
    TYPE t_preco IS TABLE OF produtos.preco%TYPE;
    TYPE t_ids IS TABLE OF produtos.prod_id%TYPE;
    
    l_preco t_preco;
    l_ids t_ids;
BEGIN
    SELECT prod_id, preco BULK COLLECT INTO l_ids, l_preco FROM produtos;
    
    FORALL i IN 1..l_ids.COUNT
        UPDATE produtos SET preco = l_preco(i) * 1.1 WHERE prod_id = l_ids(i);
    
    COMMIT;
END;

Este código primeiro carrega os IDs e salários dos funcionários usando BULK COLLECT e depois aplica um aumento salarial usando FORALL para otimizar o processamento.

Dicas para Otimização

Use LIMIT com BULK COLLECT: Se o volume de dados for muito grande, utilize LIMIT para evitar estouro de memória.

LOOP
    FETCH cursor_produtos BULK COLLECT INTO l_produtos LIMIT 1000;
    EXIT WHEN l_produtos.COUNT = 0;
    -- Processamento
END LOOP;

Combine EXCEPTION HANDLING com FORALL: Utilize a cláusula SAVE EXCEPTIONS para capturar erros sem interromper o processamento em massa.

BEGIN
    FORALL i IN 1..l_ids.COUNT SAVE EXCEPTIONS
        UPDATE produtos SET preco = preco * 1.1 WHERE prod_id = l_ids(i);
EXCEPTION
    WHEN OTHERS THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Erro na linha ' || i || ': ' || SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
END;

Evite consultar e atualizar ao mesmo tempo: Prefira armazenar os dados primeiro em coleções para minimizar acessos ao banco.

Conclusão

Com os exemplos acima e a explicação sobre o que é cada um e os beneficios como: melhoria de eficiência no processamento de grandes volumes de dados, redução de sobrecarga na troca entre SQL e PL/SQL, que são obtidos com o uso do BULK COLLECT e FORALL, proporcionando assim uma melhoria no desempenho e escalabilidade; com certeza não restará dúvidas sobre a aplicação nas mais diversas tarefas e acrescentar melhorias aos mais diversos produtos em atuação por cada profissional de banco de dados.

Referências

• Bulk data processing with BULK COLLECT and FORALL in PL/SQL