oracle forall

Oracle FORALL is a SQL statement that allows you to perform a single DML operation, such as an insert, update, or delete, across multiple rows of a table or view at once. It is similar to a FOR loop in PL/SQL, but operates on sets of data instead of individual rows.

The basic syntax of an Oracle FORALL statement is as follows:

FORALL index IN lower_bound..upper_bound
   DML_statement;

Where `index` is a loop variable, `lower_bound` and `upper_bound` define the range of values for the loop variable, and `DML_statement` is the single DML operation that will be performed on all rows within the specified range.

For example, the following FORALL statement would update the salaries of all employees whose job title is 'SALES REP':

DECLARE
   TYPE emp_table IS TABLE OF employees%ROWTYPE;
   emp_tab emp_table;
BEGIN
   SELECT * BULK COLLECT INTO emp_tab FROM employees WHERE job_title = 'SALES REP';
   FORALL i IN emp_tab.FIRST..emp_tab.LAST
      UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_tab(i).employee_id;
END;

This statement first selects all employees with the 'SALES REP' job title and stores them in a collection named `emp_tab`. It then uses a FORALL loop to update the salary of each employee in the collection by multiplying it by 1.1.