BULK COLLECT | FORALL | SELECT INTO | FETCH & LIMIT |

BULK COLLECT in PL/SQL is used to fetch multiple rows from a query result into collections (like VARRAY, PL/SQL Table, or Nested Table) in one operation, rather than fetching row-by-row. This reduces context switches between the SQL and PL/SQL engines and significantly improves performance when dealing with large data sets.

You can use BULK COLLECT with both:

SELECT INTO (to fetch all rows at once)

FETCH (to fetch rows in chunks or batches)

1. BULK COLLECT with SELECT INTO

This method retrieves all rows from the query result into a PL/SQL collection in a single operation.

Syntax:

SELECT columns
BULK COLLECT INTO collection_variable
FROM table_name
WHERE condition;

Example:

DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE; -- Create a collection type
emp_table emp_table_type; -- Declare a collection variable
BEGIN
-- Fetch all rows from employees table into emp_table collection
SELECT *
BULK COLLECT INTO emp_table
FROM employees
WHERE department_id = 10;

-- Display fetched rows
FOR i IN emp_table.FIRST .. emp_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(emp_table(i).employee_id || ' ' || emp_table(i).employee_name);
END LOOP;
END;

In this example, the entire result set from the query is bulk collected into emp_table.

The FOR loop then processes the collected data from the collection.

1. BULK COLLECT with FETCH (Explicit Cursor)

In this approach, you fetch rows in chunks using a cursor with the FETCH statement and LIMIT clause. This method is useful when you want to process large datasets in smaller batches to avoid memory issues.

Syntax:

OPEN cursor_name;

LOOP
FETCH cursor_name
BULK COLLECT INTO collection_variable
LIMIT batch_size;

EXIT WHEN collection_variable.COUNT = 0;

-- Process the data here (e.g., using a FOR loop)
END LOOP;

CLOSE cursor_name;

Example:

DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE; -- Create a collection type
emp_table emp_table_type; -- Declare a collection variable
CURSOR emp_cursor IS -- Declare a cursor
SELECT * FROM employees WHERE department_id = 10;
BEGIN
OPEN emp_cursor;

LOOP
-- Fetch rows in batches of 100
FETCH emp_cursor
BULK COLLECT INTO emp_table
LIMIT 100;

  EXIT WHEN emp_table.COUNT = 0;  -- Exit loop when no more rows

  -- Process fetched data
  FOR i IN emp_table.FIRST .. emp_table.LAST LOOP
     DBMS_OUTPUT.PUT_LINE(emp_table(i).employee_id || ' ' || emp_table(i).employee_name);
  END LOOP;

END LOOP;

CLOSE emp_cursor;
END;

Here, rows are fetched in batches of 100 until all rows have been processed.

Using LIMIT allows you to control memory usage by processing the data in manageable chunks.

Key Differences Between SELECT INTO and FETCH with BULK COLLECT:

1. SELECT INTO BULK COLLECT:

Fetches all rows at once into the collection.

Best suited for cases where you know that the result set will not exceed memory limits.

1. FETCH BULK COLLECT:

Fetches rows in batches using the LIMIT clause.

Ideal for large datasets where you want to process data in smaller chunks to avoid memory issues.

Advantages of BULK COLLECT:

Performance: Reduces context switches between the SQL and PL/SQL engines, making operations on large datasets faster.

Memory Control: When combined with FETCH and LIMIT, it allows you to manage memory usage efficiently.

Example Using Both BULK COLLECT and FORALL (For DML):

You can combine BULK COLLECT with the FORALL statement to perform bulk DML operations (INSERT, UPDATE, DELETE) efficiently.

DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
emp_table emp_table_type;
BEGIN
-- Bulk collect data from the table
SELECT *
BULK COLLECT INTO emp_table
FROM employees
WHERE department_id = 10;

-- Perform bulk update using FORALL
FORALL i IN emp_table.FIRST .. emp_table.LAST
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = emp_table(i).employee_id;

DBMS_OUTPUT.PUT_LINE('Salaries updated successfully.');
END;

In this example:

BULK COLLECT is used to fetch data.

FORALL is used to apply a bulk update, improving performance compared to executing the update row-by-row.

Conclusion:

Use BULK COLLECT with SELECT INTO to fetch all rows at once when memory allows.

Use BULK COLLECT with FETCH and LIMIT when dealing with large datasets to control memory usage.