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

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. bulk collect

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>