Cet article s'appuie sur la version 8.1.6 d'Oracle.

Une séquence est un objet de base de données Oracle, au même titre qu'une table, une vue, etc... Autrement dit, il appartient à un utilisateur, on peut le manipuler, le modifier, à condition d'avoir les droits nécessaires. Cet article a pour but de définir ce qu'est une séquence, et de présenter les possibilités que cet objet offre.

Définition d'une séquence

Définir une séquence équivaut à définir une suite de nombres entiers. L'évolution de cette suite est régie par un certain nombre de paramètres, que nous allons voir ensemble un peu plus loin. L'utilisation d'une séquence permet donc d'avoir à disposition une suite de valeurs. Ceci peut permettre de :

• générer des clés uniques dans des tables
• avoir un compteur à titre informatif, que l'on incrémente quand on veut
• etc...

Ma première séquence

Etant donné que la plupart des paramètres ont une valeur par défaut, il n'est pas nécessaire de tous les spécifier lorsqu'on souhaite créer une nouvelle séquence. Voici donc l'ordre SQL minimal de création d'une séquence :

CREATE SEQUENCE ma_sequence;

L'interrogation d'une séquence se fait par l'utilisation des "pseudo-colonnes" CURRVAL et NEXTVAL. On parle de pseudo-colonne car cela se manipule un peu comme une colonne de table, mais ce n'est pas une colonne de table.

• La pseudo-colonne CURRVAL retourne la valeur courante de la séquence.
• La pseudo-colonne NEXTVAL incrémente la séquence et retourne la nouvelle valeur.

===
SQL> select ztblseq.currval from dual;
select ztblseq.currval from dual
                            *
ERREUR à la ligne 1 :
ORA-08002: séquence ZTBLSEQ.CURRVAL pas encore définie dans cette session
===

Lors de la première utilisation d'une séquence, il faut utiliser NEXTVAL pour l'initialiser.

===
SQL> select ztblseq.nextval from dual;
  NEXTVAL
---------
        1
===

===
SQL> select ztblseq.currval from dual;
  CURRVAL
---------
        1
===

Identifiant de la séquence

Au sein d'une même base de données, plusieurs objets peuvent porter le même nom, à condition qu'ils soient chacun dans un schéma différent. Aussi, il est possible de spécifier dans quel schéma on souhaite créer la séquence :

CREATE SEQUENCE schema_toto.sequence_de_toto;

Valeur initiale et incrément

Par défaut, une séquence commence avec la valeur 1, et s'incrémente de 1 en 1 lors de chaque appel de NEXTVAL. Mais on peut tout à fait spécifier ses propres paramètres :

CREATE SEQUENCE ma_sequence START WITH 5 INCREMENT BY 3;

CREATE SEQUENCE ma_sequence INCREMENT BY -10;

Valeur maxi et valeur mini

Implicitement (par défaut), Oracle a créé notre première séquence avec les paramètres suivants (entre autres) :

CREATE SEQUENCE ma_sequence NOMAXVALUE NOMINVALUE;

CREATE SEQUENCE ma_sequence START WITH 1 INCREMENT BY 1 MAXVALUE 9999;
CREATE SEQUENCE ma_sequence START WITH -1 INCREMENT BY -1 MINVALUE -9999;

Reboucler la séquence

Lorsque la séquence atteint sa valeur maxi (resp. mini), on peut lui demander de s'arrêter (Oracle retournera une erreur lors de la prochaine tentative d'utilisation de NEXTVAL), ou de reprendre à sa valeur mini (resp. maxi) et reprendre son compte.
Cette séquence comptera jusqu'à 10 puis retournera une erreur au NEXTVAL suivant :

CREATE SEQUENCE ma_sequence START WITH 1 MINVALUE -10 MAXVALUE 10 NOCYCLE;

CREATE SEQUENCE ma_sequence START WITH 1 MINVALUE -10 MAXVALUE 10 CYCLE;

Mettre les valeurs en mémoire cache

Afin d'optimiser l'utilisation des séquences, on peut demander à Oracle de placer en mémoire cache un certain nombre de valeurs de la séquence :

