En este tema, aprenderemos sobre la colección masiva en PL/SQL, una técnica que permite mejorar el rendimiento al manipular grandes volúmenes de datos. La colección masiva se utiliza para reducir el número de cambios de contexto entre PL/SQL y SQL, lo que puede ser costoso en términos de rendimiento.
¿Qué es la Colección Masiva?
La colección masiva en PL/SQL se refiere a la capacidad de recuperar y procesar múltiples filas de datos en una sola operación. Esto se logra utilizando las cláusulas BULK COLLECT
y FORALL
.
Ventajas de la Colección Masiva
- Reducción de Cambios de Contexto: Minimiza el número de cambios de contexto entre PL/SQL y SQL.
- Mejora del Rendimiento: Aumenta la eficiencia al procesar grandes volúmenes de datos.
- Código Más Limpio: Simplifica el código al reducir la necesidad de bucles explícitos para manejar filas individuales.
Sintaxis de BULK COLLECT
La cláusula BULK COLLECT
se utiliza para recuperar múltiples filas de una consulta SQL en una colección PL/SQL (como un array o una tabla anidada).
Ejemplo Básico
DECLARE TYPE t_emp IS TABLE OF employees%ROWTYPE; l_emps t_emp; BEGIN SELECT * BULK COLLECT INTO l_emps FROM employees; FOR i IN 1..l_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Employee ID: ' || l_emps(i).employee_id); END LOOP; END; /
Explicación del Código
-
Declaración del Tipo de Colección:
TYPE t_emp IS TABLE OF employees%ROWTYPE;
Se declara un tipo de colección basado en la estructura de la tabla
employees
. -
Declaración de la Variable de Colección:
l_emps t_emp;
Se declara una variable de tipo
t_emp
. -
Uso de BULK COLLECT:
SELECT * BULK COLLECT INTO l_emps FROM employees;
Se utiliza
BULK COLLECT
para recuperar todas las filas de la tablaemployees
en la colecciónl_emps
. -
Iteración sobre la Colección:
FOR i IN 1..l_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Employee ID: ' || l_emps(i).employee_id); END LOOP;
Se itera sobre la colección y se imprime el
employee_id
de cada fila.
Uso de FORALL
La cláusula FORALL
se utiliza para ejecutar una sentencia DML (INSERT, UPDATE, DELETE) para cada elemento de una colección.
Ejemplo Básico
DECLARE TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE; l_emp_ids t_emp_ids := t_emp_ids(1001, 1002, 1003); BEGIN FORALL i IN l_emp_ids.FIRST..l_emp_ids.LAST DELETE FROM employees WHERE employee_id = l_emp_ids(i); COMMIT; END; /
Explicación del Código
-
Declaración del Tipo de Colección:
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
Se declara un tipo de colección basado en el tipo de datos de
employee_id
. -
Inicialización de la Colección:
l_emp_ids t_emp_ids := t_emp_ids(1001, 1002, 1003);
Se inicializa la colección con algunos
employee_id
. -
Uso de FORALL:
FORALL i IN l_emp_ids.FIRST..l_emp_ids.LAST DELETE FROM employees WHERE employee_id = l_emp_ids(i);
Se utiliza
FORALL
para ejecutar la sentenciaDELETE
para cadaemployee_id
en la colecciónl_emp_ids
. -
Confirmación de la Transacción:
COMMIT;
Se confirma la transacción para hacer permanentes los cambios.
Ejercicio Práctico
Ejercicio
-
Crea una tabla llamada
departments
con las siguientes columnas:department_id
(NUMBER)department_name
(VARCHAR2)
-
Inserta algunos registros en la tabla
departments
. -
Escribe un bloque PL/SQL que utilice
BULK COLLECT
para recuperar todos los registros de la tabladepartments
en una colección. -
Escribe otro bloque PL/SQL que utilice
FORALL
para actualizar el nombre de todos los departamentos en la colección.
Solución
-- Paso 1: Crear la tabla departments CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50) ); -- Paso 2: Insertar registros en la tabla departments INSERT INTO departments VALUES (1, 'HR'); INSERT INTO departments VALUES (2, 'Finance'); INSERT INTO departments VALUES (3, 'IT'); COMMIT; -- Paso 3: Bloque PL/SQL con BULK COLLECT DECLARE TYPE t_dept IS TABLE OF departments%ROWTYPE; l_depts t_dept; BEGIN SELECT * BULK COLLECT INTO l_depts FROM departments; FOR i IN 1..l_depts.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Department ID: ' || l_depts(i).department_id || ', Name: ' || l_depts(i).department_name); END LOOP; END; / -- Paso 4: Bloque PL/SQL con FORALL DECLARE TYPE t_dept IS TABLE OF departments%ROWTYPE; l_depts t_dept; BEGIN SELECT * BULK COLLECT INTO l_depts FROM departments; FOR i IN 1..l_depts.COUNT LOOP l_depts(i).department_name := l_depts(i).department_name || ' Department'; END LOOP; FORALL i IN l_depts.FIRST..l_depts.LAST UPDATE departments SET department_name = l_depts(i).department_name WHERE department_id = l_depts(i).department_id; COMMIT; END; /
Explicación de la Solución
-
Creación de la Tabla y Inserción de Datos:
CREATE TABLE departments ( department_id NUMBER, department_name VARCHAR2(50) ); INSERT INTO departments VALUES (1, 'HR'); INSERT INTO departments VALUES (2, 'Finance'); INSERT INTO departments VALUES (3, 'IT'); COMMIT;
-
Uso de BULK COLLECT:
DECLARE TYPE t_dept IS TABLE OF departments%ROWTYPE; l_depts t_dept; BEGIN SELECT * BULK COLLECT INTO l_depts FROM departments; FOR i IN 1..l_depts.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Department ID: ' || l_depts(i).department_id || ', Name: ' || l_depts(i).department_name); END LOOP; END; /
-
Uso de FORALL:
DECLARE TYPE t_dept IS TABLE OF departments%ROWTYPE; l_depts t_dept; BEGIN SELECT * BULK COLLECT INTO l_depts FROM departments; FOR i IN 1..l_depts.COUNT LOOP l_depts(i).department_name := l_depts(i).department_name || ' Department'; END LOOP; FORALL i IN l_depts.FIRST..l_depts.LAST UPDATE departments SET department_name = l_depts(i).department_name WHERE department_id = l_depts(i).department_id; COMMIT; END; /
Conclusión
En esta sección, hemos aprendido sobre la colección masiva en PL/SQL utilizando BULK COLLECT
y FORALL
. Estas técnicas son esenciales para mejorar el rendimiento al manipular grandes volúmenes de datos. Asegúrate de practicar estos conceptos con diferentes tipos de datos y escenarios para dominar su uso.
En el próximo tema, exploraremos la sentencia FORALL
en mayor detalle y cómo se puede utilizar para optimizar aún más las operaciones DML en PL/SQL.
Curso de PL/SQL
Módulo 1: Introducción a PL/SQL
Módulo 2: Fundamentos de PL/SQL
- Estructura del Bloque PL/SQL
- Variables y Tipos de Datos
- Estructuras de Control
- Cursores
- Manejo de Excepciones