I'm new in PL/SQL. I have a procedure like:
create or replace procedure insert_charge is
v_count number;
begin
for i in (select t.name, t.hire_date, t.salary
from emp t
where t.create_date >= (sysdate - 30)
and t.salary = 0) loop
insert into charge
(name, hire_date, salary)
values
(i.name, hire_date, salary);
commit;
update emp l
set l.status = 1
where l.name = i.name
and l.status = 0
and l.hire_date = i.hire_date;
commit;
end loop;
exception
when others then
rollback;
end insert_charge;
How can use FORALL statement instead of this?
There are a few additional tasks for FORALL; namely defining a collection to define the bulk area and a variable of that collection type to contain the actual data. As a safety value you should place a LIMIT on the number of fetched at once. Bulk Collect/ Forall is a trade off of speed vs memory. And at a certain point (depending on your configuration) has diminishing returns. Besides the memory you use for it is unavailable to other processes in the database. Plat well with your fellow queries. Then as @Littlefoot points out DO NOT SQUASH EXCEPTIONS log them and re-raise. Finally, a note about commits. **Do not commit after each DML statement, You may want spend some time to investigate [transactions][1]. With this in mind your procedure becomes something like:
create or replace procedure insert_charge is
cursor c_emp_cur is
select t.name, t.hire_date, t.salary
from emp t
where t.create_date >= (sysdate - 30)
and t.salary = 0;
type c_emp_array_t is table of c_emp%rowtype ; -- define collection for rows selected
k_emp_rows_max constant integer := 500; -- defines the maximum rows per fetch
l_emp_list c_emp_array_t; -- define variable of rows collection
begin
open c_emp_cur;
loop
fetch c_emp_cur -- fetch up to LIMIT rows from cursor
bulk collect
into l_emp_collect
limit k_emp_rows_max;
forall i in 1 .. l_emp_collect.count -- run insert for ALL rows in the collection
insert into charge(name, hire_date, salary)
values( l_emp_collect(i).name
, l_emp_collect(i).hire_date
, l_emp_collect(i).salary);
forall i in 1 .. l_emp_collect.count -- run update for ALL rows in the collection
update emp l
set l.status = 1
where l.name = l_emp_collect(i).name
and l.status = 0
and l.hire_date = l_emp_collect(i).hire_date;
exit when c_emp_cur%notfound; -- no more rows so exit
end loop;
close c_emp_cur;
commit; -- JUST 1 COMMIT;
exception
when others then
generate_exception_log ('insert_charge', sysdate, sql_errm ); --ASSUMED Anonomous Transaction procedure for exception log table.
raise;
end insert_charge;
DISCLAIMER: Not tested. [1]: https://www.techopedia.com/definition/16455/transaction-databases