Oracle snippets🔗

Voici quelques modestes astuces pour tout bon DBA ou développeur qui aurait besoin d'idées sous Oracle. Ces bouts de codes reflètent quelques-uns des problèmes que j'ai dû regler un jour dans la vraie vie…

Si vous utilisez une version d'Oracle Database inférieure à la 12c (donc la dernière version) et que vous débarquez du monde MySQL ou PostgreSQL, vous allez être surpris: il n'y a pas moyen d'auto-incrémenter facilement un champ.

La seule solution qui s'offre à vous est d'utiliser un trigger combiné à une séquence et le moins qu'on puisse dire, c'est que c'est loin d'être pratique au quotidien. En effet, celà implique que pour chaque table vous ayez la création d'une séquence suivie de la déclaration d'un trigger. Si vous avez de nombreuses tables dans ce genre, il vous faudra utiliser un peu de PLPSQL:

-- On commence avec la déclaration de la séquence:
CREATE SEQUENCE "MATABLE_ID_SEQ" MINVALUE 0 START WITH 0 INCREMENT BY 1 NOMAXVALUE;

-- Vient ensuite la déclaration du trigger:
CREATE OR REPLACE TRIGGER "MATABLE_ID_TRG"
BEFORE INSERT ON "ID_MATABLE"
FOR EACH ROW
WHEN (new."ID_MATABLE" IS NULL OR new."ID_MATABLE" = 0)
BEGIN
SELECT "MATABLE_ID_SEQ".NEXTVAL INTO :new."ID_MATABLE" FROM DUAL;
END;

Dans notre cas, on veut donc que la valeur du champ d'identification "MATABLE_ID_SEQ" (qui est une clef primaire de la table "MATABLE") suive la valeur de la séquence "MATABLE_ID_SEQ". Dans l'exemple, j'ai indiqué que le trigger ne se déclenche que lorsque cette clef primaire est vide ou qu'elle contient 0, mais vous pouvez bien sûr supprimer cette condition.

Parfois, on veut aller plus loin que la simple incrémentation d'un champ. J'ai été confronté à un truc méga-classique: créer un deuxième identifiant pour chaque entrée de la table. Cet identifiant est un texte contenant un texte fixe (REF_) suivi du numéro de séquence précédemment affecté à la clef primaire de l'entrée. Comme dans l'exemple précédent, on va utiliser un trigger qui fera l'auto-incrément et notre petit travail de fabrication de texte.

Dans l'exemple qui suit, "REF_MATABLE" est notre deuxième identifiant texte.

CREATE OR REPLACE TRIGGER "MATABLE_ID_TRG"
BEFORE INSERT ON "ID_MATABLE"
FOR EACH ROW
WHEN (new."ID_MATABLE" IS NULL  OR new."ID_MATABLE" = 0)
DECLARE
new_value NUMBER;
ref_id VARCHAR2(9);
BEGIN
SELECT "MATABLE_ID_SEQ".NEXTVAL INTO new_value FROM DUAL;
:new."ID_MATABLE" := new_value;
ref_id := 'REF_' || TO_CHAR(new_value,'FM00000');
:new."REF_MATABLE" := ref_id;
END;

Ici, le travail se fait en utilisant quelques variables dans le code PL/SQL. Il s'agit de new_value et de ref_id. Cette dernière est remplie en utilisant l'opérateur de concaténation de texte (||). Enfin, on convertit un type nombre vers du texte en utilisant TO_CHAR. Cette fonction utilise un argument dit modèle de format. C'est la valeur FM00000. D'après la documentation Oracle, cette valeur permet de remplir avec des zéros (FM pour Fill Mode) de telle manière qu'on dispose au minimum de cinq chiffres affichés. Si new_value contient la valeur 17, alors TO_CHAR(new_value, 'FM00000') retourne '00017'.

