The
progression is also supported for implicit query in a DML statement via the returning
keyword…
declare
type employee_ids_t is table of employees.employee_id%type
index by binary_integer;
employee_ids employee_ids_t;
type salaries_t is table of employees.salary%type
index by binary_integer;
salaries salaries_t;
begin
employee_ids(1) := 151;
employee_ids(2) := 162;
employee_ids(3) := 173;
for j in employee_ids.first..employee_ids.last
loop
update employees set salary = salary*1.1
where employee_id = employee_ids(j)
returning salary into salaries(j);
end loop;
end;
…then…forall j in employee_ids.first..employee_ids.last
update employees set salary = salary*1.1
where employee_id = employee_ids(j)
returning salary bulk collect into salaries
/* this is not a typo: employee_ids is subscipted but salaries isn't */;…then…
forall j in employee_ids.first..employee_ids.last
execute immediate 'update employees set salary = salary*1.1'
|| ' where employee_id = :the_id'
|| ' returning salary into :the_salary'
using employee_ids(j)
returning bulk collect into salaries /* new at 9i */;