Discussion :

[Gandahar]

Bonjour à tous,

je voudrai construire une requete permettant de dégager les plages de nombres consécutifs non utilisés sur un champ number d'une table.

Any idea ?
merci pour votre aide.

[mnitu]

Utilisez les fonctions analytiques pour ça. Un petit exemple sera bien venu pour éviter des confusions.

[dgi77]

Voici une requête qui pourrait être un point de départ :

Code :

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

1
2
3
4
 
select level from dual connect by level <= 100
minus 
select col_num from nom_table

A adapter bien sûr...

[Gandahar]

dgi77 : excellent et simplissime !! effectivement c'est un très bon point de départ. J'obtiens ainsi tous les nombres non utilisés effectivement mais comment en faire ressortir les extrêmes des plages et uniquement les extrêmes ?

Pour mnitu : voici un exemple.
Table client (client_id, nom, prenom, ....)
Le champ client id est affecté par une séquence mais dans certains cas (supression physique du client, non utilisation de la valeur nextval, ...) des trous sont générés. Je me retouve avec :

Code :

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

select * from client order by client_id

client_id;nom;prenom
1;NOM_CLIENT1;PRENOM_CLIENT1
2;NOM_CLIENT2;PRENOM_CLIENT2
9;NOM_CLIENT9;PRENOM_CLIENT9
15;NOM_CLIENT15;PRENOM_CLIENT15

Je souhaite que la requete me renvoie par exemple

Plage 3 à 8 disponible
Plage 10 à 14 disponible

[Waldar]

Si vous voulez boucher les trous, c'est très mauvaise idée !

[Gandahar]

Non non ce n'est pas le cas.
Ceci dit pourquoi est ce une mauvaise idée ?

[Waldar]

Celà signifierait que vous avez plusieurs façon de renseigner cette PK, et un jour où l'autre, ça finira par flancher.

Les trous de séquence ne sont pas gênant tant que l'id est unique, et ça c'est garanti par Oracle avec la séquence.

Pour votre besoin, vous pouvez utiliser une fonction analytique :

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
with client as
(
select  1 as client_id from dual union all
select  2              from dual union all
select  9              from dual union all
select 15              from dual
)
  ,  sr1 as
(
select client_id,  lag(client_id, 1, 0) over(order by client_id asc) as lg,
       client_id - lag(client_id, 1, 0) over(order by client_id asc) as df
  from client
)
  select 'Plage' || to_char(lg + 1, '90') ||
            ' à' || to_char(client_id - 1, '90') || ' disponible' as res
    from sr1
   where df > 1
order by client_id asc;
 
RES
------------------------
Plage  3 à  8 disponible
Plage 10 à 14 disponible

[mnitu]

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
 
SQL> With Data As (
  2    Select 1 client_id, 'NOM_CLIENT1' as nom, 'PRENOM_CLIENT1' as prenom From dual union all
  3    Select 2, 'NOM_CLIENT2', 'PRENOM_CLIENT2'  From dual union all
  4    Select 9, 'NOM_CLIENT9', 'PRENOM_CLIENT9'  From dual union all
  5    Select 15, 'NOM_CLIENT15', 'PRENOM_CLIENT15'  From dual
  6  )
  7  Select client_id + 1 , ld - 1
  8   From (
  9    Select client_id, lead(client_id) over( order by client_id) ld
 10      from Data
 11      )
 12  Where client_id != ld - 1
 13  /
 
CLIENT_ID+1       LD-1
----------- ----------
          3          8
         10         14

L'utilisation d'une séquence ne peut pas garantir la continuité des valeurs dans la table.

[Gandahar]

Woaw !! Bravo !
Faut vraiment que je me mettes aux fonctions analytiques....