Para poder implementar relaciones 1:N, Oracle permite definir tipos colección. Un dato de tipo colección está formado por un número indefinido de elementos, todos del mismo tipo. De esta manera, es posible almacenar en un atributo un conjunto de tuplas en forma de array (VARRAY), o en forma de tabla anidada.

Al igual que los tipo objeto, los tipo colección también tienen por defecto unas funciones constructoras de colecciones cuyo nombre coincide con el del tipo. Los argumentos de  entrada de estas funciones son el conjunto de elementos que forman la colección separados por comas y entre paréntesis, y el resultado es un valor del tipo colección.

En Oracle es posible diferenciar entre un valor nulo y una colección vacía. Para construir una colección sin elementos se puede utilizar la función constructora del tipo seguida por dos paréntesis sin elementos dentro.

El tipo VARRAY

Un array es un conjunto ordenado de elementos del mismo tipo. Cada elemento tiene asociado un índice que indica su posición dentro del array. Oracle permite que los VARRAY sean de longitud variable, aunque es necesario especificar un tamaño máximo cuando se declara el tipo VARRAY. Las siguientes declaraciones crean un tipo para una lista ordenada de precios, y un valor para dicho tipo.

CREATE TYPE precios AS VARRAY(10) OF NUMBER(12);
precios('35', '342', '3970');

Se puede utilizar el tipo VARRAY para:

• Definir el tipo de dato de una columna de una tabla relacional.
• Definir el tipo de dato de un atributo de un tipo de objeto.
• Para definir una variable PL/SQL, un parámetro o el tipo que devuelve una función.

Cuando se declara un tipo VARRAY no se produce ninguna reserva de espacio. Si el espacio que requiere lo permite, se almacena junto con el resto de columnas de su tabla, pero si es demasiado largo (más de 4000 bytes) se almacena aparte de la tabla como un BLOB.

En el siguiente ejemplo, se define un tipo de datos para almacenar una lista ordenada de teléfonos: el tipo list (ya que en el tipo set no existe orden). Este tipo se utiliza después para asignárselo a un atributo del tipo de objeto cliente_t.

CREATE TYPE lista_tel_t ASVARRAY(10) OF VARCHAR2(20) ;
CREATE TYPE cliente_t AS OBJECT (clinum NUMBER,clinomb VARCHAR2(200),
                                 direccion direccion_t,lista_tel lista_tel_t );

La principal limitación del tipo VARRAY es que en las consultas es imposible poner condiciones sobre los elementos almacenados dentro. Desde una consulta SQL, los valores de un VARRAY solamente pueden ser accedidos y recuperados como un bloque. Es decir, no se puede acceder individualmente a los elementos de un VARRAY. Sin embargo, desde un programa PL/SQL si que es posible definir un bucle que itere sobre los elementos de un VARRAY.3. Se define el tipo objeto ordenes_t y su atributo pedido almacena una tabla anidada del tipo lineas_pedido_t.

Tablas anidadas

Una tabla anidada es un conjunto de elementos del mismo tipo sin ningún orden predefinido. Estas tablas solamente pueden tener una columna que puede ser de un tipo de datos básico de Oracle, o de un tipo de objeto definido por el usuario. En este último caso, la tabla anidada también puede ser considerada como una tabla con tantas columnas como atributos tenga el tipo de objeto. 

El siguiente ejemplo declara una tabla que después será anidada en el tipo ordenes_t. Los pasos de todo el diseño son los siguientes:

1. Se define el tipo de objeto linea_t para las filas de la tabla anidada.

CREATE TYPE linea_t AS OBJECT (
linum NUMBER,
item VARCHAR2(30),
cantidad NUMBER,
descuento NUMBER(6,2));

2. Se define el tipo colección tabla lineas_pedido_t para después anidarla.

CREATE TYPE lineas_pedido_t AS TABLE OF linea_t ;

Esta definición permite utilizar el tipo colección lineas_pedido_t para:

3. Se define el tipo objeto ordenes_t y su atributo pedido almacena una tabla anidada del tipo lineas_pedido_t.

CREATE TYPE ordenes_t AS OBJECT (
ordnum NUMBER,
cliente REF cliente_t,
fechpedido DATE,
fechentrega DATE,
pedido lineas_pedido_t,
direcentrega direccion_t) ;

4. Se define la tabla de objetos ordenes_tab y se especifica la tabla anidada del tipo lineas_pedido_t.

CREATE TABLE ordenes_tab OF ordenes_t
(ordnum PRIMARY KEY,
SCOPE FOR (cliente) IS clientes_tab)
NESTED TABLE pedido STORE AS pedidos_tab) ;

Este último paso es necesario realizarlo porque la declaración de una tabla anidada no reserva ningún espacio para su almacenamiento. Lo que se hace es indicar en qué tabla (pedidos_tab) se deben almacenar todas las líneas de pedido que se representen en el atributo pedido de cualquier objeto de la tabla ordenes_tab. Es decir, todas las líneas de pedido de todas las ordenes se almacenan externamente a la tabla de ordenes, en otra tabla especial. Para relacionar las tuplas de una tabla anidada con la tupla a la que pertenecen, se utiliza una columna oculta que aparece en la tabla anidada por defecto. Todas las tuplas de una tabla anidada que pertenecen a la misma tupla tienen el mismo valor en esta columna (NESTED_TABLE_ID).

A diferencia de los VARRAY, los elementos de las tablas anidadas (NESTED_TABLE) sí pueden ser accedidos individualmente, y es posible poner condiciones de recuperación sobre ellos.  

Mas adelante,  veremos, una forma conveniente de acceder individualmente a los elementos de una tabla anidada mediante un cursor anidado. Además, las tablas anidadas pueden estar indexadas.