Si vous avez un environnement Oracle Spatial (le cartouche spatial d'Oracle Database), je vous conseille plutôt d'aller voir du côté de PostgreSQL/PostGIS. Ce sera plus simple, plus performant et aussi moins cher. Mais si vous n'avez pas le choix, parfois, vous aurez besoin de déclencher des géo-triggers. Sous ce terme, je veux parler de triggers qui font appel à des fonctions géographiques.

Prenons un cas simple: j'ai une table géographique de bâtiments et une autre de quartiers. Je souhaite que lorsque je créé ou je déplace un bâtiment, le champ ID_QUARTIER de la table bâtiment soit rempli avec la valeur de l'identifiant du quartier dans lequel se trouve le bâtiment. Cet exemple est un peu tiré par les cheveux car une simple requête spatiale permet de répondre à la question de savoir dans quel quartier se trouve tel bâtiment ou encore, quels sont les bâtiments d'un quartier donné. Néanmoins, on peut supposer que cette clef étrangère est exploitée ultérieurement par un traitement géographique.

Dans l'exemple qui suit, la table bâtiment se nomme BATIMENT, la table des quartiers se nomme QUARTIER. L'identifiant d'un quartier est "ID_QUARTIER". Le champ à remplir dans la table BATIMENT qui contient l'identifiant du quartier est "REF_QUARTIER".

CREATE OR REPLACE TRIGGER "BATIMENT_GEO_TRG"
BEFORE INSERT OR UPDATE ON "BATIMENT"
FOR EACH ROW
DECLARE
  quartier NUMBER;
BEGIN
  SELECT c."ID_QUARTIER" INTO quartier FROM "QUARTIER" c WHERE SDO_CONTAINS(c."GEOM",:new."GEOM") = 'TRUE';
  :new."REF_QUARTIER" := quartier;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :new."REF_QUARTIER" := NULL;
END;

Le trigger fait appel à une fonction géographique SDO_CONTAINS qui permet de retourner l'identifiant du quartier dans lequel est contenu le bâtiment. Vous pouvez noter que j'ai ajouté une exception. En effet, si vous créez un bâtiment en dehors d'un quartier, le trigger échoue car la requête SELECT ne retourne rien. Il faut donc affecter la valeur NULL au champ REF_QUARTIER dans ce cas.

Cette fois, on imagine qu'on travaille toujours avec des bâtiments mais cette fois, on considère qu'un bâtiment peut appartenir à plusieurs quartiers en même temps. Dans l'exemple précédent, lorsque ce cas se présentait, c'était le dernier quartier retourné par la requête SELECT. Dans le cas présent, on souhaite conserver l'ensemble des quartiers concernés. Pour cela, nous allons utiliser une table de liaison qui fera le lien entre un bâtiment et un ou plusieurs quartiers. Cette table sera nommée "BATIMENT_QUARTIER" et elle contiendra uniquement deux champs (en plus de la clef primaire):

• ID_BATIMENT qui référence le bâtiment en lien avec le quartier.
• ID_QUARTIER qui référence le quartier en lien avec le bâtiment.

Voici l'aspect du trigger

CREATE OR REPLACE TRIGGER "BATIMENT_GEO_TRG"
BEFORE INSERT OR UPDATE ON "BATIMENT"
FOR EACH ROW
BEGIN
-- On supprime les éventuelles anciennes lignes
DELETE FROM "BATIMENT_QUARTIER" WHERE "ID_BATIMENT" = :new."ID_BATIMENT";
-- On insère les nouvelles relations
INSERT INTO "BATIMENT_QUARTIER" ("ID_BATIMENT","ID_QUARTIER")
  SELECT :new."ID_BATIMENT", c."ID_QUARTIER" FROM "QUARTIER" c WHERE SDO_CONTAINS(c."GEOM",:new."GEOM") = 'TRUE'
;
END;

Ce dernier se révèle assez simple à mettre en oeuvre: on y trouve essentiellement du SQL natif pour faire du ménage (le trigger s'active également en cas de création d'objets) suivi d'une simple clause INSERT. Rien de bien folichon…