Anuncios en tutorial de programación PLSQL

miércoles, 17 de junio de 2020

Recorrer colecciones PL/SQL (métodos y funciones de las colecciones)

Una operación que se debe realizar muy comúnmente con las colecciones PLSQL es tener que recorrerse todos los elementos de la misma. La razones para tener que recorrerse totalmente los datos de una colección pueden se múltiples, desde mostrar la información que almacenan hasta buscar un elemento específico o ejecutar una sentencia DML (de manipulación de datos dentro de la base de datos Oracle) utilizando los datos almacenados en los diferentes elementos de la colección.

Métodos en las colecciones PLSQL

El tipo de código PL/SQL que se debe utilizar para operar y recorrer una colección viene determinado por el tipo de colección con la que estemos trabajando y la forma en que dicha colección fue inicializada con datos. Lo normal es elegir entre utilizar un bucle WHILE o utilizar un bucle numérico FOR.

Excepción NO_DATA_FOUND en colecciones PL/SQL


Uno de los primeros conceptos básicos a tener en cuenta cuando se trabaja con colecciones es que La base de datos Oracle generará un error NO_DATA_FOUND cuando intentemos leer un elemento de una colección cuyo valor de índice no haya sido definido todavía. Por ejemplo, si ejecutáis el siguiente código PLSQL veréis que se genera el mencionado error:
DECLARE
  TYPE coleccion_numeros IS TABLE OF NUMBER
  INDEX BY PLS_INTEGER;
  l_col coleccion_numeros;
BEGIN
  DBMS_OUTPUT.PUT_LINE (l_col(5));
END;

Cuándo utilizar un bucle numérico FOR


Los bucles numéricos FOR se deben utilizar cuando la colección ha sido poblada en su totalidad, es decir, cuando a todos los elementos desde el índice inicial al final se les ha asignado de forma secuencial un valor. El bucle numérico FOR también está pensado para los casos en los que inexcusablemente debemos recorrernos todos los elementos de la colección.

Cuando se sabe que la colección está completamente rellena y que no debería generarse la excepción NO_DATA_FOUND por ningún motivo, utilizar el bucle FOR es la forma más sencilla de recorrerse dicha colección. Veamos un ejemplo:
CREATE OR REPLACE PROCEDURE mostrar_elementos 
  (elementos IN DBMS_UTILITY.maxname_array)
IS
BEGIN
  FOR npal IN elementos.FIRST .. elementos.LAST
  LOOP
    DBMS_OUTPUT.put_line (elementos (npal));
  END LOOP;
END; 

El procedimiento PLSQL anterior lo que hace es mostrar las cadenas de caracteres almacenadas en una colección cuyo tipo está definido en el paquete estándar DBMS_UTILITY. El procedimiento mostrar_elementos realiza una llamada a los métodos FIRST y LAST, que devuelven el valor del primer y último índice de la colección respectivamente.

A continuación os dejo un ejemplo de utilización de dicho procedimiento en el que se inicializan cuatro elementos de la colección para luego mostrarlos (como podéis observar no es necesario que el primer índice tome el valor de 1).
DECLARE
  l_elementos DBMS_UTILITY.maxname_array;
BEGIN
  l_elementos (8) := 'árbol';
  l_elementos (9) := 'planta';
  l_elementos (10) := 'flor';
  l_elementos (11) := 'hoja';
  mostrar_elementos (l_elementos);
END;

Salida:
  árbol
  planta
  flor
  hoja

Cuándo utilizar un bucle WHILE


Los bucles WHILE son adecuados cuando los datos de la colección PLSQL son dispersos y poco densos o cuando podríamos necesitar parar la ejecución del bucle antes de haber recorrido todo los elementos de la colección.

A continuación podéis ver un procedimiento PLSQL que funciona de forma similar al anterior que empleaba la sentencia FOR, pero en este caso se utiliza la sentencia WHILE:
CREATE OR REPLACE PROCEDURE ver_elementos 
  (elementos IN DBMS_UTILITY.maxname_array)
IS
  l_npal PLS_INTEGER := elementos.FIRST;
BEGIN
  WHILE (l_npal IS NOT NULL)
  LOOP
    DBMS_OUTPUT.put_line (elementos (l_npal));
    l_npal := elementos.NEXT (l_npal);
  END LOOP;
END; 

