Oracle

Manejo de secuencias en PL/SQL

Por : Jhons_1101
2018-03-21
Tags :
En bases de datos Oracle, no existen las columnas de tabla autoincrementables, por ello se utilizan las secuencias. Las secuencias vienen a llenar ese vacío. Usado generalmente para crear valores enteros secuenciales únicos y asignárselos a campos numéricos; se utilizan generalmente para las claves primarias (PK) de las tablas, garantizando que sus valores no se repitan. Aunque se pueden usar en cualquier columna de tipo numérico o de texto.

Para utilizar la secuencia, crearemos una tabla de prueba para insertar un registro y comprobar que la secuencia anterior funciona correctamente.


CREATE TABLE CLIENTES (
    COD_CLIENTE NUMBER NOT NULL PRIMARY KEY, 
    NOM_CLIENTE VARCHAR2(100) UNIQUE NOT NULL, 
    TEL_CLIENTE NUMBER(15), 
    FEC_NACIMI DATE
)



Analicemos la sintaxis de la secuencia para poder generar una y por medio de ella hacer una inserción a la tabla CLIENTES.

-- Sintaxis de una secuencia...
CREATE SEQUENCE nombresecuencia
START WITH valorentero
INCREMENT BY valorentero
MAXVALUE valorentero
MINVALUE valorentero
CYCLE | NOCYCLE;

  • La cláusula "start with" indica el valor desde el cual comenzará la generación de números secuenciales. Si no se especifica, se inicia con el valor que indique "minvalue".
  • La cláusula "increment by" especifica el incremento, es decir, la diferencia entre los números de la secuencia; debe ser un valor numérico entero positivo o negativo diferente de 0. Si no se indica, por defecto es 1.
  • "maxvalue" define el valor máximo para la secuencia. Si se omite, por defecto es 99999999999999999999999999.
  • "minvalue" establece el valor mínimo de la secuencia. Si se omite será 1.
  • La cláusula "cycle" indica que, cuando la secuencia llegue a máximo valor (valor de "maxvalue") se reiniciará comenzando de nuevo con el valor mínimo ("minvalue"), es decir, la secuencia vuelve a utilizar los números. Si se omite, por defecto la secuencia se crea "nocycle".
Si no se especifica ninguna cláusula, excepto el nombre de la secuencia, por defecto, comenzará en 1, se incrementará en 1, el mínimo valor será 1, el máximo será 999999999999999999999999999 y "nocycle".
Fuente: Extraido de tutorialesprogramacionya...


Si bien, las secuencias son independientes de las tablas, se utilizarán generalmente para una tabla específica, por lo tanto, es conveniente darle un nombre que referencie a la misma..

Como estándar para nombrar las secuencia, se recomienda usar el nombre de la tabla a la cual prestará el servicio de secuencia seguido del prefijo "SQ"


-- Secuencia sencilla, definimos sólo el nombre y la cantidad que incrementa
-- cada vez que se ejecute la secuencia, Así como el valor de inicio...

CREATE SEQUENCE clienteSQ
INCREMENT BY 1
START WITH 1;


secuencia creada clientes
Haciendo la lectura de la secuencia creada anteriormente, podemos ver que inicia en 1, aumentará en 1, su valor máximo de secuencia será "Max Value 9999999999999999999999999999" y que no fue declarada cíclica.
Que pasará entonces si una secuencía supera el límite máximo de secuencia parametrizado..?
Inmediatamente mostrará un mensaje de error... ORA-08004.

la compuerta estudiantes colapso

compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url

Como usar una secuencia..?


Podemos acceder a las secuencias por medio de una consulta DUMMY, empleando la palabra reservada "select". Utilizamos las pseudocolumnas "currval" y "nextval" para recuperar el valor actual y el siguiente de la secuencia respectivamente. Estas pseudocolumnas pueden incluirse en el "from" de una consulta a la tabla "dual".


