Compare the performance differences between row-at-a-time processing and bulk processing : Fetch « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Fetch

SQL>
SQL> create table myTable1 as select * from dba_objects where 0=1;

Table created.

SQL> create table myTable2 as select * from dba_objects where 0=1;

Table created.

SQL>
SQL> create or replace procedure row_at_a_time_test as begin
  2
  3      for x in ( select * from all_objects )
  4      loop
  5          insert into myTable1 values X;
  6      end loop;
  7
  8  end;
  9  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure bulk_test
  2  as
  3    type array is table of all_objects%rowtype;
  4    l_data array;
  5    cursor c is select * from all_objects;
  6  begin
  7
  8    open c;
  9    loop
 10       fetch c bulk collect into l_data LIMIT 100;
 11
 12       forall i in 1 .. l_data.count
 13           insert into myTable2 values l_data(i);
 14
 15       exit when c%notfound;
 16    end loop;
 17
 18  end;
 19  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> exec row_at_a_time_test

PL/SQL procedure successfully completed.

SQL> exec bulk_test

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table myTable1;

Table dropped.

SQL> drop table myTable2;

Table dropped.

SQL>
SQL>
SQL>