Tablas PL/SQL [Parte 1]

Para aquellos que programan en algún lenguaje (lo que estoy diciendo es una obviedad pero es solo por dar un ejemplo didáctico), apuesto a que trabajaron con vectores, también llamados arrays, matrices, n-uplas, etc.  Que no son más que sectores de almacenamiento contiguo para datos de un mismo tipo.

Seguramente se habrán dado cuenta, que no es más que un concepto adoptado del análisis vectorial. Para los que no tuvieron “el placer” de toparse con materias como Análisis Matemático, Álgebra y Geometría Analítica, Física, etc. les comento que el concepto de vector, es puramente Matemático.

Así como estos casos tenemos unos cuantos, y ya que estamos hablando de Bases de Datos, tenemos el claro ejemplo del álgebra de conjuntos. Les aclaro que yo era uno de los que, durante las clases de Matemática, hacia la típica pregunta “Y esto que aplicación tiene??”. Era obvio que no iba a obtener ninguna respuesta de un profesor que se dedicaba a enseñar Matemática en la carrera de Ingeniería. Así que con el tiempo me fui dando cuenta solo (Si bien nunca me pidieron que desarrolle un programa para calcular el flujo  a través de un campo eléctrico con el teorema de Gauss, y que no me pondría a hacer salvo que me presenten un maletín con 200 millones de dólares).

Llendo a la temática de este post, los vectores, así como los podrán utilizar en C, C++, Pascal, etc. También existen en el lenguaje de programación que utiliza Oracle, que es el PL/SQL.

Para comenzar nos vamos a remontar a las tablas PL/SQL. Estas tablas son básicamente un array, y tienen dos componentes: una clave primaria del tipo BINARY_INTEGER para indexarla y otra columna que permite guardar los escalares o registros. Cabe destacar que estas estructuras son dinámicas.

Para manejarlas, lo primero que tenemos que hacer es declarar el tipo de dato.

TYPE nombre_tipo_tabla IS TABLE OF
{tipo_columna | variable%TYPE | tabla.columna%TYPE}[NOT NULL]
INDEX BY BINARY_INTEGER ;

Lo que acabamos de declarar es un tipo tabla, que va a ser el tipo de nuestra variable. Por lo que luego, nuestra declaración no varía en nada a la declaración formal de cualquier variable en PL/SQL:

nombre_var nombre_tipo_tabla;

Si queremos referenciar a un elemento de la tabla, sería como si de un vector se tratase:

Pl/sql_nombre_tabla(valor_primary_key);

El rango de binary integer es –2147483647.. 2147483647, por lo tanto el índice puede ser negativo, lo cual indica que el índice del primer valor no tiene que ser necesariamente el uno.

También es posible declarar los famosos “Tipo Registro”, conocidos como struct en C, como record en Pascal. Y este tipo registro puede ser tranquilamente el tipo de dato de nuestra tabla PL/SQL. Y el concepto es prácticamente el mismo que en los lenguajes de programación mencionados, o sea, tendría mi tipo vector, cuyo tipo de dato seria de un tipo de registro que haya definido. Por lo que en cada posición del vector voy a poder guardar datos de ese tipo registro. Qué ventaja me da esto, que con las posibilidades que me ofrecen los tipos record, podría guardar varios datos, de distinto tipo dentro de las posiciones de un vector. Análogamente para PL/SQL.

Con la llegada de Oracle 8 PL/SQL añade dos tipos de colecciones, las tablas anidadas y los VARRAY. Cada uno con sus atributos y métodos (muy similar a lo que es un objeto en cualquier lenguaje de programación orientada a objetos).

En sí, las tablas anidadas son muy similares a las tablas PLSQL que acabamos de ver. Con la diferencia de que estas incluyen métodos de colección adicionales, que las tablas PL/SQL no tenían, ni tienen. Por otro lado también se abre la posibilidad de guardar una tabla anidad, dentro de una tabla en la base de datos. Digamos, puedo tener una tabla, que posea un campo cuyo dominio sea el mismo que mi tipo tabla, y dentro de ella tranquilamente podría persistir ese dato.

Su declaración es muy simple:

