Oracle - Les squencesJuin 2014Cet article s'appuie sur la version 8.1.6 d'Oracle.Une squence est un objet de base de donnes Oracle, au mme titre qu'une table, une vue, etc...Autrement dit, il appartient un utilisateur, on peut le manipuler, le modifier, condition d'avoir lesdroits ncessaires. Cet article a pour but de dfinir ce qu'est une squence, et de prsenter lespossibilits que cet objet offre.

Dfinition d'une squenceDfinir une squence quivaut dfinir une suite de nombres entiers. L'volution de cette suiteest rgie par un certain nombre de paramtres, que nous allons voir ensemble un peu plus loin.L'utilisation d'une squence permet donc d'avoir disposition une suite de valeurs. Ceci peutpermettre de :

gnrer des cls uniques dans des tablesavoir un compteur titre informatif, que l'on incrmente quand on veutetc...

Ma premire squenceEtant donn que la plupart des paramtres ont une valeur par dfaut, il n'est pas ncessaire detous les spcifier lorsqu'on souhaite crer une nouvelle squence. Voici donc l'ordre SQL minimalde cration d'une squence :CREATE SEQUENCE ma_sequence;Dans cet ordre, "ma_sequence" correspond bien videmment au nom de la squence que voussouhaitez crer ; une squence porte un nom, au mme titre qu'une table, ou que tout autre objetdans Oracle. Cela permet de la manipuler... Si vous excutez cet ordre SQL et si vous disposezdes privilges ncessaires ( savoir CREATE SEQUENCE), Oracle vous rpondra biengentiment "Squence cre.". Mais comment voir ce qu'il y a dans cette squence ? Commentl'exploiter ?L'interrogation d'une squence se fait par l'utilisation des "pseudo-colonnes" CURRVAL etNEXTVAL. On parle de pseudo-colonne car cela se manipule un peu comme une colonne detable, mais ce n'est pas une colonne de table.

La pseudo-colonne CURRVAL retourne la valeur courante de la squence.La pseudo-colonne NEXTVAL incrmente la squence et retourne la nouvelle valeur.

Exemples :

===SQL> select ztblseq.currval from dual;select ztblseq.currval from dual *ERREUR la ligne 1 :ORA-08002: squence ZTBLSEQ.CURRVAL pas encore dfinie dans cette session===Cette erreur est due au fait que l'on n'a jamais encore initialis notre squence, et que l'on essayed'en rcuprer la valeur courante.Lors de la premire utilisation d'une squence, 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 rcuprer la valeur d'une squence, et que l'on saitl'incrmenter, voyons quels sont les diffrents paramtres qui permettent de dfinir une squence :

Identifiant de la squenceAu sein d'une mme base de donnes, plusieurs objets peuvent porter le mme nom, conditionqu'ils soient chacun dans un schma diffrent. Aussi, il est possible de spcifier dans quelschma on souhaite crer la squence :CREATE SEQUENCE schema_toto.sequence_de_toto;

Valeur initiale et incrmentPar dfaut, une squence commence avec la valeur 1, et s'incrmente de 1 en 1 lors de chaqueappel de NEXTVAL. Mais on peut tout fait spcifier ses propres paramtres :CREATE SEQUENCE ma_sequence START WITH 5 INCREMENT BY 3;Dans cet exemple, on a dfini la suite 5, 8, 11, 14, 17, 20... Les paramtres START WITH et INCREMENT BY peuvent s'utiliser indpendamment. Pour faireune suite descendante, il suffit d'indiquer une valeur ngative au paramtre INCREMENT BY :

CREATE SEQUENCE ma_sequence INCREMENT BY -10;

Valeur maxi et valeur miniImplicitement (par dfaut), Oracle a cr notre premire squence avec les paramtres suivants(entre autres) :CREATE SEQUENCE ma_sequence NOMAXVALUE NOMINVALUE;Si on le souhaite, on peut fixer un plafond (pour une squence ascendante) ou un plancher (pourune 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 squenceLorsque la squence atteint sa valeur maxi (resp. mini), on peut lui demander de s'arrter (Oracleretournera une erreur lors de la prochaine tentative d'utilisation de NEXTVAL), ou de reprendre sa valeur mini (resp. maxi) et reprendre son compte. Cette squence comptera jusqu' 10 puis retournera une erreur au NEXTVAL suivant :CREATE SEQUENCE ma_sequence START WITH 1 MINVALUE -10 MAXVALUE 10 NOCYCLE;Cette squence 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 dfaut, une squence ne reboucle pas (cas n1)

