BULK COLLECT and FORALL in PLSQL

BULK COLLECT and FORALL are both PL/SQL features designed to enhance performance by minimizing context switches between the SQL and PL/SQL engines. However, they serve different purposes: BULK COLLECT is used for fetching multiple rows from a query, while FORALL is used for performing DML (Data Manipulation Language) operations (like INSERT, UPDATE, or DELETE) on multiple rows.

Example Scenario:

Assume we have an employees table and we want to:

1. Fetch employee names from a specific department using BULK COLLECT.

2. Update salaries for these employees using FORALL.

Sample Table Structure:

CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);

Sample Data:

INSERT INTO employees VALUES (101, 'John', 'Doe', 50000, 10);
INSERT INTO employees VALUES (102, 'Jane', 'Smith', 60000, 10);
INSERT INTO employees VALUES (103, 'Jim', 'Beam', 70000, 20);
INSERT INTO employees VALUES (104, 'Jake', 'Blues', 55000, 10);

Using BULK COLLECT:

Here’s how to fetch employee names from department 10 using BULK COLLECT:

DECLARE
TYPE employee_table IS TABLE OF employees%ROWTYPE;
v_employees employee_table;
BEGIN
-- Fetching employees from department 10
SELECT * BULK COLLECT INTO v_employees
FROM employees
WHERE department_id = 10;

-- Displaying fetched employee names
FOR i IN v_employees.FIRST .. v_employees.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employees(i).first_name || ' ' || v_employees(i).last_name);
END LOOP;

END;
/

Using FORALL:

Now, let's say we want to give a raise to these employees by updating their salaries. Here’s how to do that with FORALL:

DECLARE
TYPE salary_table IS TABLE OF employees.salary%TYPE;
v_salaries salary_table;
v_ids salary_table;
BEGIN
-- Fetching salaries and IDs for employees in department 10
SELECT salary, employee_id BULK COLLECT INTO v_salaries, v_ids
FROM employees
WHERE department_id = 10;

-- Updating salaries using FORALL
FORALL i IN v_ids.FIRST .. v_ids.LAST
    UPDATE employees
    SET salary = v_salaries(i) * 1.1 -- 10% raise
    WHERE employee_id = v_ids(i);

-- Confirming the updates
DBMS_OUTPUT.PUT_LINE('Salaries updated for department 10 employees.');

END;
/

Key Differences:

Purpose:

BULK COLLECT: Used to fetch multiple rows into a collection.

FORALL: Used to perform DML operations (like updates or inserts) on collections efficiently.

Usage:

BULK COLLECT typically retrieves data, while FORALL updates or inserts data in bulk.

Conclusion:

Both BULK COLLECT and FORALL significantly enhance the performance of PL/SQL code by reducing context switching and allowing for bulk operations. In scenarios where you need to fetch and then process multiple rows of data, combining both features can lead to efficient code execution.