BULK COLLECT with FORALL

The combination of BULK COLLECT and FORALL in Oracle PL/SQL allows for efficient data manipulation by minimizing context switches between SQL and PL/SQL engines. You typically use BULK COLLECT to fetch large datasets into collections and FORALL to perform bulk DML operations (like INSERT, UPDATE, or DELETE) using those collections.

Key Points:

BULK COLLECT allows fetching multiple rows into a collection with a single context switch.

FORALL is used to apply DML operations in bulk to improve performance by minimizing context switches.

In this example, we will use BULK COLLECT to retrieve data from the database and FORALL to update the data in bulk. The use case will be to:

Retrieve employees with a salary less than a certain threshold.
Update their salaries in bulk by adding a raise.

Example: BULK COLLECT with FORALL

Step 1: Create a Sample employees Table


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

INSERT INTO employees VALUES (1, 'John', 'Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO employees VALUES (3, 'Mike', 'Johnson', 55000);
INSERT INTO employees VALUES (4, 'Emily', 'Davis', 70000);
INSERT INTO employees VALUES (5, 'Chris', 'Brown', 45000);

COMMIT;

Step 2: PL/SQL Block with BULK COLLECT and FORALL

DECLARE
    -- Define the collection types
    TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
    TYPE salary_tab IS TABLE OF employees.salary%TYPE;

    l_emp_ids emp_id_tab;       -- Collection to hold employee IDs
    l_salaries salary_tab;      -- Collection to hold employee salaries

BEGIN
    -- Step 1: Use BULK COLLECT to fetch data from the employees table
    SELECT employee_id, salary
    BULK COLLECT INTO l_emp_ids, l_salaries
    FROM employees
    WHERE salary < 60000;  -- Fetch only employees with salary less than 60,000

    -- Step 2: Use FORALL to bulk update the fetched employees' salaries
    FORALL i IN l_emp_ids.FIRST .. l_emp_ids.LAST
        UPDATE employees
        SET salary = salary + 5000
        WHERE employee_id = l_emp_ids(i);

    -- Display the updated employee data
    FOR i IN l_emp_ids.FIRST .. l_emp_ids.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || l_emp_ids(i) || 
                             ', New Salary: ' || (l_salaries(i) + 5000));
    END LOOP;

    COMMIT;
END;
/

Explanation:

1. Collection Declaration:

emp_id_tab is a collection type to store employee IDs.

salary_tab is a collection type to store employee salaries.

2. BULK COLLECT:

We use BULK COLLECT INTO to retrieve employees with a salary less than 60,000 and store their IDs in l_emp_ids and their salaries in l_salaries.

3. FORALL:

FORALL is used to perform a bulk update on the employee salaries. It loops over the l_emp_ids collection, updating the salary for each employee by adding 5,000.

4. Output:

After the FORALL statement, a loop goes through the collection and prints the new salary for each employee.

5. Commit:

The COMMIT statement is used to make the changes permanent in the database.

Output:

Assuming the data in the employees table, this block will update the salaries for the employees whose salaries are less than 60,000 and display the updated salaries.

Employee ID: 1, New Salary: 55000
Employee ID: 3, New Salary: 60000
Employee ID: 5, New Salary: 50000

Why Use BULK COLLECT and FORALL?

Performance: BULK COLLECT fetches multiple rows in a single operation, reducing the number of context switches between the SQL and PL/SQL engines.

Efficiency: FORALL allows you to execute DML (Data Manipulation Language) statements like UPDATE, INSERT, and DELETE in bulk. This reduces the overhead associated with individual DML statements executed in a loop.

This combination is particularly useful when you need to update large datasets efficiently.