Oracle se disfraza de SQL Server

El objetivo de este truco es simular en un servidor Oracle una técnica muy útil que SQL Server ofrece de forma directa. A la vez, repasaremos el uso de packages en PL/SQL y llegaremos a alguna conclusión sobre InterBase...

SQL SERVER EN LA CAPA INTERMEDIA

Tenemos una tabla en SQL Server, digamos que de CLIENTES. Algunas de las restantes tablas de nuestra base de datos tienen que hacer referencia a un registro de CLIENTES. Por lo tanto, introducimos en esa tabla una clave primaria sencilla, que funcionará en lo sucesivo como una especie de puntero a un cliente:

create table CLIENTES (
    IDCliente    int not null identity(1,1),
    /* ... más columnas ... */

    primary key  (IDCliente),
    /* ... más cláusulas ... */
)

En otro artículo de esta misma página he escrito algo sobre el atributo identity, aunque desde el punto de vista del BDE. Cuando a una columna se le asocia este atributo, no es necesario adjudicar valores explícitamente a la misma durante la inserción, pues la tabla mantiene un contador interno que incrementa cada vez que se añade un registro a la tabla. De hecho, lo que no podemos hacer es modificar manualmente los valores de este tipo de columnas.

Lo que ahora nos importa es saber cómo recuperar el valor utilizado como identidad justo después de insertar un nuevo registro en CLIENTES. No es tan trivial cómo puede parecer, sobre todo admitiendo la existencia de conexiones concurrentes que están trabajando sobre la misma tabla. Pondré un ejemplo de técnica incorrecta, que es la primera en venir a la mente del programador:

Recuperar el registro con la clave más alta (¡NO!): es fácil ver por qué no sirve. Si otra conexión inserta un registro después del nuestro, pero antes de que preguntemos por el registro con clave más alta, obtendremos la identidad asignada al registro de otra conexión.

Por suerte, SQL Server nos echa una mano implementando una especie de variable global, llamada @@identity, que devuelve el último valor de identidad creado por la conexión activa. Las restantes conexiones al servidor pueden hacer y deshacer lo que se les antoje, pero @@identity, fiel y leal, siempre le devolverá el último valor de identidad asignado a un registro creado por usted mismo.

¿Es tan importante conocer el último valor de una identidad? Si tenemos una aplicación en dos capas, quizás no sea demasiado importante, porque en la mayoría de los casos el middleware que utilicemos (BDE, ADO) se encargará de este asunto. Pero sí que es importante si estamos desarrollando una aplicación en tres capas. No voy a explicar los motivos en este momento, pero la importancia viene dada por la necesidad de propagar los valores generados en el servidor SQL al conjunto de datos cliente utilizado por la capa final del sistema.

¿Y SI FUESE ORACLE?

Hace poco tuve que migrar una aplicación desde SQL Server 7 a Oracle 8, y me encontré con toneladas de líneas de código que preguntaban por el valor de la clave del último registro insertado. Todas esas líneas llamaban a una función de mi módulo de datos, que ejecutaba la siguiente consulta de SQL Server:

select @@identity

¿Hay algo en Oracle que sea equivalente? Directamente no. En Oracle no se utilizan identidades, sino secuencias. Las secuencias se parecen más a los generadores de InterBase: funcionan como variables globales de 32 bits. La definición de mi tabla de clientes en Oracle cambió a esto:

create table CLIENTES (
    IDCliente    number(9) not null,
    /* ... más columnas ... */

    primary key  (IDCliente),
    /* ... más cláusulas ... */
);

Definí una secuencia mediante la siguiente instrucción:

create sequence IDsClientes
    increment by 1 start with 1;

Y forcé la asignación de un valor secuencial en un trigger asociado a la tabla de clientes:

create or replace trigger CLIENTES_BIR
    before insert on CLIENTES for each row
begin
    select IDsClientes.NextVal
    into   :new.IDCliente
    from   DUAL;
    IdentityPkg.LastIdentity := :new.IDCliente;
end;
/

Si no fuese por la última línea del trigger anterior, este sería uno más de los trucos que abundan en la Web para insertar el valor de una secuencia en un registro. Es decir, un truco "de manual". Pero aquí he añadido un package de PL/SQL a la ecuación:

create or replace package IdentityPkg as
  LastIdentity     number := -1000;
end IdentityPkg;
/

No quiero tampoco explicar todos los detalles relacionados con el uso de packages; por el momento, nos basta con saber que IdentityPkg.LastIdentity se comporta simplemente como una variable global ... ¡pero global solamente a nivel de conexión! Lo que quiero decir es que cada conexión a Oracle tendrá su propia "copia" de LastIdentity, y que poco importará la frenética actividad que otras conexiones realicen a su espalda. Siempre que pregunte por el valor de la variable, obtendrá el último valor secuencial utilizado por su conexión:

create or replace procedure UltimaIdentidad (
    ident out number ) is
begin
    ident := IdentityPkg.LastIdentity;
end;
/

¿Y QUE PASA CON INTERBASE?

Sencillamente, que InterBase por el momento no ofrece ningún recurso similar. Me refiero, por supuesto, al uso de estructuras de datos ligadas a una conexión. Por supuesto, existen los generadores para el problema de la creación de claves numéricas únicas. Pero si es necesario saber cuál es el valor de la clave de un registro recién insertado, tendremos que pedir explícitamente un valor al generador y utilizarlo explícitamente en la inserción ... lo que exige un mayor esfuerzo de planificación por parte nuestra.

En general, Oracle es el sistema mejor dotado en este sentido, pues los packages nos permiten implementar cualquier tipo de estructura ligada a una conexión. SQL Server no es tan potente, en el sentido de que no ofrece un mecanismo tan "general". Pero algunos problemas se resuelven con variable globales "de conexión", al estilo de @@identity (por ejemplo, el último error cometido). Y Transact SQL nos permite utilizar tablas temporales que se manejan por separado para cada conexión. Pero de las tablas temporales de SQL Server hablaremos otro día.