Comment Ca Marche - Communauté informatique  
   
Accueil - Encyclopédie informatiqueTélécharger l'encyclopédieContribuer à cet article

Oracle - Les séquences

Les séquences Oracle Encyclopédie

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

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.

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

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

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

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

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.

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

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

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

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

Article écrit par Tittom

Trucs & astuces pertinents trouvés dans la base de connaissances

22/09 18h56 Connexion à une base Oracle en php (Oracle)
Oracle Plus d'astuces sur « Oracle »

Discussions pertinentes trouvées dans le forum

20/06 17h48 import base donnees oracle Import base de donnees oracle Développement 17/02 09h48->abdou9
31/05 16h27 sql oracle liste champs [SQL][ORACLE] Liste des champs Développement 18/09 13h00->yannick8
09/02 14h57 oracle uml modéliser base préexistante [ORACLE, UML] modéliser une base préexistante Développement 05/12 01h55->artaud8
24/03 13h41 personal oracle 8 personal oracle 8 Logiciels/Pilotes 26/03 10h17->yanban7
07/04 02h10 installation oracle 8i windows xp Pb installation oracle 8i sous windows XP Windows 22/01 15h32->loulou7
02/12 15h59 oracle comparaison heures [oracle] comparaison d'heures Développement 09/02 14h03->Gilles M5
08/03 12h04 oracle bd document.form.submit [oracle bd] document.form.submit() Développement 12/03 11h36->renisaac5
08/02 11h08 access import données oracle [Access] import données oracle Logiciels/Pilotes 10/02 18h27->random4
11/11 17h06 oracle developer [oracle] developer Développement 11/11 18h16->fagy4
15/02 20h08 bases dnnées oracle Question dans Bases de Dnnées ORACLE Développement 23/02 12h21->Alex754
Discussion fermée Problème résolu Oracle Plus de discussions sur « Oracle »

Ce document intitulé « Oracle - Les séquences » issu de l'encyclopédie informatique Comment Ça Marche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.