Bonjour,

ma question se trouve en titre de cette discussion : comment réserver ses ids ?

Version utilisée : Oracle 12C

Enjeux : je dispose d'un modèle de données relationnel avec de nombreuses clés étrangères car j'essaie au maximum de respecter les formes normales, cependant lors de certaines insertions cela produit de très longues requêtes avec beaucoup de condition d'égalité sur du VARCHAR. Hors, afin de réduire le risque d'erreur et de raccourcir mes codes je voudrais pouvoir réserver à l'avance une suite d'id afin d'avoir, par exemple, tous les libellés qualifiant l'action de mes utilisateurs entre 1 et 5. Ainsi, au lieu de faire une recherche sur la valeur je ferai une recherche sur l'id, ce qui serait plus rapide, plus court à écrire et moins sujet aux erreurs (notamment dues à la casse).

Objectif : le code doit donc permettre de réserver une plage d'id tout en permettant l'insertion dans la même table et par un autre utilisateur d'autres valeurs (avec une réservation d'ids). Il ne doit pas y avoir de doublons d'id ou de valeur.

J'ai trouvé une solution que je vous présente ci-dessous, cependant j'y utilise une boucle séquentielle, ce qui n'est pas l'idéal pour de gros volume de données. J'aimerai donc savoir si vous connaissez une solution ou si vous auriez des pistes avec un traitement ensembliste.

Je précise également que j'utilise le type de donnée NUMBER GENERATED BY DEFAULT AS IDENTITY afin d'utiliser l'auto-incrémentation et de pouvoir modifier la séquence d'auto-incrémentation à ma guise.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- Création des tables
CREATE TABLE "G_GEO"."TEMP_LIBELLE"(
    "OBJECTID" NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 43 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "VALEUR" VARCHAR2(4000 BYTE)
);
 
CREATE TABLE "G_GEO"."TEMP_IMPORT"(
    "OBJECTID" NUMBER(38,0) GENERATED ALWAYS AS IDENTITY,
    "VALEUR" VARCHAR2(4000 BYTE)
);
-- 1. Code permettant de réserver une plage d'id et d'insérer une liste de valeur dans la même transaction ;
SET SERVEROUTPUT ON
DECLARE
    v_id_max NUMBER(38,0);
    v_nb_id_reserve NUMBER(38,0);
    v_id_reserve NUMBER(38,0);
    v_nom_sequence VARCHAR2(50);
 
BEGIN
	SAVEPOINT POINT_SAUVERGARDE_RESERVATION_ID;
-- Identification des libellés présents en base ou non
UPDATE G_GEO.TEMP_IMPORT a
    SET a.EN_BASE = CASE
                        WHEN a.VALEUR IN(SELECT b.VALEUR FROM G_GEO.TEMP_LIBELLE b)
                            THEN 1 -- si la valeur est déjà en base alors 1
                        ELSE
                            0 -- si la valeur n'est pas en base alors 0
                    END;
 
-- Décompte du nombre d'objet/de valeurs à insérer en base
SELECT
    COUNT(OGR_FID)
    INTO v_nb_id_reserve
FROM
    G_GEO.TEMP_IMPORT
WHERE
    EN_BASE = 0;
 
-- Sélection du nom de la séquence d'incrémentation
SELECT 
    SEQUENCE_NAME
    INTO v_nom_sequence
FROM user_tab_identity_cols
WHERE TABLE_NAME = 'TEMP_LIBELLE';
 
-- Sélection de la prochaine valeur de séquence de la table d'insertion
SELECT
    LAST_NUMBER
    INTO v_id_max
FROM
    ALL_SEQUENCES
WHERE
    SEQUENCE_NAME = v_nom_sequence;
 
-- Création du nouvel id à partir duquel faire repartir l'auto-incrémentation de la PK.
    v_id_reserve := v_nb_id_reserve + v_id_max
-- Modification de la valeur de départ de la séquence d'auto-incrémentation via du SQL dynamique
EXECUTE IMMEDIATE 'ALTER TABLE G_GEO.TEMP_LIBELLE MODIFY OBJECTID GENERATED BY DEFAULT AS IDENTITY (START WITH ' || v_id_reserve || ')';
 
-- Insertion des valeurs dans la table. L'incrémentation de la PK se fait via une boucle, c'est-à-dire une structure séquentielle
    FOR i IN(SELECT VALEUR FROM TEMP_MIBELLE_POINT_VIGILANCE WHERE EN_BASE = 0) LOOP
        INSERT INTO G_GEO.TEMP_LIBELLE(objectid, valeur)
        VALUES(v_id_max, i.valeur);
        v_id_max := v_id_max + 1;
    END LOOP;
 
-- Le code qui suit permet de connaître le nombre d'ids réservés et la valeur à partir de laquelle l'auto-incrémentation reprend
DBMS_OUTPUT.PUT_lINE('Nombre d''id à réserver : '|| v_nb_id_reserve || ' - id de reprise de l''incrémentation : ' || v_id_reserve);
-- En cas d'erreur un rollback est effectué, permettant de revenir à l'état de la table précédent la tentative d'insertion. Dans ce cas le LAST_NUMBER de la séquence d'auto-incrémentation n'est pas modifié.
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('une erreur est survenue, un rollback va être effectué: ' || SQLCODE || ' : '  || SQLERRM(SQLCODE));
    ROLLBACK TO POINT_SAUVERGARDE_RESERVATION_ID;
END;
Je pensais pouvoir utiliser NEXTVAL mais il me semble que cette pseudo-colonne n'est pas spécifique à la session, contrairement à CURRVAL, mais commun à toutes, ce qui empêche de faire deux réservations d'ids en même temps.

Merci pour votre aide,
Baptiste443