select BULK COLLECT : bulk collect « PL SQL Statements « Oracle PL/SQL Tutorial






SQL> create table cities
  2  as
  3  select username city
  4    from all_users
  5   where rownum<=37;

Table created.

SQL>
SQL> alter table cities
  2  add constraint
  3  cities_pk primary key(city);

Table altered.

SQL>
SQL> create table with_ri
  2  ( x    char(80),
  3    city references cities
  4  );

Table created.

SQL>
SQL> create table without_ri
  2  ( x    char(80),
  3    city varchar2(30)
  4  );

Table created.

SQL> alter session set sql_trace=true;

Session altered.

SQL>
SQL> declare
  2      type array is table of varchar2(30) index by binary_integer;
  3      l_data array;
  4  begin
  5      select * BULK COLLECT into l_data from cities;
  6      for i in 1 .. 1000
  7      loop
  8          for j in 1 .. l_data.count
  9          loop
 10              insert into with_ri
 11              values ('x', l_data(j) );
 12              insert into without_ri
 13              values ('x', l_data(j) );
 14          end loop;
 15      end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table cities cascade constraint;

Table dropped.

SQL> drop table with_ri;

Table dropped.

SQL> drop table without_ri;

Table dropped.

SQL>
SQL>








22.17.bulk collect
22.17.1.select BULK COLLECT
22.17.2.Adding a limit to BULK COLLECT
22.17.3.To get the same result by using explicit cursors and bulk collect
22.17.4.Speeding Up Data Collection with Bulk Operations
22.17.5.Using the RETURNING ...BULK COLLECT clause to SELECT directly into a PL/SQL array
22.17.6.Demonstrate how to use DELETE with bulk processing.
22.17.7.Bulk processing with the FORALL statement
22.17.8.Bulk collect with non-cursor SELECT into multiple collections
22.17.9.Bulk collect from cursor with LIMIT option