El tipo de dato colección del PL/SQL (PLSQL collection datatype)

En Oracle una “colección” PL/SQL (PLSQL collection) es un tipo de datos compuesto consistente en una matriz de una sola dimensión que está compuesta por uno o más elementos accesibles a través de un índice.

Los datos de tipo colección se utilizan en algunas de las más importantes funcionalidades del PL/SQL asociadas con la optimización del rendimiento de las bases de datos Oracle tales como:

• FORALL: dentro de los cuales se incluyen sentencias INSERT, UPDATE y DELETE que utilizan colecciones para cambiar múltiples registros de datos de una manera muy rápida.
• BULL COLLECT: sentencias SELECT que devuelven múltiples registros en una simple extracción, incrementando notablemente la velocidad de la consulta.
• Funciones de tipo tabla: funciones PLSQL que devuelven datos de tipo colección y que pueden utilizarse en la cláusula FROM de una sentencia SELECT.

Las colecciones también se pueden utilizar para trabajar en nuestros programas con listas de datos que no está almacenados en tablas de las base de datos Oracle.

Conceptos básicos y terminología del tipo de dato colección

Antes de empezar a explorar este tipo de dato del PLSQL, resulta conveniente conocer el vocabulario y la terminología que se utiliza al manejar las colecciones y que incluye los siguientes términos:

• Valor índice: indica la localización de un dato en una colección. Normalmente son números enteros pero para algunos tipos de colección pueden ser también cadenas de caracteres.
• Elementos: son los datos almacenados bajo un valor de índice específico. Los elementos en una colección son siempre del mismo tipo (todos ellos serán cadenas de caracteres, fechas o registros). Las colecciones PL/SQL están constituidas por datos homogéneos.
• Dispersión: una colección se dice dispersa si hay al menos un valor del índice entre el valor más bajo y el más alto del mismo que no está definido. Por ejemplo, una colección es dispersa si tiene un elemento asignado para el índice 1 y otro para el índice 5 pero no existe ningún elemento entre ambos índices. Lo contrario a una colección dispersa es una colección densa.
• Método: el método de una colección es un procedimiento o función que o proporciona información sobre la colección o cambia el contenido de la misma. Los métodos se asocian a las colecciones mediante el separador punto (utilizando la sintaxis del lenguaje orientado a objetos), un ejemplo sería mi_coleccion.PRIMER_METODO.

Tipos de colecciones

Las colecciones fueron introducidas por primera vez en la versión 7 de Oracle y desde entonces han ido evolucionando y mejorando a medida que han ido surgiendo nuevas versiones de la base de dato Oracle. Actualmente existen tres tipos de colecciones diferentes, cada una con sus propias características:

• Matriz asociativa: fue el primer tipo de colección disponible en PLSQL al que originalmente se le denominó “tabla PL/SQL” y sólo puede utilizarse en bloques PLSQL. Las matrices asociativas pueden ser dispersas o densas y pueden indexarse por enteros o cadenas de caracteres.
• Tablas anidadas: aparecieron en la versión 8 de la base de datos Oracle. Las tablas anidadas pueden utilizarse en bloques PL/SQL, en sentencias SQL y como tipo de dato para la columna de una tabla. Aunque las tablas anidadas pueden ser dispersas, casi siempre serán densas. Pueden indexarse exclusivamente por enteros. Es posible utilizar el operador MULTISET para realizar operaciones y comparaciones sobre el conjunto de todos los datos.
• Varray (matrices de tamaño variable): añadidas también en la versión 8 de la base de datos Oracle. Las varray o matrices de tamaño variable pueden utilizarse en bloques PL/SQL, en sentencias SQL y como tipo de dato para la columna de una tabla. Las varrays son siempre densas e indexadas por enteros. Cuando se define un varray se debe especificar el máximo número de elementos permitidos en la colección.

Viendo las definiciones anteriores es fácil concluir que raramente necesitaremos utilizar un varray, ya que será bastante poco frecuente conocer el máximo número de elementos que podrá llegar a tener nuestra colección.

También podemos decir que la matriz asociativa es el tipo de colección más comúnmente utilizado, pero las tablas anidadas, al permitir el empleo del operador MULTISET, están dotadas de una funcionalidad única y muy potente que puede simplificar notablemente el código PL/SQL a escribir cuando empleamos una colección.

Ejemplo de tabla anidada

Empezaré por mostraros un sencillo ejemplo en el que se introducen muchos aspectos de las colecciones que posteriormente exploraremos más extensamente en otros artículos que pienso escribir.

DECLARE
TYPE nombres_t IS TABLE OF VARCHAR2 (100);
empleados nombres_t := nombres_t ();
en_oficinas nombres_t := nombres_t ();
en_talleres nombres_t := nombres_t ();

BEGIN
empleados.EXTEND (4);
empleados (1) := 'Pepe';
empleados (2) := 'Elena';
empleados (3) := 'Carmen';
empleados (4) := 'Juan';

en_oficinas.EXTEND;
en_oficinas (en_oficinas.LAST) := 'Elena';
en_oficinas.EXTEND;
en_oficinas (en_oficinas.LAST) := 'Juan';

en_talleres := empleados MULTISET EXCEPT en_oficinas;

FOR l_row IN 1 .. en_talleres.COUNT
LOOP
DBMS_OUTPUT.put_line (en_talleres (l_row));
END LOOP;
END;

Al ejecutar el código ejemplo, obtendremos la siguiente salida:

Pepe
Carmen

En el código ejemplo podéis ver como hemos inicializado la colección empleados con cuatro registros. Luego hemos utilizado dos de esos registros para crear dos elementos en la colección en_oficinas. Posteriormente hemos inicializado la colección en_talleres con todos los elementos de la colección empleados que no están en la colección en_oficinas utilizando el operador MULTISET. Y finalmente hemos sacado por pantalla los elementos de la colección en_talleres.

Quedan todavía por mencionar muchos aspectos básicos sobre el manejo de colecciones (inicialización, actualización, iteración, borrado, etcétera), pero estos temas serán objeto de artículos posteriores.

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