--Para recuperar el valor actual de una secuencia usamos currval.
SELECT estudianteSQ.CURRVAL FROM DUAL;

-- Para consumir una posición de la secuencia usamos nextval.
SELECT estudianteSQ.NEXTVAL FROM DUAL;


Para ver todas las secuencias de la base de datos actual realizamos la siguiente consulta:
select *from all_sequences;

Esta instrucción nos muestra el propietario de la secuencia (SYS, SYSTEM, etc), el nombre de la secuencia, los valores mínimo y máximo parametrizado, el valor de incremento y si es cíclica o no y el siguiente valor que devolverá la secuencia cuando sea consumida. Digamos que es el resumen ordenado de las propiedades de las secuencia ceeadas en la DB.


Crear una secuencia decreciente

Para crear una secuencia decremental el parámetro INCREMENT BY tendrá valor -1 y el parámetro START WITH coincidirá con el parámetro MAXVALUE.

CREATE SEQUENCE decrementarSQ 
INCREMENT BY -1 
START WITH 100 MAXVALUE 100;



Al consultar la secuencia decrementarSQ, veremos que el contador empieza a bajar.. esto es un práctico cuando se requiere validar por ejemplo, si un límite fue cumplido..



SELECT decrementarSQ.nextval FROM DUAL;
-- salida interacción 1: 100 
-- salida interacción 2: 99
-- salida interacción 3: 98
-- etc...


Uso de las secuencia en Inserts y Updates

También podemos usar las secuencia en un insert o update, ya sea manual o por medio de %RECORD (lo vimos en el capítulo anterior).
Veamos un par de ejemplos sencillos de su uso...


-- Insert hecho a mano
INSERT INTO CLIENTES VALUES (clienteSQ.nextval,'Cliente número 1', 21345678, SYSDATE);
COMMIT;


-- Update hecho a mano, Miremos la imagen del antes y despues de correr el UPDATE..
UPDATE CLIENTES SET COD_CLIENTE = clienteSQ.nextval WHERE COD_CLIENTE = 1;
COMMIT;


-- Insert hecho por %RECORD
DECLARE
   rowCliente CLIENTES%ROWTYPE;
BEGIN
  rowCliente.COD_CLIENTE  := clienteSQ.nextval;
  rowCliente.NOM_CLIENTE  := 'CLIENTE EJEMPLO 4';
  rowCliente.TEL_CLIENTE  := 23544;
  rowCliente.FEC_NACIMI   := SYSDATE;
  INSERT INTO CLIENTES VALUES rowCliente;
  COMMIT;
END;


-- Update hecho por %RECORD
DECLARE
  rowCliente CLIENTES%ROWTYPE;
BEGIN
  rowCliente.COD_CLIENTE  := clienteSQ.currval;
  rowCliente.NOM_CLIENTE  := 'CLIENTE EJEMPLO 1';
  rowCliente.TEL_CLIENTE  := 1234567;
  rowCliente.FEC_NACIMI   := SYSDATE;
  UPDATE CLIENTES SET ROW = rowCliente WHERE COD_CLIENTE = 3;
  COMMIT;
END;


Antes y despues de update a mano con secuencia

Para eliminar una secuencia empleamos "drop sequence".

DROP SEQUENCE nombresecuencia;
Si la secuencia no existe aparecerá un mensaje indicando el error.. ORA-02289.

la secuencia no existe





Si este contenido te fue útil, no olvides compartirlo en redes sociales, Considéralo. Puede ser la manera de agradecer!


Agrega tu comentario...

Puedes utilizar etiquetas <pre></pre>, <p></p>, <div></div>, + (Nombre usuario, para responderle a alguien)

xHP1LWo





Este post no tiene comentarios, sé el primero en hacerlo
Esta entrada no cuenta con imágenes adjuntas

Unete al grupo de whatsApp +57 316 392 6456

Sigue el grupo en facebook

Siguenos.....