Oracle - Les séquences

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


<niv1>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.

Notre vidéo

Définition d'une séquence

<niv1>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

<niv1>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;

Dans cet ordre, "ma_sequence" correspond bien évidemment au nom de la séquence que vous souhaitez créer ; une séquence porte un nom, au même titre qu'une table, ou que tout autre objet dans Oracle. Cela permet de la manipuler...
Si vous exécutez cet ordre SQL et si vous disposez des privilèges nécessaires (à savoir CREATE SEQUENCE), Oracle vous répondra bien gentiment "Séquence créée.". Mais comment voir ce qu'il y a dans cette séquence ? Comment l'exploiter ?


<niv1>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.

Exemples :

===
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===

Cette erreur est due au fait que l'on n'a jamais encore initialisé notre séquence, et que l'on essaye d'en récupérer la valeur courante.


<niv1>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
===

CURRVAL retourne maintenant 1. Si si, essayez...

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

Maintenant que l'on sait comment récupérer la valeur d'une séquence, et que l'on sait l'incrémenter, voyons quels sont les différents paramètres qui permettent de définir une séquence :

Identifiant de la séquence

<niv1>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

<niv1>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;

Dans cet exemple, on a défini la suite 5, 8, 11, 14, 17, 20...

Les paramètres START WITH et INCREMENT BY peuvent s'utiliser indépendamment.

Pour faire une suite descendante, il suffit d'indiquer une valeur négative au paramètre INCREMENT BY :

CREATE SEQUENCE ma_sequence INCREMENT BY -10;

Valeur maxi et valeur mini

<niv1>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;

Si on le souhaite, on peut fixer un plafond (pour une séquence ascendante) ou un plancher (pour une suite descendante) :

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

<niv1>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;

Cette séquence comptera de 1 à 10, puis de -10 à 10, puis de -10 à 10... :

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

Par défaut, une séquence ne reboucle pas (cas n°1)

Mettre les valeurs en mémoire cache

<niv1>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;

Par défaut, Oracle maintient 20 valeurs en mémoire cache.


<niv1>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

<niv1>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';

Si vous êtes en mode parallèle (TRUE), lorsque plusieurs NEXTVAL sont exécutés simultanément, ils ne sont pas toujours traités dans le bon ordre. Aussi, il faut activer l'option ORDER de la façon suivante :

CREATE SEQUENCE ma_sequence ORDER;

Modifier une séquence

<niv1>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
===

Avez-vous remarqué que lors de l'exécution de "select ma_sequence.nextval + ma_sequence.nextval from dual;", une seule et même valeur de la séquence est utilisée ? Oracle considère NEXTVAL
comme une pseudo-colonne, et par conséquent dans cet ordre SQL, il ne la "prend" qu'une
seule fois au niveau de la base, la deuxième fois il reprend la valeur chargée en mémoire.

Modifier une séquence

<niv1>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).


<niv1>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.

Voici un exemple de trigger :

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

... où "x" est le nom du champ à auto incrémenter.


<niv1>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 ?

<niv1>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

<niv1>

• Documentation Oracle : https://www.hugedomains.com/domain_profile.cfm?d=oradoc&e=com
• Schéma de la syntaxe de CREATE SEQUENCE : https://static.hugedomains.com/images/logo_huge_domains.gif

<niv1>Article écrit par Tittom