Mettre les valeurs en mmoire cacheAfin d'optimiser l'utilisation des squences, on peut demander Oracle de placer en mmoirecache un certain nombre de valeurs de la squence :CREATE SEQUENCE ma_sequence CACHE 100;Par dfaut, Oracle maintient 20 valeurs en mmoire cache.La mise en cache est-elle importante ? Oui, elle peut avoir un effet significatif sur les performances. On peut mettre un nombre de valeurslev en mmoire cache.

Forcer le respect de l'ordre de crationCe paramtre ne concerne que les serveurs fonctionnant en mode parallle. Pour vrifier ceci,excuter l'ordre SQL suivant :SELECT name, value FROM v$parameter WHERE name = 'parallel_server';

Si vous tes en mode parallle (TRUE), lorsque plusieurs NEXTVAL sont excutssimultanment, ils ne sont pas toujours traits dans le bon ordre. Aussi, il faut activer l'optionORDER de la faon suivante :CREATE SEQUENCE ma_sequence ORDER;

Modifier une squenceDans les exemples prcdents, nous avons vu comment crer une squence en spcifiant desattributs qui dfinissent son comportement. Ces attributs sont modifiables aprs cration de lasquence. Il suffit d'utiliser l'ordre SQL ALTER SEQUENCE. Voici un exemple d'enchanementd'ordres SQL :===SQL> create sequence ma_sequence start with 1 minvalue 0;Squence cre.SQL> select ma_sequence.nextval from dual; NEXTVAL--------- 1SQL> select 'La valeur courante est ' || ma_sequence.currval from dual;'LAVALEURCOURANTEEST'||MA_SEQUENCE.CURRVAL---------------------------------------------------------------La valeur courante est 1SQL> alter sequence ma_sequence increment by 20;Squence modifie.SQL> select ma_sequence.nextval from dual; NEXTVAL--------- 21SQL> select ma_sequence.nextval + ma_sequence.nextval from dual;MA_SEQUENCE.NEXTVAL+MA_SEQUENCE.NEXTVAL--------------------------------------- 82SQL> alter sequence ma_sequence increment by -41 maxvalue 100 cycle nocache;Squence modifie.SQL> select ma_sequence.nextval from dual; NEXTVAL--------- 0SQL> select ma_sequence.nextval from dual; NEXTVAL--------- 100SQL> select ma_sequence.nextval from dual; NEXTVAL--------- 59===

Avez-vous remarqu que lors de l'excution de "select ma_sequence.nextval +ma_sequence.nextval from dual;", une seule et mme valeur de la squence est utilise ? Oracleconsidre NEXTVAL comme une pseudo-colonne, et par consquent dans cet ordre SQL, il ne la"prend" qu'une seule fois au niveau de la base, la deuxime fois il reprend la valeur charge enmmoire.

Modifier une squenceUne question qui revient souvent sur les forums Oracle est la suivante : "Comment faire pour crerune colonne de table auto-incrmente, dans le but d'en faire une cl primaire ?". En effet, Oraclene dispose pas de l'option auto_increment que l'on rencontre dans certains SGBD (dont MySQL).Le principe est le suivant :

crer une squence qui permettra de gnrer des valeurs entires uniquescrer un TRIGGER qui se dclenchera chaque INSERT, pour alimenter le champ vouluavec une valeur unique.

Voici un exemple de trigger :===create trigger t_matable_pkbefore insert on matable for each rowbegin select seq_matable_pk.nextval into :new.x from dual;end;===... o "x" est le nom du champ auto incrmenter.Cet exemple ne gre pas le contrle d'unicit de la valeur que l'on va insrer, mais si le champn'est aliment QUE par l'utilisation de la squence qui lui est ddie, et si cette squence n'estpas paramtre pour reboucler, il n'y a pas de raison qu'une erreur de cl en double surgisse...

Une squence sans trou ?Etant donn qu'une squence peut tre interroge tout moment par tout utilisateur Oracle ayantles droits suffisants, IL NE FAUT PAS considrer les squences comme un moyen de gnrerune suite de nombres sans "trous". Exemple : dans le cas de l'alimentation d'une cl primaire, siun enregistrement a t insr, puis si la transaction a subi un "rollback", alors la squence nerevient pas en arrire, et lors de l'insertion suivante, on aura l'impression que la squence a sautun ou plusieurs nombres. Donc : une squence fournit un moyen d'obtenir des valeurs uniques,mais pas forcment continues.

BibliographieDocumentation Oracle : http://www.oradoc.comSchma de la syntaxe de CREATE SEQUENCE :http://www.oradoc.com/ora816/server.816/a76989/ch4g23.gif

Article crit par Tittom

Ce document intitul Oracle - Les squences issu de CommentCaMarche (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 fixes par la licence, tant que cette note apparat clairement.