MAGIC PL/SQL ORACLE: Ejemplos de Colecciones

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.

SET SERVEROUTPUT ON

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 y 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:

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 CURSOR, Oracle 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;

SHOW ERROR

/*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

d.department_id = v_filter_number

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;

SHOW ERROR

/*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:

SET SERVEROUTPUT ON

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:

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:

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:

MAGIC PL/SQL ORACLE

lunes, 9 de enero de 2017

Ejemplos de Colecciones

Escribiendo Código Flexible en Oracle Forms

Buscar este blog