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:
- Collection Declaration:
emp_id_tab is a collection type to store employee IDs.
salary_tab is a collection type to store employee salaries.
- 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.
- 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.
- Output:
After the FORALL statement, a loop goes through the collection and prints the new salary for each employee.
- 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.
Top comments (0)