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?

CodePudding user response：

You can't.

The FORALL statement runs one DML statement multiple times

ONE DML statement. You have two (update and insert).

As of code you wrote:

• move COMMIT out of the loop
• remove that when others "handler" as it handles nothing. If error happens, Oracle will silently rollback and report that procedure completed successfully, while it - actually - failed

CodePudding user response：

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