DDL - Seqüències

MariaDB té l'atribut AUTO_INCREMENT que permet assignar valors seqüencials en un camp enter i Clau Primària de qualsevol taula.

Tot i això, igual que en altres SGBDR també existeixen les sequencies.

Una seqüència és un objecte que genera una seqüència de valors numèrics, tal com especifica la sentència CREATE SEQUENCE. Les seqüències són una alternativa a AUTO_INCREMENT quan es vol més control sobre com es generen els números de seqüència.

Com que una SEQÜÈNCIA guarda els valors a la memòria cau, de vegades pot ser més ràpid. A més, podem accedir al darrer valor generat per totes les seqüències utilitzades; no està subjecte a limitacions de LAST_INSERT_ID( ).

La sintaxis de la comanda CREATE SEQUENCE és la següent:

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS]
       sequence_name
       [ INCREMENT [ BY | = ] increment ]
       [ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
       [ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
       [ START [ WITH | = ] start ]
       [ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE]
       [table_options]

L'ordre dels paràmetres pot ser qualsevol, excepte el table_options que ha de ser el darrer.

Opció	Valor per defecte	Descripció
INCREMENT	1	Increment a utilitzar per als valors. Pot ser negatiu.
MINVALUE	1 si INCREMENT > 0 -9223372036854775807 (-2⁶³ - 1) si INCREMENT < 0	Valor mínim per la seqüència
MAXVALUE	9223372036854775806 (2⁶³ - 2) si INCREMENT > 0 -1 si INCREMENT < 0	Valor màxim per la seqüència
START	MINVALUE si INCREMENT > 0 MAX_VALUE si INCREMENT< 0	Primer valor que generarà la seqüència
CACHE	1000	Nombre de valors que s'emmagatzemaran a la memòria cau.
CYCLE	NOCYCLE	Si s'usa, la seqüència tornarà a començar quan hagi generat tots els valors possibles.

Exemples de seqüències

Si volem crear nombres pels corredors d’una cursa atlètica podem crear:

CREATE SEQUENCE cursa_id_seq
       INCREMENT BY 1
       START WITH 1
       MAXVALUE 50000
       NOCACHE
       NOCYCLE;

No podem utilitzar la opció CYCLE si la seqüència ha de generar PK."

El diccionari de dades

Per saber les seqüències que hem creat cal consultar la taula TABLES de la base information_schema i filtrar pel camp TABLE_TYPE que tingui de valor SEQUENCE.

SELECT table_schema, table_name, table_type
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'SEQUENCE'

Cal saber que el camp TABLE_SCHEMA fa referència a la base de dades

Treball amb seqüències

Una vegada hem definit una seqüència, cal anar obtenint els diferents valors i poder consultar el darrer valor obtingut. Per això utilitzarem:

Una de les dues opcions següents pel proper valor

NEXT VALUE FOR sequence
NEXTVAL(sequence_name)

Una de les dues opcions següents pel darrer valor donat

PREVIOUS VALUE FOR sequence_name
LASTVAL(sequence_name)

Exemple

SELECT previous value for cursa_id_seq;
SELECT LASTVAL(cursa_id_seq);

Cada vegada que utilitzem la funció NEXT VALUE FOR sequence ens donarà un valor nou per la seqüència i incrementarà internament el valor d’aquesta aquest.

Aquesta funció solem utilitzar-la directament en les instruccions INSERT.

Cada vegada que utilitzem la funció PREVIOUS VALUE FOR sequence_name ens retorna el darrer valor que s’ha assignat a la seqüència.

Aquesta funció solem utilitzar-la directament en instruccions SELECT per saber el darrer valor que s’ha generat. També podem utilitzar- lo en instruccions INSERT en taules filles.

Exemple de seqüències

Seqüència cíclica

CREATE OR REPLACE SEQUENCE cursa_id_seq
       INCREMENT BY 1
       START WITH 1 
       MAXVALUE 5
       NOCACHE
       CYCLE;
SELECT LASTVAL(cursa_id_seq); -- Retorna NULL
SELECT NEXTVAL(cursa_id_seq); -- Retorna 1
SELECT NEXTVAL(cursa_id_seq); -- Retorna 2
SELECT NEXTVAL(cursa_id_seq); -- Retorna 3
SELECT LASTVAL(cursa_id_seq); -- Retorna 3
SELECT PREVIOUS VALUE FOR cursa_id_seq; -- Retorna 3
SELECT NEXT VALUE FOR cursa_id_seq; -- Retorna 4
SELECT PREVIOUS VALUE FOR cursa_id_seq; -- Retorna 4
SELECT NEXT VALUE FOR cursa_id_seq; -- Retorna 5
SELECT NEXT VALUE FOR cursa_id_seq; -- Retorna 1
SELECT PREVIOUS VALUE FOR cursa_id_seq; -- Retorna 1

Exemple cursa

CREATE TABLE corredors(
       id INT PRIMARY KEY,
       nom VARCHAR(30),
       cognoms VARCHAR(30));
CREATE OR REPLACE SEQUENCE corredors_id_seq
       MAXVALUE 50000 NOCACHE;
INSERT INTO corredors VALUES
       (NEXTVAL(corredors_id_seq), 'Joana', 'Bosch'),
       (NEXTVAL(corredors_id_seq), 'Manel', 'Frigola');
SELECT * FROM corredors;
SELECT LASTVAL(corredors_id_seq);

Pèrdua de valors seqüencials

És possilbe que, al final, en una taula quedin nombres sense assignar, això pot ser degut a varis factors:

Una errada en el sistema i per tant haguem perdut aquells valors que el SGBD tenia en cache per assignar
Algun rollback realitzat després d'haver demanat un valor a la seqüència.
L'ús de la mateixa seqüència per a vàries taules diferents.

Modificació d'una seqüència

La comanda ALTER SEQUENCE ens permet modificar alguns paràmetres de les seqüències ja creades.

ALTER SEQUENCE [IF EXISTS] sequence_name
      [ INCREMENT [ BY | = ] increment ]
      [ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE
      [ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE
      [ START [ WITH | = ] start ]
      [ CACHE [=] cache ]
      [ [ NO ] CYCLE ]
      [ RESTART [[WITH | =] restart]

Opció	Valor per defecte	Descripció
RESTART	START	Assignem el proper valor que retornarà

Eliminació d'una seqüència

La comanda DROP SEQUENCE ens permet eliminar les seqüències ja creades.

DROP [TEMPORARY] SEQUENCE [IF EXISTS] [/*COMMENT TO SAVE*/]
     sequence_name [, sequence_name] ...

DROP SEQUENCE IF EXISTS corredors_id_seq;

Ara l'execució de la sentència SELECT LASTVAL(corredors_id_seq); dona error.