CREATE SEQUENCE ma_sequence CACHE 100;

La mise en cache est-elle importante ?
Oui, elle peut avoir un effet significatif sur les performances. On peut mettre un nombre de valeurs élevé en mémoire cache.

Forcer le respect de l'ordre de création

Ce paramètre ne concerne que les serveurs fonctionnant en mode parallèle. Pour vérifier ceci, exécuter l'ordre SQL suivant :

SELECT name, value FROM v$parameter WHERE name = 'parallel_server';

CREATE SEQUENCE ma_sequence ORDER;

Modifier une séquence

Dans les exemples précédents, nous avons vu comment créer une séquence en spécifiant des attributs qui définissent son comportement. Ces attributs sont modifiables après création de la séquence. Il suffit d'utiliser l'ordre SQL ALTER SEQUENCE. Voici un exemple d'enchaînement d'ordres SQL :

===
SQL> create sequence ma_sequence start with 1 minvalue 0;
Séquence créée.
SQL> select ma_sequence.nextval from dual;
  NEXTVAL
---------
        1
SQL> select 'La valeur courante est ' || ma_sequence.currval from dual;
'LAVALEURCOURANTEEST'||MA_SEQUENCE.CURRVAL
---------------------------------------------------------------
La valeur courante est 1
SQL> alter sequence ma_sequence increment by 20;
Séquence modifiée.
SQL> select ma_sequence.nextval from dual;
  NEXTVAL
---------
       21
SQL> select ma_sequence.nextval + ma_sequence.nextval from dual;
MA_SEQUENCE.NEXTVAL+MA_SEQUENCE.NEXTVAL
---------------------------------------
                                     82
SQL> alter sequence ma_sequence increment by -41 maxvalue 100 cycle nocache;
Séquence modifiée.
SQL> select ma_sequence.nextval from dual;
  NEXTVAL
---------
        0
SQL> select ma_sequence.nextval from dual;
  NEXTVAL
---------
      100
SQL> select ma_sequence.nextval from dual;
  NEXTVAL
---------
       59
===

Modifier une séquence

Une question qui revient souvent sur les forums Oracle est la suivante : "Comment faire pour créer une colonne de table auto-incrémentée, dans le but d'en faire une clé primaire ?". En effet, Oracle ne dispose pas de l'option auto_increment que l'on rencontre dans certains SGBD (dont MySQL).

Le principe est le suivant :

• créer une séquence qui permettra de générer des valeurs entières uniques
• créer un TRIGGER qui se déclenchera à chaque INSERT, pour alimenter le champ voulu avec une valeur unique.

===
create trigger t_matable_pk
before insert on matable for each row
begin
   select seq_matable_pk.nextval into :new.x from dual;
end;
===

Cet exemple ne gère pas le contrôle d'unicité de la valeur que l'on va insérer, mais si le champ n'est alimenté QUE par l'utilisation de la séquence qui lui est dédiée, et si cette séquence n'est pas paramétrée pour reboucler, il n'y a pas de raison qu'une erreur de clé en double surgisse...

Une séquence sans trou ?

Etant donné qu'une séquence peut être interrogée à tout moment par tout utilisateur Oracle ayant les droits suffisants, IL NE FAUT PAS considérer les séquences comme un moyen de générer une suite de nombres sans "trous". Exemple : dans le cas de l'alimentation d'une clé primaire, si un enregistrement a été inséré, puis si la transaction a subi un "rollback", alors la séquence ne revient pas en arrière, et lors de l'insertion suivante, on aura l'impression que la séquence a sauté un ou plusieurs nombres. Donc : une séquence fournit un moyen d'obtenir des valeurs uniques, mais pas forcément continues.

Bibliographie

• Documentation Oracle : http://www.oradoc.com
• Schéma de la syntaxe de CREATE SEQUENCE : http://www.oradoc.com/ora816/server.816/a76989/ch4g23.gif

Article écrit par Tittom

Ce document issu de CommentCaMarche.net est soumis à la licence GNU FDL. Vous pouvez copier, modifier des copies de cette page tant que cette note apparaît clairement.