BULK COLLECT: ejemplos

Os recomiendo tambien leer: introducción a BULK COLLECT

EJEMPLO recuperar registros con bulk collect:

DECLARE
   TYPE t_salarios IS TABLE OF empleado.salario%TYPE;
   salarios t_salarios;
BEGIN
   SELECT salario BULK COLLECT INTO salarios FROM empleados
      WHERE ROWNUM <= 1000;
END;

EJEMPLO recuperar registros utilizando un cursor:

DECLARE
   TYPE DeptRecTab IS TABLE OF departamentos%ROWTYPE;
   dept_recs DeptRecTab;
   CURSOR c IS
      SELECT id_departamento, nob_departamento FROM departamentos WHERE id_departamento > 100;
BEGIN
   OPEN c;
   FETCH c BULK COLLECT INTO dept_recs;
END;

EJEMPLO borrado:

DECLARE
   TYPE t_lista_num IS TABLE OF empleados.id_empleado%TYPE;
   enums t_lista_num;
BEGIN
   -- enums devolvera los id_empleados de los empleados del departamento 25
   DELETE FROM empleados WHERE id_departamento = 25
      RETURNING id_empleado BULK COLLECT INTO enums;
END;

FORALL

La clausula FORALL permite sin cambio de contexto realizar una sentencia DML de manera masiva

SINTAXIS FORALL:

FORALL indice IN limite_inferior..limite_superior
   sentencia_SQL;

EJEMPLO BULK COLLECT Y FORALL:

declare
  type t_array_elementos is table of elementos_orig%rowtype
                      index by binary_integer;

  array_elementos t_array_elementos;

  cursor c1 is 
     select * from elemetos_orig;
  
  limit_in integer := 1000;
begin
  open cursor;
  loop
    fetch c1 bulk collect into array_elementos limit limit_in;

   begin
     forall i in 1..array_elementos.count save exceptions
       insert into elementos_dest2 values array_elementos (i);
   exception
     when others then
       dbms_output.put_line (sqlerrm);
   end;
   
   EXIT WHEN c1%NOTFOUND;
  end loop;
  commit;
  close c1;
exception
  when others then
    dbms_output.put_line (sqlerrm);
end;

EJEMPLO CON TRATAMIENTO DE EXCEPCIONES:

declare
  type t_array_elementos is table of elementos_orig%rowtype
                      index by binary_integer;

  array_elementos t_array_elementos;

  cursor cursor is 
     select * from elemetos_orig;
  
  errores PLS_INTEGER;
  array_dml EXCEPTIONS;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  
  limit_in PLS_INTEGER := 1000;
  
  total_errores PLS_INTEGER := 0;
BEGIN
  OPEN cursor;
  LOOP
   FETCH cursor BULK COLLECT INTO array_elementos LIMIT limit_in;

   BEGIN
	    FORALL i IN 1..array_elementos.COUNT SAVE EXCEPTIONS -- save exceptions evita, que se detenga, cpaturando la excepcion
	       INSERT INTO elementos_dest2 VALUES array_elementos (i);
   EXCEPTION
	   WHEN dml_errors THEN  -- Captura las excepciones al realizar operaciones DML
	        
	        -- Contabiliza los errores
	        errores := SQL%BULK_EXCEPTIONS.COUNT;
	        dbms_output.put_line('Cantidad de registros que fallaron: ' || errores);
	        total_errores := total_errores + errores;
	        
	        -- Extrae la información de los errores
	        FOR i IN 1 .. errores LOOP 
	            dbms_output.put_line('Error #' || i || ' at '|| 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
	            dbms_output.put_line('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
	        END LOOP;
   END;
   
   EXIT WHEN cursor%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE cursor;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (sqlerrm);
END;

Os recomiendo tambien leer: BULK COLLECT