G-3210
🆓Warning
Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.
Reason
Context switches between PL/SQL and SQL are extremely costly. BULK Operations reduce the number of switches by passing an array to the SQL engine, which is used to execute the given statements repeatedly.
(Depending on the PLSQL_OPTIMIZE_LEVEL parameter a conversion to BULK COLLECT will be done by the PL/SQL compiler automatically.)
Example
Non-Compliant Example
declare
t_employee_ids employee_api.t_employee_ids_type;
co_increase constant employees.salary%type := 0.1;
co_department_id constant departments.department_id%type := 10;
begin
t_employee_ids := employee_api.employee_ids_by_department(
id_in => co_department_id
);
<<process_employees>>
for i in 1..t_employee_ids.count()
loop
update employees
set salary = salary + (salary * co_increase)
where employee_id = t_employee_ids(i);
end loop process_employees;
end;
/Issues
| Line | Column | Message |
|---|---|---|
| 12 | 7 | Row-by-row update statement. |
Compliant Solution - ★★★★★
declare
t_employee_ids employee_api.t_employee_ids_type;
co_increase constant employees.salary%type := 0.1;
co_department_id constant departments.department_id%type := 10;
begin
t_employee_ids := employee_api.employee_ids_by_department(
id_in => co_department_id
);
<<process_employees>>
forall i in 1..t_employee_ids.count()
update employees
set salary = salary + (salary * co_increase)
where employee_id = t_employee_ids(i);
end;
/References
- same as Trivadis G-3210
- similar to plsql:UseForallStatementCheck
The scope of plsql:UseForallStatementCheck is limited to FORALL statements.