Crear un campo autoincrementable en ORACLE


Para los que nos iniciamos en los Gestores de Bases de Datos con SQL Server, la transición a ORACLE resulta siempre más dura que cuando se realiza el camino inverso. Realizando una analogía simple, en mi caso podría decir que T-SQL es a PL/SQL lo que C# es a C++. Más complicación, más potencia… y un sistema más “tiquismiquis” que los desarrollados por Microsoft.
SQL Server proporciona una forma muy sencilla de crear campos autonuméricos, generalmente utilizados en los IDs de las claves primarias de las tablas. Para crear un campo autoincrementable, lo único que teníamos que hacer era editar las propiedades de la columna que queríamos convertir en autoincrementable, acceder a sus propiedades y seleccionar la opción [Especificación de Identidad] marcando la casilla (Identidad) e informando del número de inicio del índice (Inicialización de identidad) y el número de unidades que se incrementará cada vez que se intente insertar un registro (Incremento de identidad).

Oracle nos pone las cosas un poco más difíciles, obligándonos a realizar dos pasos para realizar el mismo procedimiento: crear una secuencia y crear un trigger.

Creando una secuencia

Una secuencia, como su propio nombre indica, es un artefacto que devolverá un número incrementado en las unidades que le digamos cada vez que se consulte. Crear una secuencia es sencillo. La sintaxis para ello es la siguiente:


CREATE SEQUENCE nombre_secuencia
[INCREMENT BY unidades_incremento]
[START WITH valor_inicial]
[MINVALUE valor_minimo] || [NOMINVALUE]
[MAXVALUE valor_maximo] || [NOMAXVALUE];

Existen otras opciones, como CYCLE, CACHE u ORDER, pero para el ejemplo básico que queremos mostrar, será más que suficiente. Crearemos una secuencia llamada “Secuencia_Usuarios”, que comience en 1, se incremente de una unidad en una unidad y que no posea valores mínimos ni máximos. La sentencia PL/SQL para tal tarea sería la siguiente:


CREATE SEQUENCE Secuencia_Usuario
INCREMENT BY 1
START WITH 1
NOMINVALUE
NOMAXVALUE;

Debemos fijarnos en un detalle: es aconsejable crear una secuencia distinta para cada autoincremento que queramos programar. Esto se debe a que, si utilizamos la misma secuencia para varias tablas, cada vez que se inserte en una de ellas, el valor para el incremento será común a todas las tablas que hagan uso de ella.

Creando un Trigger

Un Trigger o Disparador es una accion programada que se ejecuta cuando se da una determinada condición (generalmente, una inserción, una modificación o una eliminación). Crearemos un Trigger que inserte el siguiente valor de la secuencia en el lugar del ID autoincrementable, para lo cual, de forma coloquial, le diremos lo siguiente a nuestra base de datos:

Antes de insertar una fila en la tabla Usuario


almacena temporalmente la fila que vas a insertar,


guarda el siguiente valor de la secuencia en una variable


y rellena el campo IdUsuario con ese valor.

A continuación, procede con la inserción.

Lo cual, en PL/SQL, se traduciría en el siguiente código:


CREATE OR REPLACE TRIGGER Trigger_Usuario_Id
BEFORE INSERT ON Usuario
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE valorSecuencia NUMBER := 0;
BEGIN
SELECT Secuencia_Usuario.NEXTVAL INTO valorSecuencia FROM DUAL;
:NEW.IdUsuario := valorSecuencia;
END;

Lo que le estamos diciendo a Oracle es lo siguiente:

  • En primer lugar, queremos crear un Trigger que se llame “Trigger_Usuario_Id”.
  • En segundo lugar, la condición: queremos que se dispare ANTES de insertar una fila.
  • En la tercera línea, le estamos diciendo a ORACLE que, para cada fila insertada, nos referiremos a la nueva fila con el nombre de NEW (así, si la tabla Usuario tiene un campo “Nombre” y ese campo va a ser insertado en base de datos en la operación INSERT actual, nos referiremos a él como :NEW.Nombre).
  • A continuación declaramos una variable numérica, que inicializamos con el valor “0”.
  • En la siguiente línea comienza “realmente” la acción del Trigger (BEGIN): lo primero que hacemos es almacenar el siguiente valor de la secuencia (Secuencia_Usuario.NEXTVAL) en nuestra variable local (valorSecuencia).

Llegados a este punto, para los que estamos acostumbrados a T-SQL vemos algo extraño: además de asignar el valor a la variable, se añade una cláusula FROM que referencia a la tabla DUAL. ¿Qué es DUAL? DUAL es una tabla especial de ORACLE. A diferencia de T-SQL, PL/SQL no puede realizar sentencias SELECT sin su correspondiente FROM. La tabla DUAL es una tabla con una sola columna (llamada DUMMY) y un solo valor (generalmente, ‘X’). Así nos aseguramos de que recuperemos un solo valor, que es precisamente lo que queremos hacer. Por lo tanto, las siguientes sentencias T-SQL y PL/SQL serían equivalentes:


-- T-SQL:
SELECT @variable = 1;
SELECT @variable;

-- PL/SQL:
SELECT 1 INTO variable FROM DUAL;
SELECT variable FROM DUAL;

  • Tras este inciso, antes de finalizar la ejecución del Trigger con la cláusula END, le decimos a ORACLE que almacene en el campo IdUsuario de la fila actual (:NEW.IdUsuario) el contenido de la variable que acabamos de obtener.

Finalizada la secuencia y el trigger, cada vez que se inserte una fila en nuestra tabla, un nuevo valor se añadirá automáticamente a nuestro campo Id.

7 comments

  1. Excelente explicación, incluso detalles que otros no entran o que dan por sabidos los enseñas perfectamente. Un 10 en pedagogía y generosidad.

    Saludos!

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s