IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Procédure stockée simple


Sujet :

PL/SQL Oracle

  1. #1
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut Procédure stockée simple
    Bonjour,

    Je cherche à écrire une procédure stockée très simple, sauf que je n'y connais pas grand chose en PL/SQL et c'est à moi de me débrouiller ...

    J'ai potassé, fait des essais, FETCH, CURSOR, tout ça je m'en sors pas :/

    Voici ce que je voudrais :

    J'ai une table de log qui liste les erreurs (bloquantes ou non) de l'intégration automatique de commandes.
    Dans cette table de log, j'ai une ligne par erreur. Chaque ligne a un statut :
    - soit (null)
    - soit 'I' comme intégré. Dans ce cas, sauf erreur informatique, la commande ne contient qu'une ligne de log, dans ce cas, la commande elle même est dite intégrée.
    - soit 'R' comme rejeté.
    --> S'il n'y a qu'une ligne de log et qu'elle est 'R', la commande elle même est dite rejetée.
    --> S'il 'y a plusieurs lignes et qu'elles sont toutes 'R', la commande elle même est dite rejetée.
    --> S'il 'y a plusieurs lignes et qu'elles sont indifféremment (null) ou 'R', la commande DOIT ETRE considérée comme rejetée, c'est à dire qu'il faut mettre à jour toutes les lignes de log à 'R'

    Exemple

    Commande Etat
    COM01 I
    COM02 R
    COM03 R
    COM03 R
    COM04 (null)
    COM04 R


    --> COM01 = Intégrée
    --> COM02 = Rejetée
    --> COM03 = Rejetée
    --> COM04 = Rejetée et la première ligne doit etre mise à jour.

    En gros mon idée :
    - Faire un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT commande FROM MA_TABLE
    - Boucler sur ces commandes
    - Dans la boucle faire un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT etat FROM MA_TABLE where commande = numero_de_commande récupéré_plus_haut
    --> Si une seule ligne, on passe à la commande suivante
    --> Si plusieurs lignes, on regarde si les valeurs de chaque ligne sont identiques
    --> Si elles sont identiques, on passe à la commande suivante
    --> Si elles ne sont pas identiques, dès qu'on récupère un 'R', on s'arrete et on fait un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    UPDATE MA_TABLE set etat='R' where commande = numero_de_commande récupéré_plus_haut
    Quelqu'un peut-il m'aider ?
    D'avance merci

  2. #2
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Nul besoin d'écrire une procédure la où un update suffit
    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
     
    Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
    Connected as mni
     
    SQL> 
    SQL> drop table t_commande
      2  /
     
    Table dropped
    SQL> create table t_commande (
      2    commande    varchar2(10),
      3    statut      varchar2(1)
      4  )
      5  /
     
    Table created
    SQL> insert ALL
      2    into t_commande Values(commande, status)
      3    Select 'COM01' commande, 'I' status From dual Union All
      4    Select 'COM02' commande, 'R' status From dual Union All
      5    Select 'COM03' commande, 'R' status From dual Union All
      6    Select 'COM03' commande, 'R' status From dual Union All
      7    Select 'COM04' commande, Null status From dual Union All
      8    Select 'COM04' commande, 'R' status From dual
      9  /
     
    6 rows inserted
    SQL> commit
      2  /
     
    Commit complete
    SQL> Select * from t_commande
      2  /
     
    COMMANDE   STATUT
    ---------- ------
    COM01      I
    COM02      R
    COM03      R
    COM03      R
    COM04      
    COM04      R
     
    6 rows selected
    SQL> Update t_commande
      2    set statut = 'R'
      3   Where (statut != 'R' Or statut Is Null)
      4     And commande In (Select commande
      5                        From t_commande t1
      6                       Group By commande
      7                       Having Count(*) > 1
      8                          And min(Nvl(statut,'I')) != 'R'
      9                      )
     10  /
     
    1 row updated
    SQL> Select * from t_commande
      2  /
     
    COMMANDE   STATUT
    ---------- ------
    COM01      I
    COM02      R
    COM03      R
    COM03      R
    COM04      R
    COM04      R
     
    6 rows selected
     
    SQL>

  3. #3
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Oh merci mnitu , le NVL super j'y aurais pas pensé

    Le "problème" c'est que ce morceau de code doit être exécuté après une procédure stockée, c'est pour ca que je pensais appeler une autre procédure stockée... Je peux mettre ce bout de code dans une prock stock direct ?...

  4. #4
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Vous pouvez toujours écrire une procédure PL/SQL qui fait l’update en question. C’est la seule façon optimale de résoudre ce type de problème.

  5. #5
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Mintu, merci pour vos réponses !

    Cependant j'ai un petit souci avec la requête car j'ai commis une erreur en disant

    - soit 'I' comme intégré. Dans ce cas, sauf erreur informatique, la commande ne contient qu'une ligne de log, dans ce cas, la commande elle même est dite intégrée.

    C'est faux, une commande peut avoir une ligne à 'I' et une ligne à (null) (null = rejet non bloquant pour l'intégration).

    On a donc en fait :
    R + null = R
    I + null = I

    En gros dès qu'il y a un R, la commande est rejetée, sinon elle est intégrée.

    Comment puis-je modifier la requête pour parvenir à ce résultat ?

    Merci !!

  6. #6
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Ah bah non en fait ça marche quand même
    Je suis pas réveillée !!

    Ta requête elle va faire que

    R + null = R + I ==> = R
    I + null = I + I ==> = I

    Donc c'est bon !!!


    Sorry

    Et merci !!

  7. #7
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Ah bah non parce qu'après l'update va tout mettre en statut R

    SOS Mintu, je suis larguée

  8. #8
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Mnitu et pas Mintu, pardon

  9. #9
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Le meilleur conseil que je peux te donnez est d’essayer de voir comment la requête fonctionne, ensuite c’est simple de la modifier.
    La requête met a jour touts les logs des commandes qui ont le statut différent de R ou à Nul pour les commandes pour lesquels il y a au moins deux lignes dans les logs et pour lesquels le statut minime est I ou est Null

  10. #10
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Après presque 2 heures à tourner cela dans ma tête je n'y suis pas arrivée..
    Je ne comprends pas à quoi sert le "min" et je n'arrive pas à différencier les couples (I, null) de (R, null)

  11. #11
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Quelqu'un pourrait-il m'aider SVP ?

  12. #12
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Je n'ai pas trouvé d'autre façon que de le faire en 2 fois :

    -- 1 °) Update des commandes avec un statut à I et un statut à (null) ==> (I, null) doit devenir (I,I)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    UPDATE t_commande
    SET statut = 'I'
    WHERE (statut != 'R' OR statut IS NULL)
    AND commande IN (
                SELECT commande
                FROM t_commande
                GROUP BY commande
                HAVING Count(*) > 1
                AND min(Nvl(statut,'R')) != 'R'
    );

    -- 2 °) Update des commandes avec un statut à R et un statut à (null) ==> (R, null) doit devenir (R,R)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    UPDATE t_commande
    SET statut = 'R'
    WHERE (statut != 'I' OR statut IS NULL)
    AND commande IN (
                SELECT commande
                FROM t_commande
                GROUP BY commande
                HAVING Count(*) > 1
                AND min(Nvl(statut,'R')) != 'I'
    );
    Qu'en pensez-vous ?

  13. #13
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Dans la suite j'ai supposé que le statut des commandes est soit I, soit R ,soit Null et I, soit Null et R.
    Pour identifier les commandes à modifier, l'idée est d'abord de remplacer la valeur NULL par la valeur A, de telle façon que dans l'ordre alphabétique on peut avoir soit A et I, soit A et R. Pour les autres cas si j'ai bien compris il n'y a rien à faire. Examinez d'abord la requête suivante (j'ai ajouté encore une ligne pour la commande COM01 avec le statut à null) dans lequel les commandes qui ont une seule ligne sont ignorée (count(*) > 1)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    SQL>
    SQL> Select commande, min(nvl(statut,'A')) min , max(nvl(statut,'A')) Max
      2    FROM t_commande
      3   GROUP BY commande
      4  HAVING Count(*) > 1 --seulement les commandes multi-lignes
      5  /
     
    COMMANDE   M M
    ---------- - -
    COM03      R R
    COM04      A R
    COM01      A I
    La commande COM02 n'est pas affiché parce que elle a une seule ligne. La commande COM03 a la même valeur pour le min et le max. Les commandes qui nous intéressent sont COM01 et COM04 qui ont la valeur du min différente du max( A et I ou A et R). Donc les commandes à modifier sont
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SQL> Select commande
      2    FROM t_commande
      3   GROUP BY commande
      4  HAVING Count(*) > 1
      5     AND min(nvl(statut,'A')) != max(nvl(statut,'A'))
      6  /
     
    COMMANDE
    ----------
    COM04
    COM01
    Les lignes à modifier sont les lignes pour lequel le statut est nul. La valeur à mettre est soit I soit R cet à dire Max(statut) pour la commande en question. Donc
    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
     
    SQL> SELECT * FROM t_commande
      2  /
     
    COMMANDE   S
    ---------- -
    COM01      I
    COM01
    COM02      R
    COM03      R
    COM03      R
    COM04
    COM04      R
     
    7 ligne(s) sélectionnée(s).
     
    SQL> UPDATE t_commande t1
      2    SET statut = (Select Max(statut)
      3                    From t_commande t2
      4                   Where t2.commande = t1.commande
      5                 )
      6   WHERE (statut IS NULL)
      7     AND commande IN (SELECT commande
      8                        FROM t_commande
      9                       GROUP BY commande
     10                      HAVING Count(*) > 1
     11                         AND min(nvl(statut,'A')) != max(nvl(statut,'A'))
     12                     )
     13  /
     
    2 ligne(s) mise(s) à jour.
     
    SQL> SELECT * FROM t_commande
      2  /
     
    COMMANDE   S
    ---------- -
    COM01      I
    COM01      I
    COM02      R
    COM03      R
    COM03      R
    COM04      R
    COM04      R
     
    7 ligne(s) sélectionnée(s).
    A toi de voir si cella te convient.

  14. #14
    Membre confirmé
    Profil pro
    Inscrit en
    Juillet 2003
    Messages
    214
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Juillet 2003
    Messages : 214
    Par défaut
    Merci Mnitu, c'est exactement ça !!
    Je connaissais mal les having, group by, min, max, et du coup je m'en suis servi pour 2 autres requêtes ça a bien optimiser le temps de traitement !!

    Merci bcp pour tout le temps que tu m'as consacré

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Procédure stockée simple
    Par vistar76 dans le forum SQL Procédural
    Réponses: 2
    Dernier message: 17/10/2010, 21h37
  2. simple select plus performant que procédure stockée
    Par dens19 dans le forum Développement
    Réponses: 5
    Dernier message: 01/09/2010, 10h36
  3. Procédure Stockée des plus simples
    Par cotede2 dans le forum MS SQL Server
    Réponses: 10
    Dernier message: 17/04/2009, 13h42
  4. Procédure stockée simple
    Par popovitch130 dans le forum SQL Procédural
    Réponses: 0
    Dernier message: 29/08/2008, 12h57
  5. Procédure stockée : comment faire plus simple / plus fiable ?
    Par allaume dans le forum Accès aux données
    Réponses: 1
    Dernier message: 08/08/2007, 12h17

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo