Discussion :

[david2109]

Bonjour,

J'ai une table ORACLE dans laquelle par suite d'ajouts et suppressions, il existe des numéros de séquence disponibles.

Par exemple : TABLE TBPRNI

IDPRNI;LILPRNI
3532;BANCS PD DE
3533;BANCS PD CH
3534;BANCS PD BE
3537;PRNIV INT ES
3539;PRNIV INT ES
3540;PRNIV DOM ES
3541;PRNIV DOM IT
3542;PRNIV PD ES
3543;PRNIV INT GB

Les numéros 3535 3536 3538 3544 et au-delà sont donc disponibles

Je sais récupérer le 1er numéro disponible de ma table.

SELECT MIN(A.IDPRNI) + 1
FROM TBPRNI A WHERE A.IDPRNI + 1
NOT IN (SELECT B.IDPRNI FROM TBPRNI B)

renvoie 3535 (correct)


Mais j'ai besoin de récupérer les N premiers numéros disponibles ? N pouvant varier selon le contexte.

Si N=3, la requete SQL doit retourner 3535 3536 et 3538
Si N=5, la requete SQL doit retourner 3535 3536 3538 3544 et 3545

Je ne parviens pas à construire cette requete.
Une idée ?

PS : J'ai besoin que les 3 numéros soient remontées d'un seul coup. Impossible d'aller chercher le 1er numéro disponible, de commiter, de chercher le 2eme 1er numero dispo, de commiter, etc.

[al1_24]

J'ai la flemme de rechercher comment générer une liste d'entiers (la première sous-requête), mais le principe est là :

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
WITH    nbr
    AS  (   SELECT  1   num FROM DUAL
        UNION   SELECT  2   FROM DUAL
        ...
        UNION   SELECT  :n  FROM DUAL
        )
    ,   lst
    AS  (   SELECT  src.idprni + nbr.num    dispo
                ,   ROW_NUMBER() OVER (ORDER BY src.idprni + nbr.num)   ord
            FROM    tbprni  src
                CROSS JOIN
                    nbr
            WHERE   NOT EXISTS
                    (   SELECT  NULL
                        FROM    tbprni  xst
                        WHERE   xst.idprni = src.idprni + nbr.num
                    )
        )
SELECT  lst.dispo
FROM    lst
WHERE   lst.ord IN  
        (   SELECT  num
            FROM    nbr   
        )
;

Comme cela, tu récupères le plus d'identifiants possibles dans chaque série de "trous", dans la limite du nombre que tu as précisé.

[pacmann]

Salut,

En faisant la différence avec le suivant, que tu obtiens avec la fonction analytique lead, tu sais pour chaque ligne quels sont les trous qui la succède.

L'astuce CONNECT BY permet de répliquer les lignes autant de fois qu'il n'y a de trous (Merci Waldar et Skuat, c'est un truc que j'ai appris récemment ici )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
WITH TBPRNI AS (
SELECT column_value idprni
FROM table(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
, u as (select idprni, lead(idprni, 1) over(order by idprni) - idprni  n
        from tbprni)
select idprni + level
from u
where n > 1
connect by level < n
and prior idprni = idprni
and PRIOR sys_guid() IS NOT NULL

[david2109]

Bonjour,

Merci pour vos réponses.

Concernant la première possibilité, j'ai oublié de préciser que la requete SQL sera appelée par du code et qu'il sera donc difficile de la modifier dynamiquement.
La partie

UNION SELECT 2 FROM DUAL
...
UNION SELECT :n FROM DUAL

pose donc problème.
En outre, cette requête renvoie toujours 3535 ?!


Concernant la seconde possibilité, pas simple effectivement mais cela fonctionne mieux

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
WITH TBPRNI AS (
SELECT column_value idprni
FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)
, u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT idprni + level
FROM u
WHERE n > 1
connect BY level < n
AND prior idprni = idprni
AND PRIOR sys_guid() IS NOT NULL

j'obtiens la liste de tous les trous et après, je peux me débrouiller.

Par contre, dans la requete, les numéros idprni 3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543 sont en dur.
L'exemple que j'ai fourni n'est qu'un extrait. En pratique la table TBPRNI est trop importante pour mettre les numéros en dur.

Est-il possible de faire en sorte que la requete récupère la liste des numéros utilisés ? J'ai essayé - sait-on jamais ? - un "select distinct from tbprni mais ca ne marche pas (ca serait trop simple)."

[pacmann]

La partie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
WITH TBPRNI AS (
SELECT column_value idprni
FROM TABLE(sys.odcinumberlist(3532, 3533, 3534, 3537, 3539, 3540, 3541, 3542, 3543))
)

... ne servait qu'à émuler la création de la table TBPRNI de mon côté, et tester la solution. De ton côté, tu peux omettre cette partie et te contenter de la suite de la requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
WITH u AS (SELECT idprni, lead(idprni, 1) over(ORDER BY idprni) - idprni  n
        FROM tbprni)
SELECT idprni + level
FROM u
WHERE n > 1
connect BY level < n
AND prior idprni = idprni
AND PRIOR sys_guid() IS NOT NULL

[david2109]

Bonjour,

Merci cela fonctionne ! J'obtiens la liste de tous les identifiants disponibles dans la table TBPRNI

Une dernière question : comment et où amender la requete pour qu'elle me renvoie la liste des 5 premiers identifiants disponibles ? (le chiffre pouvant être un paramètre)

Je ne veux pas abuser mais il y a trop de notions que je dois d'abord approfondir dans cette requete !