Revisión actual del 18:08 26 ago 2009

BULK COLLECT es una función de Oracle que facilita la selección masiva de información de la base a alta velocidad, permitiendo el fetch de múltiples filas en una o mas colecciones.

Adicionalmente, el uso del FORALL facilita la ejecución masiva de inserts, updates y deletes, permitiendo transferir la información de una o mas colecciones a la base. Ambas alternativas pueden ser utilizadas de manera conjunta o individual, y la principal implicancia es el incremento en la performance de las operaciones.

Ejemplo

create table t1(cod_cliente  number(10),
                des_cliente  varchar2(10),
                fec_proceso  date);

create table t2(num_abonado  number(10),
                des_abonado  varchar2(10),
                cod_cliente  number(10));

create table t3(cod_cliente  number(10),
                des_cliente  varchar2(10),
                cnt_abonado  number(10));

insert  into t1
select  rownum,'C' || lpad(rownum,9,0),null
from    dual
connect by level <= 5000;

insert  into t2
select  rownum,'A' || lpad(rownum,9,0),mod(rownum-1,5000)+1
from    dual
connect by level <= 20000;

commit;

Caso 1: sin manejo de excepciones

declare

type tty_rowid       is table of urowid              index by binary_integer;
type tty_cod_cliente is table of t3.cod_cliente%type index by binary_integer;
type tty_des_cliente is table of t3.des_cliente%type index by binary_integer;
type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;

tab_rowid       tty_rowid;
tab_cod_cliente tty_cod_cliente;
tab_des_cliente tty_des_cliente;
tab_cnt_abonado tty_cnt_abonado;

i number := 0;

cursor cur_test is select  t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
                   from     t1,t2
                   where  t1.cod_cliente = t2.cod_cliente
                   group by t1.rowid,t1.cod_cliente,t1.des_cliente;

begin
 open cur_test;
 loop
     fetch cur_test bulk collect into tab_rowid,
                                      tab_cod_cliente,
                                      tab_des_cliente,
                                      tab_cnt_abonado limit 100;
     exit when tab_rowid.count=0;

     forall i in 1..tab_rowid.count
       insert into t3 values(tab_cod_cliente(i),
                             tab_des_cliente(i),
                             tab_cnt_abonado(i));

     forall i in 1..tab_rowid.count
       update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);

     commit;

 end loop;

 close cur_test;

exception
  when others then
     rollback;
end;

Caso 2: con manejo de excepciones global

declare

 type tty_rowid       is table of urowid              index by binary_integer;
 type tty_cod_cliente is table of t1.cod_cliente%type index by binary_integer;
 type tty_des_cliente is table of t1.des_cliente%type index by binary_integer;
 type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;

 tab_rowid       tty_rowid;
 tab_cod_cliente tty_cod_cliente;
 tab_des_cliente tty_des_cliente;
 tab_cnt_abonado tty_cnt_abonado;

 v_rows_ok  number := 0;
 v_rows_er  number := 0;
 i number := 0;

 cursor cur_test is select t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
                    from    t1,t2
                    where  t1.cod_cliente = t2.cod_cliente
                    group  by t1.rowid,t1.cod_cliente,t1.des_cliente
                    order  by t1.cod_cliente;

excp_bulk_error exception;
pragma exception_init (excp_bulk_error, -24381 );

begin

 open cur_test;

 loop

   fetch cur_test bulk collect into tab_rowid,
                                    tab_cod_cliente,
                                    tab_des_cliente,
                                    tab_cnt_abonado limit 100;
   exit when tab_rowid.count=0;

   forall i in 1..tab_rowid.count save exceptions
          insert into t3 values(tab_cod_cliente(i),
                                tab_des_cliente(i),
                                tab_cnt_abonado(i));

   forall i in 1..tab_rowid.count
          update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);

   commit;

 end loop;

 close cur_test;

exception

  when excp_bulk_error then
       p('# Handler global ');
       v_rows_ok := sql%rowcount;
       v_rows_er := sql%bulk_exceptions.count;
       p('# Rows[OK] ',v_rows_ok);
       p('# Rows[ER] ',v_rows_er);
       for i in 1..v_rows_er loop
           p('# Row         ',sql%bulk_exceptions(i).error_index);
           p('# Code        ',sql%bulk_exceptions(i).error_code);
           p('# Descripcion ',sqlerrm(-sql%bulk_exceptions(i).error_code));
       end loop;
       rollback;

  when others then
       rollback;

end;

Caso 3: con manejo de excepciones local

declare

 type tty_rowid       is table of urowid              index by binary_integer;
 type tty_cod_cliente is table of t1.cod_cliente%type index by binary_integer;
 type tty_des_cliente is table of t1.des_cliente%type index by binary_integer;
 type tty_cnt_abonado is table of t3.cnt_abonado%type index by binary_integer;

 tab_rowid       tty_rowid;
 tab_cod_cliente tty_cod_cliente;
 tab_des_cliente tty_des_cliente;
 tab_cnt_abonado tty_cnt_abonado;

 v_rows_ok   number := 0;
 v_rows_er   number := 0;
 i number := 0;

 cursor cur_test is select t1.rowid,t1.cod_cliente,t1.des_cliente,count(*)
                    from   t1,t2
                    where  t1.cod_cliente = t2.cod_cliente
                    group  by t1.rowid,t1.cod_cliente,t1.des_cliente
                    order  by t1.cod_cliente;

 excp_bulk_error exception;
 pragma exception_init (excp_bulk_error, -24381 );

begin

 open cur_test;

 loop

   fetch cur_test bulk collect into tab_rowid,
                                    tab_cod_cliente,
                                    tab_des_cliente,
                                    tab_cnt_abonado limit 100;

   exit when tab_rowid.count=0;

   begin

     forall i in 1..tab_rowid.count save exceptions
            insert into t3 values(tab_cod_cliente(i),
                                  tab_des_cliente(i),
                                  tab_cnt_abonado(i));

     forall i in 1..tab_rowid.count
            update t1 set fec_proceso = sysdate where rowid = tab_rowid(i);

  exception
     when excp_bulk_error then
           p('# Handler local ');
           v_rows_ok := sql%rowcount;
           v_rows_er := sql%bulk_exceptions.count;
           p('# Rows[OK] ',v_rows_ok);
           p('# Rows[ER] ',v_rows_er);
           for i in 1..v_rows_er loop
              p('# Row         ',sql%bulk_exceptions(i).error_index);
              p('# Code        ',sql%bulk_exceptions(i).error_code);
              p('# Descripcion ',sqlerrm(-sql%bulk_exceptions(i).error_code));
           end loop;
          rollback;
   end;

  commit;

end loop;

close cur_test;

exception
  when others then
       rollback;

end;

Estos casos ejemplos y otros relacionados pueden verse en el package th_blkav_pkg

Sintaxis

SELECT C1,C2,….,CN BULK COLLECT INTO COL1,COL2,….COLN.
SELECT C1,C2,….,CN BULK COLLECT INTO COLT.
FETCH  C  BULK COLLECT INTO COL1,COL2,….COLN LIMIT MAXROWS
FETCH  C  BULK COLLECT INTO COLT LIMIT MAXROWS.

Sugerencias de Uso

Utlizar un LIMIT adecuado para cada fetch, utilizar uno extremadamente grande puede generar problemas de memoria sobre el server.

Casos prácticos de uso

Procesamiento masivo de datos.

Ver también

• Oracle