Lunes, 08 Junio 2020 01:36

Ejemplos de Colecciones

Rate this item
(0 votes)

Objetivos:
• Introducción al método BULK COLLECT.

• Ver Ejemplos Prácticos de Colecciones de PL/SQL.
• Usar Colecciones como valor de retorno de Subprogramas.


NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

BULK COLLECT

Con BULK COLLECT podemos obtener grandes cantidades de datos de manera mas rápida y optima que con cualquier otro método de consulta. Al usar BULK COLLECT, el motor de PL/SQL le dice al motor SQL que colecte muchas filas a la vez y las coloque en una Colección previamente definida. Durante una recolección masiva de Oracle, el motor SQL recupera todas las filas y las carga en la Colección para luego cambiar de nuevo al motor PL/SQL. Cuanto mayor sea el número de filas que desea extraer, mayor seria el rendimiento de la consulta.
Ejemplo:
Nota: El siguiente ejemplo tiene como Fin Único mostrar el desempeño/rendimiento del método BULK COLLECT, por lo cual, el mismo puede no se perciba una utilidad en el mundo real.
DECLARE
    --Definimos este CURSOR con el fin de mostrar el rendimiento de BULK COLLECT.
    CURSOR  c_cross_join IS
        SELECT  cross.*     --131,079,601 Registros en Total.
        FROM
            (
                SELECT
                        e.employee_id,
                        e.last_name,
                        e.salary    
                FROM    hr.employees e
                CROSS JOIN hr.employees e1
            ) cross
        CROSS JOIN
            (
                SELECT
                        e.employee_id,
                        e.last_name,
                        e.salary    
                FROM    hr.employees e
                CROSS JOIN hr.employees e1
            ) cross1
        WHERE   ROWNUM  < 1000001;      --Para los fines de este ejemplo, un millón sera suficiente.
 
    --Record con campos acordes al CURSOR: c_cross_join.
    TYPE  typ_emp_rec IS RECORD
    (
        emp_id      hr.employees.employee_id%TYPE,
        emp_name    hr.employees.last_name%TYPE,
        emp_sal     hr.employees.salary%TYPE
    );
 
    --Definimos el Tipo Colección(Matriz Asociativa)
    TYPE  typ_assoc_array
        IS TABLE OF typ_emp_rec
            INDEX BY PLS_INTEGER;
 
    --Definimos la variable Colección: v_assoc_array donde haremos el FETCH de los datos.
    v_assoc_array   typ_assoc_array;
 
    --La variable v_method indicara el método a ejecutar:
    ---Si es 1 ejecutara el método BULK COLLECT.
    ---De lo contrario usara el Tradicional.
    v_method        NUMBER    :=  1;
    v_method_name   VARCHAR2(12);
    v_counter       NUMBER    :=  1;
BEGIN
    CASE
        WHEN  v_method  = 1 THEN
            OPEN  c_cross_join;
            LOOP    --LOOP Simple
                --La cláusula LIMIT hará que el BULK COLLECT colecte los registros de 500 mil en 500 mil.
                FETCH c_cross_join BULK COLLECT INTO v_assoc_array LIMIT 500000;
 
                EXIT
                    WHEN  v_counter   >   1;    --Para salir del LOOP en la segunda Iteración.
                v_counter   :=  v_counter+1;
            END LOOP;
            CLOSE c_cross_join;
 
            v_method_name   :=  'BULK COLLECT';
        ELSE
            FOR i IN c_cross_join LOOP    --CURSOR FOR LOOP
                v_assoc_array(v_counter).emp_id     :=  i.employee_id;
                v_assoc_array(v_counter).emp_name   :=  i.last_name;
                v_assoc_array(v_counter).emp_sal    :=  i.salary;
 
                v_method_name   :=  'Tradicional';
                v_counter :=  v_counter+1;
            END LOOP;     
    END CASE;
 
    --Notaran que la Colección trae el millón de registros del CURSOR solo cuando se usa el método Tradicional
    --Esto es porque por cada Iteración del LOOP Simple se reemplazan los 500 mil primeros Indices de la Colección
    --con los nuevos datos extraídos del CURSOR. Recuerden que el Fin del ejemplo es mostrar el Rendimiento del BULK COLLECT.
    DBMS_OUTPUT.PUT_LINE('Número de Indices: '||v_assoc_array.COUNT);
 
    --Muestro la Fecha y Hora para que se vea claro que primero ejecuto el método BULK COLLECT.
    DBMS_OUTPUT.PUT_LINE('Método: '||v_method_name||CHR(10)||'Fecha y Hora de Ejecución: '||SYSTIMESTAMP);
