BULK COLLECT and FORALL in PLSQL

#sql #plsql #fullstack #devops

BULK COLLECT and FORALL

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:

Fetch employee names from a specific department using BULK COLLECT.
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.

DEV Community

BULK COLLECT and FORALL in PLSQL