Um dos erros mais comuns cometidos por desenvolvedores que migram de outras linguagens para o ecossistema Oracle é a utilização excessiva de loops linha por linha (row-by-row processing) combinados com comandos SQL individuais. No Oracle, cada vez que um comando SQL é executado dentro de um bloco procedural PL/SQL, ocorre uma troca de contexto (Context Switching) entre o motor PL/SQL e o motor SQL.
Quando estamos processando tabelas com milhões de registros, essa troca constante gera um overhead massivo de processamento na CPU, fazendo com que rotinas simples levem horas para rodar. Para resolver esse problema de arquitetura, a Oracle disponibiliza as extensões de coleta em lote: BULK COLLECT e FORALL.
O comando BULK COLLECT instrui o motor SQL a carregar múltiplos registros de uma consulta diretamente para dentro de uma coleção PL/SQL (como uma TABLE OF ou VARRAY) em uma única operação de fetch. Isso reduz drasticamente o número de viagens de ida e volta entre os engines.
DECLARE TYPE t_cliente IS RECORD ( id clients.client_id%TYPE, nome clients.full_name%TYPE, limite clients.credit_limit%TYPE ); TYPE t_clientes_list IS TABLE OF t_cliente; l_clientes t_clientes_list; BEGIN SELECT client_id, full_name, credit_limit BULK COLLECT INTO l_clientes FROM clients WHERE status = 'ACTIVE' AND credit_limit < 5000; -- Processamento em memória dos dados coletados FOR i IN 1..l_clientes.COUNT LOOP -- Lógica de negócio complexa em memória l_clientes(i).limite := l_clientes(i).limite * 1.15; END LOOP; -- Os dados agora estão prontos para serem persistidos em bloco END;
Enquanto o BULK COLLECT otimiza a leitura, o FORALL otimiza a escrita e alteração (INSERT, UPDATE, DELETE). É importante destacar que o FORALL não é um loop tradicional (ele não aceita comandos complexos de controle de fluxo internos como loops iterativos comuns), mas sim uma diretiva para enviar um array de dados de uma só vez para o motor SQL.
DECLARE TYPE t_id_list IS TABLE OF clients.client_id%TYPE; TYPE t_limit_list IS TABLE OF clients.credit_limit%TYPE; l_ids t_id_list; l_limites t_limit_list; BEGIN -- 1. Coleta dados pendentes SELECT client_id, credit_limit * 1.15 BULK COLLECT INTO l_ids, l_limites FROM clients WHERE status = 'ACTIVE'; -- 2. Atualização em lote utilizando FORALL FORALL i IN 1..l_ids.COUNT UPDATE clients SET credit_limit = l_limites(i), updated_date = SYSDATE WHERE client_id = l_ids(i); COMMIT; END;
Um dos maiores medos ao executar operações em lote (como atualizar 100.000 registros de uma vez) é que, se o registro de número 50.000 violar alguma restrição de chave ou regra de negócio, a transação inteira falhe e dê rollback em tudo.
Para solucionar isso, o PL/SQL oferece a cláusula SAVE EXCEPTIONS em conjunto com o atributo SQL%BULK_EXCEPTIONS. Veja como tratar falhas parciais sem perder o lote inteiro:
DECLARE -- Declaração de coleções e variáveis de controle... ex_dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381); BEGIN FORALL i IN 1..l_ids.COUNT SAVE EXCEPTIONS UPDATE clients SET credit_limit = l_limites(i) WHERE client_id = l_ids(i); COMMIT; EXCEPTION WHEN ex_dml_errors THEN DECLARE l_total_erros NUMBER := SQL%BULK_EXCEPTIONS.COUNT; BEGIN FOR i IN 1..l_total_erros LOOP DBMS_OUTPUT.PUT_LINE('Erro no índice: ' || SQL%BULK_EXCEPTIONS(i).error_index || ' | Código do Erro: ' || SQL%BULK_EXCEPTIONS(i).error_code); END LOOP; -- Opcional: efetivar o que passou e logar os erros COMMIT; END; END;
Apesar de extremamente potentes, coleções carregadas via BULK COLLECT consomem memória PGA (Program Global Area) no servidor de banco de dados. Caso sua query retorne dezenas de milhões de linhas de uma só vez, você pode esgotar a memória da sessão. Para cenários extremos, utilize sempre a cláusula LIMIT:
OPEN c_cursor; LOOP FETCH c_cursor BULK COLLECT INTO l_collection LIMIT 5000; EXIT WHEN l_collection.COUNT = 0; -- Processa em blocos de 5 mil registros por vez END LOOP; CLOSE c_cursor;
Dominar essas técnicas garante aplicações corporativas altamente performáticas, escaláveis e preparadas para ambientes de missão crítica.