END;
/*El ejemplo muestra lo eficiente que resulta el uso de BULK COLLECT cuando se trata de grandes cantidades de datos. Primero ejecutamos el método BULK COLLECT luego el Tradicional, esto para mostrar que aunque ya Oracle diseñó el Plan de Ejecución para la consulta, aun así el BULK COLLECT resulta mas optimo.*/
---OUTPUT:
BULK COLLECT CROSS JOIN
Ver que con el método Tradicional el SCRIPT se tomo 2 Segundo mientras que con el BULK COLLECT solo tardó 1 Segundo. Tener en cuenta que a mayor la cantidad de datos mejor el rendimiento,

Nota: Si se usa el método BULK COLLECT sin la cláusula LIMIT y de paso se le quitamos la condición de filtro: WHERE   ROWNUM  < 1000001 al CURSOROracle trataría de extraer los 131,079,601 Registros y esto podría congelar el computador.

Funciones que Reciben y Retornan Colecciones

Ejemplo:

Nota: A continuación creamos un Paquete de Base de Datos en cual aparte de usar las Colecciones, implementamos el concepto de Sobrecarga (Overload) de Funciones, si no esta familiarizado con ellas puede adquirir ayuda en esta publicación: Sobrecargas en Oracle.

Si necesita ayuda con los paquetes, este tema podría ayudarle: Paquetes de PL/SQL.

CREATE OR REPLACE PACKAGE   hr.pkg_collections IS
    --Definimos los Tipo Records:
    TYPE typ_emp_rec IS RECORD
    (
        last_name           hr.employees.last_name%TYPE,
        salary              hr.employees.salary%TYPE,
        job_title           hr.jobs.job_title%TYPE,
        department_name     hr.departments.department_name%TYPE,
        country_name        hr.countries.country_name%TYPE
    );
    --Definimos los Tipo Colecciones:
 
    --Para usarlos como parámetros de entrada:
    TYPE typ_assoc_array_in
        IS TABLE OF hr.jobs.job_id%TYPE
            INDEX BY PLS_INTEGER;
    TYPE typ_nest_tab_in
        IS TABLE OF hr.departments.department_id%TYPE;
    TYPE typ_varray_in
        IS VARRAY(1) OF hr.countries.country_id%TYPE;
 
    --Para usarlos de Retorno:
    TYPE typ_assoc_array
        IS TABLE OF typ_emp_rec
            INDEX BY PLS_INTEGER;
    TYPE typ_nest_tab
        IS TABLE OF typ_emp_rec;
    TYPE typ_varray
        IS VARRAY(200) OF typ_emp_rec;
 
    --Definimos los Subprogramas:
    FUNCTION   f_bring_details (p_input     IN      typ_assoc_array_in)
        RETURN  typ_assoc_array;
    FUNCTION   f_bring_details (p_input     IN      typ_nest_tab_in)
        RETURN  typ_nest_tab;
    FUNCTION   f_bring_details (p_input     IN      typ_varray_in)
        RETURN  typ_varray;
END pkg_collections;
/*He aquí la Especificación de Paquete: pkg_collections, los objetos, variables y subprogramas definidos aquí serán públicos, de esta manera cualquier usuario con privilegios sobre el paquete podrá crear instancias de los mismos. Notar como la función: f_bring_details aparece 3 veces con tipos de parámetros y retornos distintos (cada uno recibe y retorna un tipo colección diferente). De esta manera podrán ver como cada Colección tiene un manejo ligeramente distinto pero se pueden usar para el mismo fin.*/
 