Como podéis ver, en el nuevo procedimiento ver_elementos, la variable l_npal se inicializa con el valor del menor índice definido. Conviene saber que si la colección PL/SQL está vacía, los métodos FIRST y LAST van a devolver el valor NULL (podéis ver el listado completo de métodos de las colecciones en el enlace anterior). En nuestro procedimiento utilizamos esta circunstancia y por eso el bucle WHILE termina cuando l_npal toma el valor NULL.

Dentro del bucle lo que hacemos es mostrar la información del elemento siendo procesado y posteriormente se llama al método NEXT que devuelve el siguiente índice definido mayor que l_npal (la función NEXT devuelve el valor NULL si no hay ningún índice superior definido).

Por lo tanto el procedimiento ver_elementos muestra todos los elementos de la colección aunque no se hayan definido de forma secuencial y sin que se genere la excepción NO_DATA_FOUND. Como en otras ocasiones veámoslo con un ejemplo:
DECLARE
  l_elementos DBMS_UTILITY.maxname_array;
BEGIN
  l_elementos (-5) := 'árbol';
  l_elementos (0) := 'planta';
  l_elementos (10) := 'flor';
  l_elementos (1000) := 'hoja';
  ver_elementos (l_elementos);
END;

Salida:
  árbol
  planta
  flor
  hoja 

También es posible recorrer una colección en sentido inverso, es decir, empezando por el último registro (LAST) y terminando por el primero. Para poder hacerlo hay que usar el método o función PRIOR. Podéis ver su funcionamiento en el siguiente ejemplo:
CREATE OR REPLACE PROCEDURE al_reves_elementos  
  (elementos IN DBMS_UTILITY.maxname_array)
IS
  l_npal PLS_INTEGER := elementos.LAST;
BEGIN
  WHILE (l_npal IS NOT NULL)
  LOOP
    DBMS_OUTPUT.put_line (elementos (l_npal));
    l_npal := elementos.PRIOR (l_npal);
  END LOOP;
END;

Y siguiendo con el ejemplo, la salida arrojada por el procedimiento al_reves_elementos sería:

DECLARE
  l_elementos DBMS_UTILITY.maxname_array;
BEGIN
  l_elementos (-5) := 'árbol';
  l_elementos (0) := 'planta';
  l_elementos (10) := 'flor';
  l_elementos (1000) := 'hoja';
  al_reves_elementos (l_elementos);
END;

Salida:
  hoja
  flor
  planta
  árbol

Borrado de los elementos de una colección PL/SQL


El lenguaje PLSQL dispone también del método DELETE con el que es posible borrar uno, algunos o todos los elementos de una colección.

Si queremos borrar todos los elementos de una colección simplemente hay que utilizar el método DELETE sin parámetro alguno. Esta forma de utilizar la función DELETE funciona con los tres tipos de colección existentes (tabla anidada (nested table), matriz asociativa (associative array) y varray).
elementos.DELETE;

Para borrar un solo elemento de la colección hay que pasar a la función DELETE el valor del índice del elemento que queremos borrar. Este tipo de borrado solo funciona en colecciones de tipo tabla anidada o de tipo matriz asociativa.
elementos.DELETE (10);
elementos.DELETE (elementos.LAST);

Para borrar los elementos de una colección situados entre dos valores de índice, basta con pasar a la función ambos valores. Igualmente que para el caso anterior, este tipo de borrado solo funciona para colecciones de tipo tabla anidada o de tipo matriz asociativa.
elementos.DELETE (5,12);

Al utilizar el método DELETE, la base de datos no generará ningún error aunque en los parámetros que le pasamos alguno de los índices no esté definido.

Por otro lado, también es posible utilizar el método TRIM para borrar elementos desde el final de una colección. Este método solo funciona con las colecciones de tipo varray o de tipo tabla anidada.

Para borrar el último elemento de la colección utilizaremos:
elementos.TRIM;

Para borrar los “n” últimos:
elementos.TRIM(n);

Para concluir solo me quedaría indicar que, en mi opinión, una de las mayores ventajas que ofrece el PLSQL frente a otros lenguajes de programación de bases de datos es el hecho de que permite el uso de colecciones. Con este artículo y el anterior en el que hablé sobre la inicialización y asignación de valores a colecciones PL/SQL podréis trabajar con ellas sin problemas, pero todavía es conveniente saber que existen algunas funciones avanzadas que no he mencionado, como la utilización de cadenas de caracteres indexadas o el uso de colecciones anidadas que serán objeto de artículos futuros.

Artículos relacionados: Cláusula BULK COLLECT para mejorar el rendimiento

0 comentarios: