You mentioned bulk operations. Tell me more about the BULK COLLECT and FORALL combination.

BULK COLLECT and FORALL are PL/SQL constructs used for efficient processing of large volumes of data, minimizing the number of switches between SQL and PL/SQL.

• BULK COLLECT allows loading multiple rows from an SQL query directly into a PL/SQL collection (e.g., an array), instead of iterating through them one by one.

• FORALL is used for bulk execution of DML operations (INSERT, UPDATE, DELETE) using collections, which significantly speeds up execution compared to looping through individual operations.

Example of using the combination:

DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  TYPE t_salaries IS TABLE OF employees.salary%TYPE;

  l_ids t_ids;
  l_salaries t_salaries;
BEGIN
  -- Load data with BULK COLLECT
  SELECT employee_id, salary
  BULK COLLECT INTO l_ids, l_salaries
  FROM employees
  WHERE department_id = 10;

  -- Update salaries using FORALL
  FORALL i IN l_ids.FIRST .. l_ids.LAST
    UPDATE employees
    SET salary = l_salaries(i) * 1.1
    WHERE employee_id = l_ids(i);

  COMMIT;
END;

This approach allows:

• Reducing the number of switches between SQL and PL/SQL.
• Significantly improving performance when processing large data sets.

It is important to consider the size of collections and use LIMIT parameters to control the volume of data per operation to avoid memory overflow.