CREATE OR REPLACE PACKAGE  BODY hr.pkg_collections IS
    --Definimos las variables y CURSORES Privados:
    v_filter_char      VARCHAR2(10);
    v_filter_number    NUMBER;
    v_counter          NUMBER(3)    :=  1;
 
    CURSOR  c_emp_details IS
        SELECT
                e.last_name,
                e.salary,
                j.job_title,
                d.department_name,
                c.country_name
        FROM
                employees   e,
                departments d,
                jobs        j,
                locations   l,
                countries   c
        WHERE   (
                    j.job_id = v_filter_char
                 OR
                    d.department_id = v_filter_number
                 OR
                    c.country_id = v_filter_char
                )
        AND     e.job_id = j.job_id
        AND     e.department_id = d.department_id
        AND     d.location_id = l.location_id
        AND     l.country_id = c.country_id
        ORDER BY    e.employee_id DESC;
 
    --Implementación de los los Subprogramas:
    FUNCTION   f_bring_details (p_input     IN      typ_assoc_array_in)
        RETURN  typ_assoc_array IS
    v_assoc_array     typ_assoc_array;
    BEGIN
        v_filter_char   :=  p_input(1);
 
        FOR i IN c_emp_details LOOP
            v_assoc_array(v_counter).last_name  :=  i.last_name;
            v_assoc_array(v_counter).salary  :=  i.salary;
            v_assoc_array(v_counter).job_title  :=  i.job_title;
            v_assoc_array(v_counter).department_name  :=  i.department_name;
            v_assoc_array(v_counter).country_name  :=  i.country_name;
 
            v_counter   :=  v_counter+1;
        END LOOP;
        RETURN v_assoc_array;
    END f_bring_details;
 
    FUNCTION   f_bring_details (p_input     IN      typ_nest_tab_in)
        RETURN  typ_nest_tab IS
    v_nest_tab     typ_nest_tab;
 
    BEGIN
        v_nest_tab     := typ_nest_tab();
        v_filter_char   :=  p_input(1);
 
        FOR i IN c_emp_details LOOP
            v_nest_tab(v_counter).last_name  :=  i.last_name;
            v_nest_tab(v_counter).salary  :=  i.salary;
            v_nest_tab(v_counter).job_title  :=  i.job_title;
            v_nest_tab(v_counter).department_name  :=  i.department_name;
            v_nest_tab(v_counter).country_name  :=  i.country_name;
 
            v_counter   :=  v_counter+1;
        END LOOP;
        RETURN  v_nest_tab;
    END f_bring_details;
 
    FUNCTION   f_bring_details (p_input     IN      typ_varray_in)
        RETURN  typ_varray IS
    v_varray      typ_varray;
 
    BEGIN
        v_varray      :=    typ_varray();
        v_filter_char   :=  p_input(1);
 
        FOR i IN c_emp_details LOOP
            v_varray(v_counter).last_name  :=  i.last_name;
            v_varray(v_counter).salary  :=  i.salary;
            v_varray(v_counter).job_title  :=  i.job_title;
            v_varray(v_counter).department_name  :=  i.department_name;
            v_varray(v_counter).country_name  :=  i.country_name;
 
            v_counter   :=  v_counter+1;
        END LOOP;
        RETURN  v_varray;
    END f_bring_details;
END pkg_collections;
/*He aquí el cuerpo del Paquete: pkg_collections; El mismo es donde desarrollamos la lógica del negocio y le damos funcionalidad a los subprogramos definidos en la especificación. Notar que en el cuerpo podemos también crear variables, CURSORES y hasta subprogramas que sirven de respaldo para implementar todo la lógica. Tenga en cuenta que los Objetos creados solo en el cuerpo del paquete son privados por lo cual ningún usuario podría crear instancias de los mismo fuera del paquete.*/

Implementación del Paquete: pkg_collections

Ejemplos:

DECLARE
    v_assoc_array_in    hr.pkg_collections.typ_assoc_array_in;
    v_assoc_array       hr.pkg_collections.typ_assoc_array;
BEGIN
    v_assoc_array_in(1)    :=  'IT_PROG';
    v_assoc_array       :=  hr.pkg_collections.f_bring_details(v_assoc_array_in);
 
    IF v_assoc_array.COUNT  > 0 THEN
        
        DBMS_OUTPUT.PUT_LINE(
                            LPAD('=',100,'=')||CHR(10)||
                            'Nombre    ||  Salario ||    Empleo           ||  Departamento    ||      País
                             '||CHR(10)||LPAD('=',100,'=')
                            );
        FOR i IN    v_assoc_array.FIRST..v_assoc_array.LAST LOOP
            DBMS_OUTPUT.PUT_LINE
                                (
                                    RPAD(v_assoc_array(i).last_name, 15, ' ')||
                                    RPAD(v_assoc_array(i).salary, 10, ' ')||
                                    RPAD(v_assoc_array(i).job_title, 26, ' ')||
                                    RPAD(v_assoc_array(i).department_name, 20, ' ')||
                                    v_assoc_array(i).country_name
                                );
        END LOOP;
    END IF;
END;
/*Para el ejemplo elegimos usar la sobrecarga que recibe y retorna el Tipo Colección: Matriz Asociativa. Notar que con tan solo pasarle una variable del Tipo: Matriz Asociativa Oracle se encarga de seleccionar la función acorde a ella.*/
---OUTPUT:
pkg collections implementation

Nota: La implementación de las demás funciones del paquete es similar a la que acabo de presentar, por tal razón no la publicare, si tienes dudas de como realizarlas, el siguiente Link le seria de gran ayuda: Las Colecciones de PL/SQL.

Bonus:

Para concluir decidimos presentar un ejemplo ligeramente complejo en cual usamos las Colecciones para similar un escenario común del día a día. El SCRIPT le permite crear una relación similar a la siguiente:
Tree
Nos limitamos este pequeño Albor para así facilitar el entendimiento del ejemplo, pero es prudente recargar que estas Estructuras de Datos combinadas con los Records tienen la capacidad de modelar escenarios de la vida diaria Bastante Complejos.

