📤 Compartilhe este artigo com o link curto:
One of the most common mistakes made by developers migrating from other languages to the Oracle ecosystem is the excessive use of row-by-row processing combined with individual SQL commands. In Oracle, each time an SQL statement is executed within a PL/SQL procedural block, a context switch occurs between the PL/SQL engine and the SQL engine.
When processing tables with millions of records, this constant switching creates massive CPU overhead, causing simple routines to take hours to run. To solve this architectural problem, Oracle provides the batch collection extensions: BULK COLLECT and FORALL.
The BULK COLLECT command instructs the SQL engine to load multiple records from a query directly into a PL/SQL collection (such as a TABLE OF or VARRAY) in a single fetch operation. This drastically reduces the number of round trips between the 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; -- In-memory processing of collected data FOR i IN 1..l_clientes.COUNT LOOP -- Complex business logic in memory l_clientes(i).limite := l_clientes(i).limite * 1.15; END LOOP; -- Data is now ready to be persisted in batch END;
While BULK COLLECT optimizes reading, FORALL optimizes writing and modification (INSERT, UPDATE, DELETE). It is important to note that FORALL is not a traditional loop (it does not accept complex flow control statements inside), but rather a directive to send an array of data at once to the SQL engine.
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. Collect pending data SELECT client_id, credit_limit * 1.15 BULK COLLECT INTO l_ids, l_limites FROM clients WHERE status = 'ACTIVE'; -- 2. Batch update using 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;
One of the biggest fears when performing batch operations (such as updating 100,000 records at once) is that if record number 50,000 violates a constraint or business rule, the entire transaction fails and rolls back everything.
To solve this, PL/SQL offers the SAVE EXCEPTIONS clause in conjunction with the SQL%BULK_EXCEPTIONS attribute. Here's how to handle partial failures without losing the entire batch:
DECLARE -- Collection declarations and control variables... 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('Error at index: ' || SQL%BULK_EXCEPTIONS(i).error_index || ' | Error Code: ' || SQL%BULK_EXCEPTIONS(i).error_code); END LOOP; -- Optionally commit what passed and log errors COMMIT; END; END;
Although extremely powerful, collections loaded via BULK COLLECT consume PGA memory on the database server. If your query returns tens of millions of rows at once, you may exhaust session memory. For extreme scenarios, always use the LIMIT clause:
OPEN c_cursor; LOOP FETCH c_cursor BULK COLLECT INTO l_collection LIMIT 5000; EXIT WHEN l_collection.COUNT = 0; -- Process in blocks of 5,000 records at a time END LOOP; CLOSE c_cursor;
Mastering these techniques ensures highly performant, scalable, and mission-critical enterprise applications.