TYPE nombre_tabla IS TABLE OF tipo_tabla [NOT NULL];

Como podrán observar desaparece la clausula para la indexación, que tenían las anteriores, que es BINARY_INTEGER.

Una vez que creamos el tipo, la declaración de nuestra variable es análoga a cualquier otra declaración. Y por supuesto que en la declaración de nuestra variable, podemos inicializarla, como si se tratase de una variable “varchar”, por mencionar un ejemplo, con la diferencia de que para el caso de las nested tables, su inicialización se realiza a través del constructor de la misma.

Un simple ejemplo seria:

DECLARE

    TYPE tabla_numero IS TABLE OF NUMBER;
    var_tabla_1 tabla_numero := tabla_numero(0);
    var_tabla_2 tabla_numero := tabla_numero(1, 2, 3, 4);
    var_tabla_3 tabla_numero := tabla_numero();
    var_tabla_4 tabla_numero;

BEGIN

    var_tabla_1(1):= 123;
    DBMS_OUTPUT.PUT_LINE('Valor 1 de var_1: ' || var_tabla_1(1));
    DBMS_OUTPUT.PUT_LINE('Valor 1 de var_2: ' || var_tabla_2(1));

    IF var_tabla_3 IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var_tabla_3 SI es NULL');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var_tabla_3 NO es NULL');
    END IF;

    IF var_tabla_4 IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var_tabla_4 SI es NULL');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var_tabla_4 NO es NULL');
    END IF;

END;
/

Por supuesto que la inicialización no es obligatoria, y si la obviamos tendríamos una tabla sin elementos.

Cuando se inicializa una tabla utilizando el constructor, los elementos de la tabla se numeran secuencialmente desde 1 hasta el número de elementos especificado en la llamada al constructor. En caso de borrado en una tabla anidada de la base de datos, las claves se reenumeran para seguir siendo secuenciales.

Un ejemplo:

DECLARE

    TYPE tabla_numero IS TABLE OF NUMBER;
    var_tabla_2 tabla_numero := tabla_numero(10, 20, 30, 40);

BEGIN

    DBMS_OUTPUT.PUT_LINE('Nro elem de var_tabla_2: ' || var_tabla_2.count);
    DBMS_OUTPUT.PUT_LINE('Primer elem: ' || var_tabla_2.first);
    DBMS_OUTPUT.PUT_LINE('Último elem: ' || var_tabla_2.last);

END;
/

La salida será:

Nro elem de var_tabla_2: 4

Primer elem: 1

Último elem: 4

Aunque las tablas no tienen un tamaño fijo, no se puede asignar un valor a un elemento que todavía no existe. Para poder añadir elementos hay que utilizar el método EXTEND (que ya lo veremos en otro momento).

Ejemplo:

DECLARE

    TYPE tabla_numero IS TABLE OF NUMBER;
    var_tabla_2 tabla_numero := tabla_numero(10, 20, 30, 40);

BEGIN

    var_tabla_2 (1) := 50;
    DBMS_OUTPUT.PUT_LINE('Primer elemento de var_tabla_2: ' || var_tabla_2(1) );

END;
/

La salida es:

Primer elemento de var_tabla_2: 50

Procedimiento PL/SQL terminado con éxito.

Pero si pongo:

DECLARE

    TYPE tabla_numero IS TABLE OF NUMBER;
    var_tabla_2 tabla_numero := tabla_numero(10, 20, 30, 40);

BEGIN

    var_tabla_2 (1) := 50;
    DBMS_OUTPUT.PUT_LINE('Primer elemento de var_tabla_2: ' || var_tabla_2(1) );
    Var_tabla_2 (5) := 60;
    DBMS_OUTPUT.PUT_LINE('Quinto elemento de var_tabla_2: ' || var_tabla_2(5));

END;
/

Salida:

Primer elemento de var_tabla_2: 50

DECLARE

*

ERROR en línea 1:

ORA-06533: Subíndice mayor que el recuento

ORA-06512: en línea 7

Para la próxima parte, concluiremos con este tema hablando sobre algunos aspectos adicionales pero no menos importantes sobre las nested tables.