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






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>








25.4.Fetch
25.4.1.Fetch data into PL/SQL table
25.4.2.Fetch cursor value into column type variable
25.4.3.Fetch cursor till cursorName%NOTFOUND
25.4.4.Using a simple UPDATE statement without locking for rows fetched from Cursors
25.4.5.To lock all the records while you're working on them. This is done using a SELECT FOR UPDATE command
25.4.6.Fetching Across Commits
25.4.7.Fetching Across Commits, Example 2
25.4.8.Populating a Record with FETCH INTO
25.4.9.cursor bulk
25.4.10.Compare the performance differences between row-at-a-time processing and bulk processing
25.4.11.Raise no data found exception if cursor is empty
25.4.12.Fetch cursor to three variables
25.4.13.Nested cursor open
25.4.14.Fetch cursor till notfound
25.4.15.Fetch cursor to table collection of row type
25.4.16.Fetch cursor value to three variables
25.4.17.Fetch row by row