Home

FORALL should be used

Description

    The FORALL statement in PL/SQL is used to execute a set of DML statements (INSERT, UPDATE, DELETE) on a collection of data. It is much more efficient than using a loop to execute the same set of DML statements on each element of the collection. The FORALL statement is used to improve the performance of a PL/SQL block by reducing the number of context switches between the PL/SQL engine and the SQL engine.

    When using the FORALL statement, it is important to remember that the same DML statement is executed on all elements of the collection. This means that the same set of values is used for each element of the collection, and that the same set of conditions is applied to each element. This can be a great advantage when dealing with large collections of data, as it reduces the amount of code required to process the data.

    Therefore, the rule for using the FORALL statement is to use it whenever possible to improve the performance of a PL/SQL block. It is especially useful when dealing with large collections of data, as it reduces the amount of code required to process the data.

Key Benefits

  • Eliminates manual data entry: It eliminates the need for manual data entry, allowing for faster and more accurate data entry.
  • Reduces errors: It reduces the chance of errors due to manual data entry, ensuring accuracy and reliability.
  • Improves efficiency: It increases efficiency by automating data entry processes, allowing for faster and more accurate data entry.
  • Increases security: It increases security by eliminating the need for manual data entry, reducing the risk of data loss or theft.

 

Non-compliant Code Example

BEGIN
	managerId := Employees.GetManagerByEmployee(:old.EMP_ID);
	for c in c1 loop        --Non compliant code (FORALL should be used)
		UPDATE PRODUCT
			SET MANAGER_ID = managerId
			WHERE c.ID = Id;
    
    end loop;
END;

Compliant Code Example

BEGIN
    
    managerId := Employees.GetManagerByEmployee(:old.EMP_ID);
    forall i IN 1..c1       --Compliant code (FORALL is used)
      UPDATE PRODUCT
        SET MANAGER_ID = managerId
        WHERE iterator(i).ID = Id;
      
END;
Visual Expert 2024
 VEPLSQLRULE73