Procédure stockée simple

Version imprimable

14/01/2009, 15h31
babylone7

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 ...:oops:

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:

SELECT commande FROM MA_TABLE

- Boucler sur ces commandes
- Dans la boucle faire un

Code:

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:

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 ;)

Nul besoin d'écrire une procédure la où un update suffit

Code:

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>

14/01/2009, 16h21
babylone7

Oh merci mnitu , le NVL super j'y aurais pas pensé :oops::oops:

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 ?...
14/01/2009, 16h25
mnitu

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.
15/01/2009, 10h29
babylone7

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 !!
15/01/2009, 10h39
babylone7

Ah bah non en fait ça marche quand même :lol:
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 :oops:

Et merci !! :D
15/01/2009, 10h45
babylone7

Ah bah non parce qu'après l'update va tout mettre en statut R :oops::aie:

SOS Mintu, je suis larguée :cry:
15/01/2009, 14h18
babylone7

Mnitu et pas Mintu, pardon :oops:
15/01/2009, 14h59
mnitu

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
16/01/2009, 17h00
babylone7

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) :oops:
20/01/2009, 11h53
babylone7

Quelqu'un pourrait-il m'aider SVP ? :oops:

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:

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:

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 ?

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:

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:

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:

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.

21/01/2009, 11h00
babylone7

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 !!:lol:

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