Search    
  juillet 26, 2017  
Recherche

Gestion de mon blog
You must be logged in and have permission to create or edit a blog.

Création de votre Blog

Vous pouvez librement créer votre blog sur notre site, pour cela vous devez :

1- Vous identifier au préalable. Cliquez ici pour vous identifier ou créer votre compte.
2- Nous envoyez un mail à l'adresse information{at].business-patterns{dot]com
avec votre identifiant.

Votre Blog sera disponible sous 24h.


   You are here:  Blog Corner    
LINQ : Microsoft planche sur un OQL pour sa plateforme .Net 3.0

Comment ajouter une fonctionnalité de filtrage à vos procédures stockées d'extraction de données.

déc. 4

Written by:
04/12/2007 16:51  RssIcon

Dans cet article, la base de données utilisée est Oracle 10g. Cet article expose une méthode vous permettant d'ajouter une fonctionnalité de filtrage à vos procédures stockées d'extraction de données. Après une présentation de la problématique, je montrerai quels sont les avantages de cette méthode : - préservation de la performance dans le cas d'utilisation "non filtré" (cas d'origine) - intégration de la fonctionnalité de filtrage "sans couture" avec le code existant (sans duplication de code pour les cas d'utilisation "filtré" et "non filtré") - extensibilité de la solution (cas d'utilisation "filtré" avec plusieurs critères de filtrage optionnels)

La problématique :

Soit une table T (table de données) avec les colonnes suivantes :
ID  (clé primaire)
LIB (libellé)  non NULL


Soit une procédure stockée d'extraction de données :