DECLARE
    --Record: typ_room_rec para representar los muebles de una casa.
    TYPE    typ_room_rec   IS RECORD
    (
        room_type       VARCHAR2(30),
        room_size       VARCHAR2(30)
    );

    --Colección: typ_room_tab para representar una lista de muebles.
    TYPE    typ_room_tab
            IS TABLE OF typ_room_rec
                INDEX BY PLS_INTEGER;

    --Record: typ_people_rec para representar personas.
    TYPE    typ_people_rec   IS RECORD
    (
        name        VARCHAR2(50),
        sex         CHAR(1),
        age         NUMBER(2),
        kin         VARCHAR2(15)
    );

    --Colección: typ_people_tab para representar un lista de personas.
    TYPE    typ_people_tab
            IS VARRAY(8) OF typ_people_rec;

    --Record: typ_estate_rec para representar una casa.
    TYPE    typ_estate_rec   IS RECORD
    (
        estate_type     VARCHAR2(30),
        location        VARCHAR2(50),
        rooms           typ_room_tab,
        people          typ_people_tab
    );

    --Colección: typ_state_tab para representar un lista de casas.
    TYPE    typ_state_tab
            IS TABLE OF typ_estate_rec;

    --Record: typ_owner_rec para representar el dueño de una lista de casas.
    TYPE    typ_owner_rec IS RECORD
    (
        owner       VARCHAR2(30),
        estates     typ_state_tab
    );

    v_owner_rec     typ_owner_rec;
    v_room_type     VARCHAR2(30);
    v_room_size     VARCHAR2(30);
    v_name          VARCHAR2(30);
    v_sex           CHAR(1);
    v_age           NUMBER(2);
    v_kin           VARCHAR2(30);
BEGIN
    v_owner_rec.owner       :=     'Moquete';

    v_owner_rec.estates     :=      typ_state_tab();
    v_owner_rec.estates.EXTEND;
    v_owner_rec.estates(1).estate_type  :=  'Apartamento';
    v_owner_rec.estates(1).location  :=  'San Juan de la Maguana';

    FOR i IN 1..5 LOOP
        CASE
            WHEN i < 3 THEN
                v_room_type :=  'Baño';
                v_room_size :=  '5 M2';
            ELSE
                v_room_type :=  'Habitación';
                v_room_size :=  '10 M2';
         END CASE;

        v_owner_rec.estates(1).rooms(i).room_type   :=  v_room_type;
        v_owner_rec.estates(1).rooms(i).room_type   :=  v_room_type;
    END LOOP;
    
    v_owner_rec.estates(1).people     :=    typ_people_tab();
    v_owner_rec.estates(1).people.EXTEND(3);
    
    FOR i IN v_owner_rec.estates(1).people.FIRST..v_owner_rec.estates(1).people.LAST LOOP
        CASE
            WHEN i < 2 THEN
                v_name  :=  'Lola';
                v_sex   :=  'F';
                v_age   :=  32;
                v_kin   :=  'Esposa';
            WHEN i > 2 THEN
                v_name  :=  'Carlitos';
                v_sex   :=  'M';
                v_age   :=  10;
                v_kin   :=  'Hijo';
            ELSE
                v_name  :=  'Lupita';
                v_sex   :=  'F';
                v_age   :=  12;
                v_kin   :=  'Hija';
        END CASE;

        v_owner_rec.estates(1).people(i).name  :=  v_name;
        v_owner_rec.estates(1).people(i).sex   :=  v_sex;
        v_owner_rec.estates(1).people(i).age   :=  v_age;
        v_owner_rec.estates(1).people(i).kin   :=  v_kin;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(LPAD('=',45,'=')||CHR(10)||'Reporte!!!'||CHR(10)||LPAD('=',45,'='));
    DBMS_OUTPUT.PUT_LINE('Usuario: '||v_owner_rec.owner);
    DBMS_OUTPUT.PUT_LINE('Cantidad de Viviendas: '||v_owner_rec.estates.COUNT);
    
    FOR i IN v_owner_rec.estates.FIRST..v_owner_rec.estates.LAST    LOOP
        DBMS_OUTPUT.PUT_LINE(LPAD('-',45,'-')||CHR(10)||'Detalles Vivienda!!!'||CHR(10)||LPAD('-',45,'-'));
        DBMS_OUTPUT.PUT_LINE('Tipo: '||v_owner_rec.estates(i).estate_type);
        DBMS_OUTPUT.PUT_LINE('Ubicación: '||v_owner_rec.estates(i).location);
        DBMS_OUTPUT.PUT_LINE('Cantidad de Habitaciones: '||v_owner_rec.estates(i).rooms.COUNT);
        DBMS_OUTPUT.PUT_LINE('Cantidad de Habitantes: '||v_owner_rec.estates(i).people.COUNT);
    END LOOP;
END;
----OUTPUT:
BONUS EXAMPLE
Read 545 times Last modified on Martes, 11 Agosto 2020 15:30

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.