Per soddisfare vincoli come PRIMARY KEY
o UNIQUE
dobbiamo in qualche modo conoscere l'ultimo valore inserito (o addirittura tutti) nella relativa colonna, ed evitare la ripetizione di un valore già inserito.
Per evitare di ricorrere a query molto complesse per ricarvare il nuovo valore possiamo servirci delle sequenze.
Le sequenze di Oracle sono dei generatori automatici di numeri che permettono di assegnare, in automatico, i valori nelle colonne numeriche PRIMARY KEY
, in modo simile al comportamento dei contatori di Access, che probabilmente molti di noi hanno già utilizzato.
CREATE SEQUENCE
Il comando per creare una sequenza è CREATE SEQUENCE
ed in questa lezione ne esamineremo il funzionamento.
Sintassi di CREATE SEQUENCE
CREATE SEQUENCE <schema>.<nome sequenza>
START WITH <n>
INCREMENT BY <n>
MINVALUE <n> MAXVALUE <n>
[CYCLE | NOCYCLE];
I parametri di CREATE SEQUENCE
nome
Identifica univocamente la sequenza all'interno di uno schema. Non è possibile avere due sequenze con lo stesso nome nello stesso schema. Diversamente, due schemi all'interno di uno stesso database possono creare sequenze con gli stessi nomi.
Il nome della sequenza sarà memorizzato nel dizionario dei dati di Oracle e vi faremo riferimento ogni volta che useremo la sequenza nei comandi SQL e PL/SQL.
Lo schema indica, ovviamente, l'utente proprietario della sequenza. È possibile creare una sequenza in qualunque schema solo se si possiede il privilegio CREATE ANY SEQUENCE
.
START WITH
Consente di definire il valore di partenza ovvero il numero più piccolo generato da sequenza crescente o il numero più grande per una sequenza decrescente. Il valore di default è 1.
INCREMENT BY
Questa clausola definisce l'intervallo tra i valori nella sequenza e può assumere qualsiasi valore. Se, ad esempio, assegnamo un incremento di 1 la sequenza aumenterà di volta in volta di una unità. Se invece scegliamo un valore negativo, ad esempio -2, al valore corrente verrà sottratto 2 ad ogni passo.
MINVALUE
Rappresenta il più piccolo numero della sequenza ne costituisce cioè il limite inferiore. Il valore di default è NOMINVALUE
che equivale a 1 per una sequenza crescente e a -1026 per una sequenza decrescente.
MAXVALUE
Definisce il limite superiore della sequenza. Il valore di default è NOMAXVALUE
che equivale a 1027 per una sequenza crescente e a -1 per una sequenza decrescente.
CYCLE E NOCYCLE
Queste clausola sono opzionali: CYCLE configura la sequenza per la replica dei numeri dopo che è stato raggiunto un limite (superiore o inferiore) mentre la clausola NOCYCLE impedisce la replica dei numeri al raggiungimento del limite.
Per default il comportamento di una sequenza è "NOCYCLE
". Se usassimo NOCYCLE
in una sequenza incrementale e cercassimo di far generare un numero superiore a MAXVALUE
Oracle solleverebbe un errore.
Nota: Il valore per la clausola START WITH
deve essere sempre maggiore o uguale a MINVALUE
.
Privilegi necessari
Per poter creare una sequenza nel proprio schema necessitiamo del privilegio CREATE SEQUENCE
, che ci permette inoltre di modificare una sequenza già esistente e di eliminarla.
Se l'amministratore di sistema (SYSTEM) ha concesso il privilegio di sistema CONNECT
all'utente che desidera creare la sequenza, questi non necessiterà anche del privilegio CREATE SEQUENCE
in quanto è già idoneo a creare risorse (tabelle, indici, sequenze, ecc.) all'interno del proprio schema.
Esempio
Per esempio creiamo una nuova tabella, "PadreSeq", ed una sequenza per generare i valori della colonna idpadre.
SQL> CREATE TABLE PadreSeq 2 ( 3 IDPadre NUMBER (6) CONSTRAINT pk_PadreSeq_ID PRIMARY KEY, 4 Nominativo VARCHAR2 (20) CONSTRAINT nn_PadreSeq_Nom NOT NULL, 5 DataNascita DATE 6 ); SQL> CREATE SEQUENCE myseq 2 START WITH 1 INCREMENT BY 1 3 MINVALUE 1 NOMAXVALUE NOCYCLE;