PROCEDURE P_GET_DATA([paramètres d'extraction],P_CURSOR IN OUT REF CURSOR)
IS
...
BEGIN
 OPEN P_CURSOR FOR
  SELECT
   T.*
  FROM
   T
  WHERE
   [conditions sur les paramètres d'extraction]
  ;
END;


Nous voudrions ajouter une fonctionnalité de filtrage à la procédure stockée P_GET_DATA pour implémenter les besoins suivants :
- dans le cas d'utilisation "non filtré" : fonctionnement inchangé par rapport à l'existant
- dans le cas d'utilisation "filtré" : filtrage des enregistrements de T par rapport à une liste d'ID dans un premier temps, puis ID et LIB dans un second temps


La solution :

Définissons la table TPER (table de périmètre) avec les colonnes suivantes :
IDPER (identifiant de périmètre)     NUMBER(4) non NULL
ID (filtre sur identifiant d'enregistrement de T)  du même type que T.ID non NULL
LIB (filtre sur libellé d'enregistrement de T)  du même type que T.LIB, peut être NULL

Commentaire :

IDPER nous permettra de définir plusieurs périmètres (cela peut être pratique d'en définir plusieurs afin de spécifier différentes configurations de filtrage, par exemple).

Exemple :
select * from TPER;
IDPER    ID    LIB
1        1     Libelle11
1        2     Libelle12
1        3    
2        1     Libelle21

Nous voyons qu'il y a deux périmètres de filtrage.
Pour le périmètre IDPER=1, nous filtrerons les enregistrements sur :
(ID=1 et LIB=Libelle11) ou
(ID=2 et LIB=Libelle12) ou
(ID=3).

Pour le périmètre IDPER=2, nous filtrerons les enregistrements sur :
(ID=1 et LIB=Libelle21).

Modification de la procédure stockée :

Voyons maintenant la nouvelle implémentation de la procédure stockée P_GET_DATA, modifiée pour implémenter la fonctionnalité de filtrage :

PROCEDURE P_GET_DATA([paramètres d'extraction],P_CURSOR IN OUT REF CURSOR,P_IDPER IN NUMBER(4))
IS
...
/* debut ajout */
filt_actif CONSTANT SMALLINT:=CASE WHEN P_IDPER IS NULL THEN 0 ELSE 1 END;
/* fin ajout */
BEGIN
 OPEN P_CURSOR FOR
  SELECT
   T.*
  FROM
   T
   /* debut ajout */
   LEFT JOIN TPER ON T.ID=TPER.ID AND TPER.IDPER=P_IDPER
   /* fin ajout */
  WHERE
   [conditions sur les paramètres d'extraction]
  /* debut ajout */
  AND  T.ID =  CASE WHEN filt_actif=1 THEN PER.ID ELSE T.ID END
  /* fin ajout */
  ;
END;
Explications :

filt_actif est une constante (il est important que ce soit une constante pour bénéficier des optimisations du moteur Oracle comme nous le verrons par la suite)
valant 0 si P_IDPER est NULL (donc cas d'utilisation "non filtré") ou 1 si P_IDPER est non NULL (cad d'utilisation "filtré").
Impact sur le code : ajout d'une ligne de code.

Ligne LEFT JOIN TPER ...
Cela permet de ramener les mêmes enregistrements de T dans les cas d'utilisation "filtré" et "non filtré". De plus,
la condition de jointure porte sur ce qu'on veut filtrer (T.ID) et en prenant en compte l'identifiant de périmètre (P_IDPER).
Impact sur le code : ajout d'une ligne de code.

Et enfin :
AND T.ID = CASE WHEN filt_actif=1 THEN PER.ID ELSE T.ID END
C'est là que réside la beauté de la solution (si je puis me permettre ;-) ).
En effet, supposons que filt_actif=0 (cas d'utilisation "non filtré"). Dans ce cas, la condition est équivalente à :
AND T.ID = T.ID
, qui est toujours vraie (sauf évidemment si T.ID est NULL, mais comme ID est une clé primaire cela n'arrive jamais).
==> on obtient le même résultat que la procédure stockée d'origine.

Supposons que filt_actif=1 (cas d'utilisation "filtré"). Dans ce cas, la condition est équivalente à :
AND T.ID=PER.ID
Compte-tenu de la condition de jointure (LEFT JOIN...), PER.ID est nul s'il n'y a pas de correspondance entre T.ID et PER.ID
==> si PER.ID est NULL (condition de jointure LEFT JOIN non remplie), la condition est fausse et l'enregistrement T.ID n'est pas sélectionné
==> si PER.ID n'est pas NULL (condition de jointure LEFT JOIN remplie) , la condition est vraie et l'enregistrement T.ID est sélectionné
Impact sur le code : ajout d'une ligne de code.


Sous-conclusion :

Nous avons ajouté seulement trois lignes de code à notre procédure stockée d'origine et répondu aux deux besoins suivants :
- dans le cas d'utilisation "non filtré" : fonctionnement inchangé par rapport à l'existant
- dans le cas d'utilisation "filtré" : filtrage des enregistrements de T par rapport à une liste d'ID

Tout cela est bien, mais on pourrait améliorer le filtrage : sur des ID et LIB donnés.

Amélioration : filtrage sur d'autres critères

Considérons notre table de filtrage TPER :

IDPER    ID    LIB
1        1     Libelle11
1        2     Libelle12
1        3     NULL
2        1     Libelle21

Nous voudrions implémenter une fonctionnalité de filtrage sur ID ET LIB. Nous voulons faire un filtrage par rapport à TPER.LIB (à condition que TPER.LIB soit non NULL).

Par exemple, pour la ligne (IDPER,ID)=(1,3), LIB est NULL ==> la valeur de T.LIB ne sera pas prise en compte pour le filtrage.
Pour la ligne (IDPER,ID)=(1,1), LIB est NON NULL ==> la valeur de T.LIB sera prise en compte pour le filtrage.

Voyons comment modifier notre implémenter notre besoin :

PROCEDURE P_GET_DATA([paramètres d'extraction],P_CURSOR IN OUT REF CURSOR,P_IDPER IN NUMBER(4))
IS
...
filt_actif CONSTANT SMALLINT:=CASE WHEN P_IDPER IS NULL THEN 0 ELSE 1 END;
BEGIN
 OPEN P_CURSOR FOR
  SELECT
   T.*
  FROM
   T
   LEFT JOIN TPER ON T.ID=TPER.ID AND TPER.IDPER=P_IDPER
  WHERE
   [conditions sur les paramètres d'extraction]
  AND  T.ID =  CASE WHEN filt_actif=1 THEN PER.ID ELSE T.ID END
  /*
     debut ajout
  */
  AND ( T.LIB IS NULL AND ((filt_actif=1 AND TPER.LIB IS NULL) OR filt_actif=0) OR
   (T.LIB IS NOT NULL AND T.LIB = CASE WHEN filt_actif=1 THEN NVL(TPER.LIB,T.LIB) ELSE T.LIB END) )
  /*
     fin ajout
  */
  ;
END;
Explications :

Nous avons ajouté les deux lignes suivantes :

  AND ( T.LIB IS NULL AND ((filt_actif=1 AND TPER.LIB IS NULL) OR filt_actif=0) OR 
   (T.LIB IS NOT NULL AND T.LIB = CASE WHEN filt_actif=1 THEN NVL(TPER.LIB,T.LIB) ELSE T.LIB END) )

Si filt_actif=1 :
 Si T.LIB est NULL, l'enregistrement ne sera sélectionné que si TPER.LIB est NULL.
 Si T.LIB n'est pas NULL, la condition est équivalente à T.LIB = NVL(TPER.LIB,T.LIB).
  Si TPER.LIB est NULL, la condition est équivalente à T.LIB = T.LIB ==> l'enregistrement sera toujours sélectionné.
  Si TPER.LIB n'est pas NULL, la condition est équivalente à T.LIB=TPER.LIB ==> l'enregistrement sera filtré.

Si filt_actif=0, la condition est équivalente à (T.LIB IS NULL) OR (T.LIB IS NOT NULL AND T.LIB=T.LIB) , qui est toujours vraie ==> l'enregistrement sera toujours sélectionné.


Conclusion :

J'ai montré dans cet article une méthode permettant d'ajouter une fonctionnalité de filtrage à des procs stockées d'extraction de données (sans duplication de code, simple et performante).
Je suis preneur de toute solution encore plus simple. Si vous en connaissez, n'hésitez pas à faire vivre ce billet !

Olivier Ramat.

Tags:
Categories:
Location: Blogs Parent Separator Olivier Ramat

1 comment(s) so far...


Re: Comment ajouter une fonctionnalité de filtrage à vos procédures stockées d'extraction de données.

has luminescent hands and indices ferrari +
A spectacular breakthrough Pulsar [url=http://www.watch59.net/swatch-watches.html]swatch[/url]
so I was concerned that the http://www.watch59.net,replica watch

By gfgf on   20/04/2011 04:02

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Annuler 

Pourquoi publier sur Business-Patterns.com ?

Cet espace est tout d'abord un lieu de rencontre ouvert aux passionnés qui souhaitent faire partager leurs idées, leurs expériences et leurs expertises.

Il n'est pas nécessaire d'être un expert technique pour publier, bien au contraire, ainsi que le définit la philosophie de BusinessPatterns :

  • les Modèles d'une part, s'adressent aux architectes experts en modélisation de système d'information
  • les Métiers d'autre part, s'adressent aux experts fonctionnels dans tous les domaines.

Nous souhaitons que vous puissiez échanger vos expériences pour aider à faire évoluer les systèmes d'information d'entreprise, vous aidez dans vos fonctions ou simplement vous faire découvir les sujets abordés sur ce site ...

Publier sur BusinessPatterns c'est aussi la possibilité d'être contacté par nos partenaires pour des offres de missions lorsque vos articles les ont intéressés.

Bloggers c'est à vous ...


Copyright 2000-2009 BusinessPatterns & Modèles Métiers - Marques Déposées
Conditions d'utilisation Confidentialité
Accueil|Blog Corner