Oracle PL/SQL BULK COLLECT: Ejemplo FORALL

¿Qué es la RECOGER A GRANEL?

BULK COLLECT reduce los cambios de contexto entre el SQL y el motor PL/SQL y permite que el motor SQL obtenga los registros de una sola vez.

Oracle PL / SQL Proporciona la funcionalidad de recuperar los registros en masa en lugar de recuperarlos uno por uno. Este BULK COLLECT se puede utilizar en una instrucción SELECT para llenar los registros en masa o para recuperar un cursor En lote. Dado que BULK COLLECT recupera los registros en lote, la cláusula INTO siempre debe contener una variable de tipo colección. La principal ventaja de usar BULK COLLECT es que mejora el rendimiento al reducir la interacción entre la base de datos y el motor PL/SQL.

Sintaxis:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

En la sintaxis anterior, BULK COLLECT se utiliza para recopilar los datos de las sentencias SELECT y FETCH.

Cláusula FORALL

La instrucción FORALL realiza Operaciones DML sobre datos masivos. Se asemeja a un bucle FOR, con la diferencia de que en un bucle FOR las acciones se realizan a nivel de registro, mientras que en FORALL no existe el concepto de bucle. En cambio, todos los datos presentes en el rango especificado se procesan simultáneamente.

Sintaxis:

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

En la sintaxis anterior, la operación DML especificada se ejecutará para todos los datos que se encuentren entre el rango inferior y el superior.

Cláusula LÍMITE

El concepto de recopilación masiva carga todos los datos en la variable de recopilación de destino de una sola vez. Sin embargo, esto no es recomendable cuando el número total de registros que se deben cargar es muy grande, ya que al intentar cargar todos los datos mediante PL/SQL se consume mucha memoria de sesión. Por lo tanto, siempre es conveniente limitar el tamaño de esta operación de recopilación masiva.

Este límite de tamaño se puede lograr fácilmente introduciendo la condición ROWNUM en la instrucción SELECT, mientras que en el caso de un cursor esto no es posible.

Para superar esto, Oracle ha proporcionado la cláusula LIMIT que define el número de registros que deben incluirse en el lote.

Sintaxis:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

En la sintaxis anterior, la instrucción de obtención de cursor utiliza la instrucción BULK COLLECT junto con la cláusula LIMIT.

Atributos de RECOGER A GRANEL

De forma similar a los atributos del cursor, BULK COLLECT tiene %BULK_ROWCOUNT(n) que devuelve el número de filas afectadas en la enésima instrucción DML de la instrucción FORALL; es decir, proporciona el recuento de registros afectados en la instrucción FORALL para cada valor de la variable de colección. El término 'n' indica la secuencia del valor en la colección para el cual se necesita el recuento de filas.

Ejemplo 1: En este ejemplo, proyectaremos todos los nombres de los empleados de la tabla emp usando BULK COLLECT, y también vamos a aumentar el salario de todos los empleados en 5000 usando FORALL.

La captura de pantalla a continuación muestra este ejemplo de BULK COLLECT y FORALL junto con su salida en Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

Resultado

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code Explicación:

• Code línea 2: Declarando el cursor guru99_det para la instrucción 'SELECT emp_name FROM emp'.
• Code línea 3: Declarando lv_emp_name_tbl como una tabla de tipo VARCHAR2(50).
• Code línea 4: Declarar lv_emp_name como el tipo lv_emp_name_tbl.
• Code línea 6: Abriendo el cursor.
• Code línea 7: Obteniendo el cursor usando BULK COLLECT con un tamaño LIMIT de 5000 en la variable lv_emp_name.
• Code líneas 8-11: Configurar un bucle FOR para imprimir todos los registros de la colección lv_emp_name.
• Code línea 12: Utilizar FORALL para actualizar el salario de todos los empleados en 5000.
• Code línea 14: Cometer el transaccional.

Preguntas Frecuentes