Oracle PL/SQL BULK COLLECT: Ejemplo FORALL

¿Qué es la RECOGER A GRANEL?

BULK COLLECT reduce los cambios de contexto entre SQL y el motor PL/SQL y permite que el motor SQL recupere los registros a la vez.

Oracle PL/SQL proporciona la funcionalidad de recuperar los registros de forma masiva en lugar de recuperarlos uno por uno. Este BULK COLLECT se puede utilizar en la instrucción 'SELECT' para completar los registros de forma masiva o para recuperar el cursor de forma masiva. Dado que BULK COLLECT recupera el registro en BULK, la cláusula INTO siempre debe contener una variable de tipo de colección. La principal ventaja de utilizar BULK COLLECT es que aumenta el rendimiento al reducir la interacción entre la base de datos y el motor PL/SQL.

Sintaxis:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

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

Cláusula FORALL

FORALL permite realizar operaciones DML sobre datos en masa. Es similar a la instrucción de bucle FOR excepto en En bucle las cosas suceden a nivel de récord, mientras que en FORALL no existe el concepto LOOP. En lugar de ello, todos los datos presentes en el rango dado se procesan al mismo tiempo.

Sintaxis:

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

<DML operations>;

En la sintaxis anterior, la operación DML dada se ejecutará para todos los datos presentes 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 forma masiva, es decir, todos los datos se completarán en la variable de recopilación de una sola vez. Pero esto no es aconsejable cuando el registro total que se necesita cargar es muy grande, porque cuando PL / SQL Si intenta cargar todos los datos, consumirá más memoria de sesión. Por lo tanto, siempre es bueno limitar el tamaño de esta operación de recopilación masiva.

Sin embargo, este límite de tamaño se puede lograr fácilmente introduciendo la condición ROWNUM en la declaración 'SELECT', mientras que en el caso del cursor esto no es posible.

para superar esto Oracle ha proporcionado la cláusula 'LIMIT' que define la cantidad 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 recuperación del cursor utiliza la instrucción BULK COLLECT junto con la cláusula LIMIT.

Atributos de RECOGER A GRANEL

Similar a cursor atributos BULK COLLECT tiene %BULK_ROWCOUNT(n) que devuelve el número de filas afectadas en el n^th Declaración DML de la declaración FORALL, es decir, dará el recuento de registros afectados en la declaración FORALL para cada valor de la variable de colección. El término 'n' indica la secuencia de valores en la colección, para la cual se necesita el recuento de filas.

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

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 salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;	
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

Salida

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

Explicación del código:

• Línea de código 2: Declarar el cursor guru99_det para la declaración 'SELECT emp_name FROM emp'.
• Línea de código 3: Declarar lv_emp_name_tbl como tipo de tabla de VARCHAR2(50)
• Línea de código 4: Declarar lv_emp_name como tipo lv_emp_name_tbl.
• Línea de código 6: Abriendo el cursor.
• Línea de código 7: Obteniendo el cursor usando BULK COLLECT con el tamaño LIMIT como variable 5000 intl lv_emp_name.
• Línea de código 8-11: Configurar el bucle FOR para imprimir todos los registros de la colección lv_emp_name.
• Línea de código 12: Usando FORALL actualizando el salario de todos los empleados en 5000.
• Línea de código 14: Comprometiendo la transacción.