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

  1. 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.

  2. Declaración de la Variable de Colección:

    l_emps t_emp;
    

    Se declara una variable de tipo t_emp.

  3. Uso de BULK COLLECT:

    SELECT * BULK COLLECT INTO l_emps FROM employees;
    

    Se utiliza BULK COLLECT para recuperar todas las filas de la tabla employees en la colección l_emps.

  4. 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

  1. 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.

  2. 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.

  3. 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 sentencia DELETE para cada employee_id en la colección l_emp_ids.

  4. Confirmación de la Transacción:

    COMMIT;
    

    Se confirma la transacción para hacer permanentes los cambios.

Ejercicio Práctico

Ejercicio

  1. Crea una tabla llamada departments con las siguientes columnas:

    • department_id (NUMBER)
    • department_name (VARCHAR2)
  2. Inserta algunos registros en la tabla departments.

  3. Escribe un bloque PL/SQL que utilice BULK COLLECT para recuperar todos los registros de la tabla departments en una colección.

  4. 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

  1. 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;
    
  2. 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;
    /
    
  3. 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.

© Copyright 2024. Todos los derechos reservados