13-3. Speeding Up Read/Write Loops
Problem
You have identified a loop that reads and writes large batches of data. You want to speed it up.
Solution
Use a BULK COLLECT statement to fetch the target data records, and then use a FORALL loop to update the local database. For example, suppose you want to speed up the sync_hr_data procedure demonstrated in Chapter 11:
CREATE OR REPLACE PROCEDURE sync_hr_data AS
CURSOR driver IS
SELECT *
FROM employees@hr_data;
TYPE recs_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER;
recs recs_type;
BEGIN
OPEN driver;
FETCH driver BULK COLLECT INTO recs;
CLOSE driver;
FORALL i IN 1..recs.COUNT
UPDATE employees
SET first_name = recs(i).first_name WHERE employee_id ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
